General Date Query

[skydiver]

Access 2010. I have three unrelated tables. Each table has it's own Date Field. There are three fields in each table I want to query. Instead of using any of the date fields for any of the tables, can I create a general date field in order to pull data for the specified records entered on any particular date? I want the query (using a date range) to find any one particular date that has data in the specified fields/records.

Example:

Table1
EntryDate
Field1
Field2
Field3

Table2
ProcessDate
Field1
Field2
Field3

Table3
FilingDate
Field1
Field2
Field3

 

[plog]

Build a UNION query (http://www.techonthenet.com/sql/union.php) from your 3 sources, then use that query as the basis for your search.

 

[skydiver]

A bit chinese for me. Could you break this down a little further based on what I've provided? Greatly appreciated!!!

 

[skydiver]

I've written my SQL statement as such (below), however I'm getting a syntax error (incomplete query clause). Can you please review? Thanks!

SELECT Field1, Field2, Field3
FROM Table1
UNION ALL
SELECT Field1, Field2, Field3
FROM Table2
UNION ALL
SELECT Field1, Field2, Field3
FROM Table3;
UNION ALL

 

[jdraw]

Try

SELECT Field1, Field2, Field3
FROM Table1
UNION
SELECT Field1, Field2, Field3
FROM Table2
UNION
SELECT Field1, Field2, Field3
FROM Table3;

 

[skydiver]

Okay, here are my real fields/tables:

SELECT I407, I181, MS92
FROM Archived Records
UNION
SELECT I407NotTraveling, I181NotTraveling, MS92NotTraveling
FROM Administrative
UNION
SELECT I407Traveling, I181Traveling, MS92Traveling
FROM Main;

The error message I'm getting is:

"The Microsoft Access database engine cannot find the input table or query 'Archived'. Make sure it exists and that its name is spelled correctly."

Also what about a date range? Will that come later?

 

[JHB]

If table names or field names include a space you must have [] around the name.

So put [] around table name "Archived Records", ([Archived Records])

 

[jdraw]

Is there really a space in this name? Archived Records
If so enclose it with [ ]