Combining Access 2003 Queries for Dates Criteria
Access 2003, Access 2003 date query, Access query, Access Tables, Windows XP Professional, Windows XP Professional SP3
I have a MainInfo table and 3 different Log tables (PleadingsLog, LitigationLog and DiscoveryLog) that are linked to the MainInfo table by an IDnumber. Each of the log tables contains a date field and a notes field.
I am trying to find a way to query all records from the MainInfo table that show a date within the next 30 days in ANY of the 3 associated log tables. I have been able to query the records through each of the Logs individually, but not all together.
I tried adding all 3 log tables to the query, and I got the following results: (1) when I added the date criteria to the date field from LitigationLog, I got multiple returns for the record due to other dates entered in the other logs, or (2) if I enter the same date criteria in each of the date fields (from each of the 3 Log tables), it returns no records because there are no records that have a future date in ALL of the related logs tables. Any help is sincerely appreciated.
Software/Hardware used:
Windows XP Professional, Access 2003
I am trying to find a way to query all records from the MainInfo table that show a date within the next 30 days in ANY of the 3 associated log tables. I have been able to query the records through each of the Logs individually, but not all together.
I tried adding all 3 log tables to the query, and I got the following results: (1) when I added the date criteria to the date field from LitigationLog, I got multiple returns for the record due to other dates entered in the other logs, or (2) if I enter the same date criteria in each of the date fields (from each of the 3 Log tables), it returns no records because there are no records that have a future date in ALL of the related logs tables. Any help is sincerely appreciated.
Software/Hardware used:
Windows XP Professional, Access 2003
ASKED:
December 11, 2010 12:43 AM
UPDATED:
December 14, 2010 9:33 PM
RELATED QUESTIONS
Answer Wiki:
I think that you need UNION instead of JOIN. Ex.
<pre>select * from (
select * from PleadingsLog
union all
select * from LitigationLog
union all
select * from DiscoveryLog
) as x
where <date criteria></pre>
To see all answers submitted to the Answer Wiki: View Answer History.
I agree, a UNION should be what you are looking for.
Note than applying the criteria to each individual subquery could be more efficient.
2 Carlosdl
> Note than applying the criteria to each individual subquery could be more efficient.
I agree in the general case, but SQL Server optimizer gives identical execution plans (with predicate inside).
Thank you for your responses. I have never used a Union Query before. I created the Union Query using SQL and did use the WHERE condition for the dates for each subquery. It worked beautifully. Thank you again!