Querying same date parameter from multiple tables

During a client's stay with our agency, they are served among multiple contracts and are placed within multiple programs. Because clients move frequently between contracts and between programs, we have separate tables which have start and end date fields. It is common for any client to have multiple listings in these two tables, but with different event dates.

I am struggling to create a query that will capture each client's most CURRENT contract AND program. In the future, I'll want to create a parameter query that will allow the end user to enter a range of dates and capture all of the contract and program movement during a period of time.

Any suggestions?

I would use a form to select the client and the date range of interest and then using a button open a second form that has 2 subforms, one for the contracts and the other for the programs for the chosen client. Each subform would be based on a query that use the dates specified on the first form.

SELECT fields...
FROM tblPrograms
WHERE clientID=forms!firstformname!NameOfControlHoldingClientID and programstartdate between forms!firstformname!NameOfControlHoldingFirstDateOfInterest and forms!firstformname!NameOfControlHoldingSecondDateOfInterest


SELECT fields...
FROM Contracts
WHERE clientID=forms!firstformname!NameOfControlHoldingClientID and contractstartdate between forms!firstformname!NameOfControlHoldingFirstDateOfInterest and forms!firstformname!NameOfControlHoldingSecondDateOfInterest

Please don't post the same question in different sections of the forum. It winds everybody up.

Dear Wound Up...where might I find the other question? which I looked for before posting mine? but couldn't find...

lisacu said:

Dear Wound Up...where might I find the other question? which I looked for before posting mine? but couldn't find...

Click to expand...

Here: http://www.access-programmers.co.uk/forums/showthread.php?p=696013#post696013