Join

[kbreiss]

I have two tables.......APPOINTMENT and ARCHIVED. I'm trying to get stats on how many appointments each username has scheduled. So I'm wanting to query both tables and group by "username" and get a count for each username Is this possible to do with one query.

Sample table layout:

APPOINTMENT
---DATE
---TIME
---USERNAME

ARCHIVED
---DATE
---TIME
---USERNAME
________
WASHINGTON MARIJUANA DISPENSARIES

 

[dcx693]

Yes, this is possible. If the tables don't overlap, create a Union query. That will "merge" the contents of the two separate tables into one using a query (but won't alter the individual tables). You can then run your totals query, grouping on username, off the union query.

Be aware that Union queries automatically screen out duplicates. This will probably be desired in your case. If not, use "UNION ALL" instead of just "UNION" in your query design.

Read up in the Access help for more info on Union queries.

 

[kbreiss]

Ok,
I tried a Union query and it works except it gives me a count for the same USERNAME from the APPOINTMENTS table and ARCHIVED table. I would like for them to be grouped by USERNAME regardless of which table they came from. Am I doing something wrong. I was able to run another sum query off of the union query and it worked, but wondering if I could get it done in the union query. Here is my query I used.....

SELECT Appointment.username, count(*) as tot
FROM APPOINTMENT
group by appointment.username
UNION
select archived.username, count(*)
from archived
group by archived.username;

I was then able to use..........

SELECT Query3.username, Sum(tot) as tot_sum
FROM Query3
GROUP BY Query3.username;

Are both of these queries necessary to get what I am wanting to achieve?

Thanks,
Kacy
________
Rolls-royce limited

 

[dcx693]

I would first do the Union query without grouping them so you'd just get the detail records. Then I'd run the totals query grouped on username.

 

[kbreiss]

Thank you.........that works!

Kacy
________
WEED VAPORIZERS