By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,656 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

Multi table/query report

P: n/a
I am trying to create a report using multiple tables.

The first table has Employee info in it (name and id's).

the next three tables have employee info for training, test, scores, etc.

I am having trouble in how to set up a report to capture all the of the
tables for one specific employee and for a date range.

If I create the report based on the Employee info in table 1 and then create
subreports I do not know how to link the dates of the training, tests, and
scores since these do not exist in table 1. I tried creating Training as a
subreport and then Test as a subreport within the Training subreport and link
the user id and dates, but I rcvd an error and Access closed.

Can someone give me some ideas on how to create this report?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200708/1

Aug 2 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
ladybug via AccessMonster.com wrote:
I am trying to create a report using multiple tables.

The first table has Employee info in it (name and id's).

the next three tables have employee info for training, test, scores, etc.

I am having trouble in how to set up a report to capture all the of the
tables for one specific employee and for a date range.

If I create the report based on the Employee info in table 1 and then create
subreports I do not know how to link the dates of the training, tests, and
scores since these do not exist in table 1. I tried creating Training as a
subreport and then Test as a subreport within the Training subreport and link
the user id and dates, but I rcvd an error and Access closed.

Can someone give me some ideas on how to create this report?
Your link would be the EmployeeID most likely. IOW, have the employeeID
in each sub report (most likely visible = False). If you right click on
each subreport and select Properties and click the data tab. In the
LinkChild/Master set to the EmpID.
Aug 2 '07 #2

P: n/a
I know how to link the subreports together by Employee id, but I don't know
how to do link the date ranges together.
Salad wrote:
>I am trying to create a report using multiple tables.
[quoted text clipped - 12 lines]
>>
Can someone give me some ideas on how to create this report?

Your link would be the EmployeeID most likely. IOW, have the employeeID
in each sub report (most likely visible = False). If you right click on
each subreport and select Properties and click the data tab. In the
LinkChild/Master set to the EmpID.
--
Message posted via http://www.accessmonster.com

Aug 2 '07 #3

P: n/a
ladybug via AccessMonster.com wrote:
I know how to link the subreports together by Employee id, but I don't know
how to do link the date ranges together.
Salad wrote:
>>>I am trying to create a report using multiple tables.

[quoted text clipped - 12 lines]
>>>Can someone give me some ideas on how to create this report?

Your link would be the EmployeeID most likely. IOW, have the employeeID
in each sub report (most likely visible = False). If you right click on
each subreport and select Properties and click the data tab. In the
LinkChild/Master set to the EmpID.

You could put
Between [Enter From Date] To [Enter To Date] if you aren't calling the
report for a form.

If calling from a form, enter
Between Forms!RptForm!FromDate and Forms!RptForm!FromDate
in the recordsource of the subreports or make the recordsource a query
that has criteria like the above.
Aug 2 '07 #4

P: n/a
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

How do I link the queries together to get this for a report?
Thank you so much for trying to help me. I appreciate any assistance!!
Salad wrote:
>I know how to link the subreports together by Employee id, but I don't know
how to do link the date ranges together.
[quoted text clipped - 9 lines]
>>>each subreport and select Properties and click the data tab. In the
LinkChild/Master set to the EmpID.

You could put
Between [Enter From Date] To [Enter To Date] if you aren't calling the
report for a form.

If calling from a form, enter
Between Forms!RptForm!FromDate and Forms!RptForm!FromDate
in the recordsource of the subreports or make the recordsource a query
that has criteria like the above.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200708/1

Aug 2 '07 #5

P: n/a
On Aug 2, 6:01 pm, "ladybug via AccessMonster.com" <u21071@uwewrote:
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)
CustomerFirstName Text
CustomerLastName Text
CustomerID CustomerFirstName CustomerLastName
1111 John Smith
2222 Joe Miller
3333 Sally Davis

tblTestTraining
TTID AutoNumber (Primary Key)
CustomerID Long (Foreign Key)
TrainingTestDate Date/Time
TrainingTestDescription Text
TestTraining Text
TTID CustomerID TrainingTestDate TrainingTestDescription 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.CustomerID, CustomerFirstName, CustomerLastName,
TrainingTestDate, tblTestTraining.TrainingTestDescription,
TestOrTraining FROM tblCustomers INNER JOIN tblTestTraining ON
tblCustomers.CustomerID = tblTestTraining.CustomerID;

!qryTestTraining:
CustomerID CustomerFirstName CustomerLastName TrainingTestDate
TrainingTestDescription 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

CustomerIDHeader: CustomerID, CustomerFirstName, CustomerLastName
TestOrTrainingHeader: TestOrTraining, Heading Labels
Detail: TrainingTestDate, TrainingTestDescription

!rptTestTraining:
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 TrainingTestDescription 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.TrainingTestDate
and on tblTestTraining.CustomerID to aid in filtering and joining.

James A. Fortune
CD********@FortuneJames.com

Aug 3 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.