Query for same fields, different dates (1 Viewer)

[robina]

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]));

 

[plog]

You kind of lost me with the explanation, but this struck me:

In other words, I am trying to return all results from both queries

To achieve that you want a UNION query (http://www.techonthenet.com/sql/union.php). Check that out and if its not what you want post sample data from your sources and then what you want to end up with based on that sample data. Use this format:

Source1Name
Field1Name, Field2Name, Field3Name
17, David, 4/17/2013
2, Jennifer, 5/15/2010

 

[robina]

plog,
Thank you for your help. I created a UNION ALL query and it works beautifully. I appreciate your time.