On Aug 2, 6:01 pm, "ladybug via AccessMonster.c om" <u21071@uwewrot e:
I have that as the criteria for the date as well, but I do not know how to
link the different queries together by date without it prompting me for the
date range 4 times. I might need to be more specific.
First table:
Customer ID First Name Last Name
1111 John Smith
2222 Joe Miller
3333 Sally Davis
Second table:
Customer ID Training Date Training Description
1111 07/01/07 How to Tie Your shoe
1111 07/02/07 Learning the Alphabet
2222 07/02/07 Learning the Alphabet
Third table:
Customer ID Test Date Test Description
1111 07/10/07 Presidents
2222 07/10/07 Presidents
1111 07/15/07 Countries
Ok, now I want a report that the user can enter in the employee id and date
range and the information for only that employee within that date range is
returned. So for example, if I entered in 1111 for employee id and 07/01/07
as begin date and 07/10/07 as end date in the criteria I want returned
Employee ID: 1111 First Name: John Last Name: Smith
Training
07/01/07 How to Tie Your shoe
07/02/07 Learning the Alphabet
Tests
07/10/07 Presidents
If you have control over the tables and their structure, I would
combine the information from your second and third tables. This
eliminates the UNION and makes the SQL much easier if a third category
is added. Then I might do something like:
tblCustomers
CustomerID AutoNumber (Primary Key)
CustomerFirstNa me Text
CustomerLastNam e Text
CustomerID CustomerFirstNa me CustomerLastNam e
1111 John Smith
2222 Joe Miller
3333 Sally Davis
tblTestTraining
TTID AutoNumber (Primary Key)
CustomerID Long (Foreign Key)
TrainingTestDat e Date/Time
TrainingTestDes cription Text
TestTraining Text
TTID CustomerID TrainingTestDat e TrainingTestDes cription TestTraining
1 1111 07/01/07 How to Tie Your Shoe Training
2 1111 07/02/07 Learning the Alphabet Training
3 2222 07/02/07 Learning the Alphabet Training
4 1111 07/10/07 Presidents Tests
5 2222 07/10/07 Presidents Tests
6 1111 07/15/07 Countries Tests
qryTestTraining :
SELECT tblCustomers.Cu stomerID, CustomerFirstNa me, CustomerLastNam e,
TrainingTestDat e, tblTestTraining .TrainingTestDe scription,
TestOrTraining FROM tblCustomers INNER JOIN tblTestTraining ON
tblCustomers.Cu stomerID = tblTestTraining .CustomerID;
!qryTestTrainin g:
CustomerID CustomerFirstNa me CustomerLastNam e TrainingTestDat e
TrainingTestDes cription TestOrTraining
1111 John Smith 7/1/2007 How to Tie Your Shoe Training
1111 John Smith 7/2/2007 Learning the Alphabet Training
1111 John Smith 7/10/2007 Presidents Tests
1111 John Smith 7/15/2007 Countries Tests
2222 Joe Miller 7/2/2007 Learning the Alphabet Training
2222 Joe Miller 7/10/2007 Presidents Tests
rptTestTraining :
Report Sorting and Grouping: CustomerID Ascending, TestOrTraining
Descending
CustomerIDHeade r: CustomerID, CustomerFirstNa me, CustomerLastNam e
TestOrTrainingH eader: TestOrTraining, Heading Labels
Detail: TrainingTestDat e, TrainingTestDes cription
!rptTestTrainin g:
1111 John Smith
Training
7/2/2007 Learning the Alphabet
7/1/2007 Howto Tie Your Shoe
Test
7/15/2007 Countries
7/10/2007 Presidents
2222 Joe Miller
Training
7/2/2007 Learning the Alphabet
Test
7/10/2007 Presidents
Note: Even though the TrainingTestDes cription field contained "How to
Tie Your Shoe," the A97 report displayed "Howto Tie Your Shoe." Maybe
Access is trying too hard to be helpful. I am working on a similar
problem tonight where I have to keep track of safety training for
employee badges (e.g., HiLo training or Ladder Safety), plus
certification training such as Weld or Laser Inspection. Anyone
operating equipment can be stopped to see if their safety badge allows
them to use that equipment. I left the date range specification out
of the example. It can be placed in the SQL (using PARAMETERS, WHERE,
BETWEEN, etc.) or you can use Salad's dynamic string Filter idea.
Remember to put non-unique indices on tblTestTraining .TrainingTestDa te
and on tblTestTraining .CustomerID to aid in filtering and joining.
James A. Fortune
CD********@Fort uneJames.com 
