Access VBA: how to create crosstab table using two queries

[miss D]

hi,
I have two queries, named:
1. Q1, which has columns name
Mgr ID, MGR_Name, Empl_ID, Empl_Name, Years_of_Servic e

and
2.Q2 which has
MGR_ID, EE count

both queries have 1:1 relationship for MGR_ID

Using this two queries, I'd like to output a table like this:



The empl_name with respective MGR_ID will be automatically feed to columns named GAR1 until GAR 6 and the respective years_of_servic e will be feed to GAR 7 - GAR 13 columns accordingly.

Can anyone help me?

[kadghar]

have you tried using Docmd.RunSQL?:

Docmd.RunSQL "Select MGR_ID from Table1 where ..."

[miss D]

not yet..
how can i do that?
can u help me with the code?
i am a newbie in vba..

[kadghar]

just like i told you above.

The tricky part here is doing the SQL for showing the count and other columns, since SQL with count willo only show you the counted column, so make 2 SELECT and merge them.

[miss D]

for the count part, i have no problem
as i already generate the query to count number of employee that manager has.


the problem is how do I define my own column named GAR 1 until GAR 6 to feed the employees names. followed by GAR 7 untill GAR 12 for years of service for respective employees.
Can u help me?

Thanks in advance

[kadghar]

mmm, i see. That's a hard one, why dont you try asking in the MySQL or SQL Server forum?
I'm sure the guys there will give you a better SQL command.

[miss D]

alright.
thanks for your replies :)