To Do List Query (1 Viewer)

[onamission5]

Hi all,
Im looking to create a simple "To Do List" query in Access 2003. Basically I have 2 tables each containing the usual Reference/ID numbers as well specific fields for both tables. Each table also contains a "Date" field.

Is there anyway I can produce a query which will display records from both tables on a certain date in the same query?
For example Table 1 - All records dated 12/11/2008. Table 2 - All records dated 12/11/2008.

Any advice is much appreciated.

Thanks

 

[WayPay]

You can use a UNION query to combine results from two unrelated tables:

SELECT t1.fldName, t1.fld2, "" as fld3 FROM t1 WHERE t1.fldDate = #1/1/2008#
UNION
SELECT t2.fldName, "", t2.fld3 FROM t2 WHERE t2.fldDate = #1/1/2008#

That will:
- combine t1.fldName and t2.fldName in the first column,
- show t1.fld2 in the second column (and nothing for t2), and
- t2.fld3 in the 3rd column.

You can combine columns when they're the same data type.

 

[onamission5]

Firstly, thanks for the quick response.

I will describe the structure of the 2 tables in more detail.

Jobs Placed Table
Jobs Placed Ref (Autonumber/PK)
Company (Text)
Date (Date/Time)
Position (Text)
Job Description (Text)

Jobs Proposed Table
Jobs Proposed Ref (Autonumber/PK)
Company (Text)
Date (Date/Time)
Position (Text)
Job Description (Text)

If I wanted to produce a query that pulls the Company and Position columns from both tables on a specific date would I be able to use the same SQL coding?

So it would look something like this?

To Do Query
Date Company Position
12/11/2008 Test Company Tester
12/11/2008 Test Company 2 Tester 2

Also If I could distinguish whether it is a Proposed Job or a Placed Job that would be helpful.

Many thanks

 

[Alansidman]

I am not sure why you need two tables when one would work in this situation. Eliminate one table. In the other, add a field with the option "Proposed" or "Placed." If you need a second date to distinguish "Date Placed" from "Date Proposed," add it. You should change your field names to eliminate spaces. This will save you aggravation in the future. Additionally, the term "Date" is a reserved name in Access. Use of it may cause issues in the future. Change it.

Now that you have one table, you can do a query on the table specifying "Proposed" or "Placed" in your criteria.

Alan

 

[dkinley]

You should change the Date name anyhow since this is a reserved word in Access.

http://www.allenbrowne.com/AppIssueBadWord.html

-dK

 

[onamission5]

Thanks for the replies.

The reason there are 2 tables is because each table contains additional columns (which I have not listed here as they were not necessary for this query) which are unique to each table.

I will remove all spaces in column/table names and I will also rename the "Date" columns!

Thanks