Gather dates from multiple tables and put them into a single form

[revolution9540]

I have different tables for different types of IT equipment, such as desktops, laptops, printers, etc. Each item in each one of these tables has a Warranty Expiration Date. I would like to create a form that queries based on whether a date from any of these tables are approaching in 1 month or already passed. The user will have three options:

1. View active warranties (dates that are more than one month away from the current date)
2. View warranties expiring soon (dates that are 1 month away)
3. View expired warranties (dates that have already passed)

The Username of the person who uses the items is the primary key used to link the database together. I would like each item that matches one of the appropriate date selections above to be listed in a form, along with the name of the person that owns the item. How could I go about accomplishing this?



My issue is that I want to search and display dates from all tables without involving the primary key (Username) which is the basis for all relationships between tables. Please let me know if anyone has suggestions with how to get started.

[ranman256]

DONT store them in separate tables, because then you must run queries on X number of tables.
Store everything in 1 table, they all have make,model, serial#,
just add the field [EqType] (laptop, PC, fax, monitor,etc)

that way 1 query to view warranties expiring
1 query to show inventory

days before warrenty expires:
criteria = dateDiff("d", [warrentyExpDate],date()) < 31

expired:
criteria = [warrentyExpDate]<date()

[revolution9540]

DONT store them in separate tables, because then you must run queries on X number of tables.
Store everything in 1 table, they all have make,model, serial#,
just add the field [EqType] (laptop, PC, fax, monitor,etc)

that way 1 query to view warranties expiring
1 query to show inventory

days before warrenty expires:
criteria = dateDiff("d", [warrentyExpDate],date()) < 31

expired:
criteria = [warrentyExpDate]<date()

It would be way too much work for me to put my entire database into one table just so I can have the convenience of using one query. Access is very dynamic, I'm sure there is a better way. Is there not?