 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008Union Query Group By
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  Signaturetm A UNION query naturally eliminates duplcates. To include duplicates, you have to use the UNION ALL SignatureDave Hargis, Microsoft Access MVP > 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 [quoted text clipped - 17 lines] > 333 > 444 What do you want to show in this... just an unduplicated list of Client IDs? > 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 [quoted text clipped - 17 lines] > 333 > 444 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); Signaturetm > What do you want to show in this... just an unduplicated list of Client IDs? > [quoted text clipped - 19 lines] > > 333 > > 444 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 Signaturetm > 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 [quoted text clipped - 17 lines] > 333 > 444 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.