Display report showing fields corresponding to a date entered by user

[MaggieE]

Help please!

I have 4 different tables with a dozen of fields and 4 corresponding forms filled up by the user. Each table has a Date field. I need to get a report where the user enters a date and the report displays the corresponding fields from each table (EmployeeName, Shift…).
So, the user types a date and the report gives:

TableA.EmployeeName TableA.Shift
TableB.EmployeeName TableB.Shift
TableC.EmployeeName TableC.Shift
TableD.EmployeeName TableD.Shift

I made a query with all those fields and for the 4 Date fields the criteria is: Like "*" & [Enter Date] & "*"
It works if all 4 tables have the same dates in a same order. If not, it returns every combination
Row1TableA, Row1TableB, Row1TableC…
Row2TableA, Row1TableB, Row1TableC…
…
Row1TableA, Row3TableB, Row1TableC…

What I need is the report to display the fields from the 4 tables for a same date. A same date appears twice in each table because an employee fills up the form on day shift then a different employee on night shift. I basically want to know if the employee has filled up the form; if not, the report would display a blank field for that date:

TableA.EmployeeName TableA.Shift
TableB.EmployeeName TableB.Shift
‘blank (TableC’s form has not been filled up on that date)


TableD.EmployeeName TableD.Shift

Any suggestion would be really appreciated!

Thank you

Maggie

[June7]

Why do you have 4 tables? What is the data structure? Are these tables really different in structure?

If you want a record for every date for every table, then need a dataset of every possible date.