Union Query Group By

T

talibm

Hello Access Nation, I have two tables that I want to join and get distinct
client records. The tables are for meals at a hunger center. One table
contains breakfast info and another contains lunch info. I've looked at all
the threads but I'm missing something. Any and all help is appreciated.
Thanks T

breakfast table
brecno bclienti breakfast(yes/no) date
1 222 y 4/22/08
2 444 y 4/15/08

lunch table
lrecno clientid lunch(yes/no) date
1 222 y 4/11/08
2 333 y 4/12/08

I want to join the results from these two tables and get an unduplicated
result. What I'm trying to get is an unduplicated list or count.
222
333
444
 
K

Klatuu

A UNION query naturally eliminates duplcates. To include duplicates, you
have to use the UNION ALL
 
T

talibm

yes, an unduplicated list of client ids during a specific date range. this is
what I try to make work. i dont get an unduplicated list. what an I doing
wrong?

SELECT DISTINCT [tbl BreakfastAttendance].[Client ID], [tbl
BreakfastAttendance].Date, [tbl BreakfastAttendance].AttendedBreakfast
FROM [tbl BreakfastAttendance]
WHERE (([tbl BreakfastAttendance].Date) Between [start] And [end]) AND
(([tbl BreakfastAttendance].AttendedBreakfast)=Yes)
UNION
SELECT DISTINCT [tbl LunchAttendance].[Client ID], [tbl
LunchAttendance].Date, [tbl LunchAttendance].AttendedLunch
FROM [tbl LunchAttendance]
WHERE(([tbl LunchAttendance].Date) Between [start] And [end]) AND (([tbl
LunchAttendance].AttendedLunch)=Yes);
 
T

talibm

Thanks guys for pointing me in the right direction. I created two parameter
queries one for each table. Then I used UNION to join them and I got the
result that I had been looking for
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top