Combining Access 2003 Queries for Dates Criteria

GCIB
15 pts.
Tags:
Access 2003 date query
Microsoft Access 2003
Microsoft Access queries
Microsoft Access tables
Windows XP Professional
Windows XP 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
Asked: Last updated:
Related Questions

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    I agree, a UNION should be what you are looking for. Note than applying the criteria to each individual subquery could be more efficient.
    84,495 pointsBadges:
    report
  • msi77
    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). :-)
    1,670 pointsBadges:
    report
  • GCIB
    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!
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: