Linking two tables based on date field

[slenish]

Hello,

Im having an issue right now where im trying to link two tables in a query based on the date and then output the results to a report.

What I have is a form set up to where you enter a date range. With one table its not a problem but i need to pull information from another table as well. The only way I can see to link the two tables is on the date. Reason being because since im pulling from a date range I want to include whats on table 2(secondary) along with table 1(main). Im trying to figure how to make this work with my query. At the moment I have a Where Criteria for the date on the main table. Can I use just that field and some vba so it knows to pull the date from the other table as well? Or do i need to add another Date field on the query for the second table and a new Where Criteria function?

Any help would be great!

Thanks

[jimatqsi]

You don't say what relationship the two tables have to each other. If the two tables are directly related (i.e. Invoice header and invoice detail) then the natural link would be the invoice number. Filter the header by date range and link by invoice number and everything is good, no need to further filter the invoice detail by date, right?

In your case, how are these tables related to each other? If you can answer that, then I think you can answer the question of how to pull the data from the second table. Filter the first table by date and pull the data from the second table based on some identifier (order#? employee#? some other key?).

Hope that helps,
Jim

[slenish]

Hi Jim,

Appreciate the response. Well the tables are linked by a facility name, but what i need to do is pull a facility name based on the date. This is actually the third table im trying to link in. What im doing is pulling the information based on facility name and date range. I have two tables that pulls by that information already and im trying to link in a third table. What happens is when i try to pull the information on the third table, if I put in a date for say May and then a date for June (on the table) it trys to pull all of the information when I link by facility name. If I link by date it pulls none of the information.

any ideas??