Combining Access 2003 Queries for Dates Criteria

15 pts.
Tags:
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

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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.
    68,820 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,660 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following