Query for same fields, different dates

[BCJourney]

I have a query that is based on three queries. 2 of those queries have the same fields but return data for different dates. The 3rd query returns the dates needed. In other words, I am trying to return all results from both queries. these are the results I want:



Report_ID Report_Date Contents Approvers
Standard ops 5/1/13 daily operations rpt tom tomlin
royer manuf 7/1/13 syndication rpt rob davis

etc.

These are for year-t0-date. there is a YTD query that returns all dates needed. The other two queries return records on different dates, on is for dates <5/5/13 and the other is for dates >5/4/13. I have listed the SQL below. It returns the correct records, but for the Record_ID it returns -1 in each field instead of the report_ID text. So, I need to get the other fields to show up correctly.

SELECT [Daily_rpts_YTD_due_before_5_5_2013]![Report_ID] Or [Daily_rpts_YTD_due_after_5_4_2013]![Report_ID] AS [Report ID], due_dates_Daily_YTD.Due_dates
FROM (due_dates_Daily_YTD LEFT JOIN Daily_rpts_YTD_due_before_5_5_2013 ON due_dates_Daily_YTD.Due_dates = Daily_rpts_YTD_due_before_5_5_2013.Due_dates) LEFT JOIN Daily_rpts_YTD_due_after_5_4_2013 ON due_dates_Daily_YTD.Due_dates = Daily_rpts_YTD_due_after_5_4_2013.Due_dates
WHERE (((due_dates_Daily_YTD.Due_dates)=[Daily_rpts_YTD_due_after_5_4_2013]![Due_dates] Or (due_dates_Daily_YTD.Due_dates)=[Daily_rpts_YTD_due_before_5_5_2013]![Due_dates]));

[Dal Jeanis]

I believe that's because it's evaluating this OR condition

[Daily_rpts_YTD_due_before_5_5_2013]![Report_ID] Or [Daily_rpts_YTD_due_after_5_4_2013]![Report_ID]

to "Null" OR "True", rather than giving you one or the other.

You need to replace that with an IIF/NZ structure.

By the way, I highly recommend Aliasing your tables and queries. In design view, you can right-click the source table or source query and give it a short new name to make the SQL more readable. Your desired SQL should end up looking somewhat like this (I coded it by hand, so don't expect it to be perfect):

SELECT (NZ(DD5.[Report_ID], DD4.[Report_ID]) AS [Report ID], 
       DDY.Due_dates
FROM 
   (
     (due_dates_Daily_YTD AS DDY
      LEFT JOIN 
      Daily_rpts_YTD_due_before_5_5_2013 AS DD5
      ON DDY.Due_dates = DD5.Due_dates
     ) 
   LEFT JOIN 
   Daily_rpts_YTD_due_after_5_4_2013 AS DD4 
   ON DDY.Due_dates = DD4.Due_dates
   )  
WHERE 
   (
    ((DDY.Due_dates)=DD4.[Due_dates]) 
 OR ((DDY.Due_dates)=DD5.[Due_dates])
   );

[BCJourney]

Thank you for your help. I ended up doing a UNION ALL query and it works!

[Dal Jeanis]

Good enough. If you don't mind, please post the version that worked, and mark the thread solved. Top of page, under thread tools.

[BCJourney]

This is the UNION ALL query I did that worked:

SELECT Report_ID,due_dates FROM Daily_rpts_YTD_due_before_5_5_2013
UNION ALL
SELECT Report_ID,due_dates
FROM Daily_rpts_YTD_due_after_5_4_2013;