Two queries / one report

[Stephanie.D]

'Two queries / one report' seems to be a common topic, but I still can't find the information I need.

Normally I am pretty good at finding answers through google or by searching a forum like this one, but in this case I don't know exactly what I'm looking for help on.

Let's say I have two tables that look like this:

Employees
---------
ID
Name
Hire Date


Absence
-------
ID
Date
Reason


So there is a one-to-many relationship and the tables link on ID.

I want a report in which each page looks a little something like this:

=====
Name: John Smith
ID: 123

Absences:
Dec 12, 2006 sick
Dec 18, 2006 car trouble
Dec 22, 2006 cat died
=====

I think that putting all these fields into a single query and feeding that into a report would give me a separate page for each absence, would it not?

I see the word "subreport" thrown around now and then, maybe that is what I'm looking for, with two queries . . ?

In case it matters, there are a couple other associated tables similar to Absences that I would like to include later on, so I'm looking for a solution that will work with more than one linked table.

Any help would be greatly appreciated. Thanks very much in advance.

-SD

 

[boblarson]

You should be able to get what you want by using the Grouping and Sorting and then putting the person's name as a grouping level and then putting the other stuff in the details section.

 

[Stephanie.D]

Thanks for the quick reply.

With just two tables, it seems to give me what I need. (see first screen shot)

When I add another table (courses taken) there is a problem - it shows each occurance of an absence as many times as there are courses for that person. (second screen shot)

With the extra table added, I would like the report to look like:

Name: John Smith
ID: 123

Absences:
Dec 12, 2006 sick
Dec 18, 2006 car trouble
Dec 22, 2006 cat died

Courses:
NS01
NS02
Ns03

Thanks again..

BTW, I am creating these reports by using the Report Wizard and selecting fields right from the tables, not from a query.

 

[boblarson]

Yes, to bring the courses into it you will need a sub report. Create a sub report which pulls the courses for each person and then place that report into the details section and tie the Master / Child links to the person id.

 

[Stephanie.D]

Create a sub report which pulls the courses for each person and then place that report into the details section and tie the Master / Child links to the person id.

Your suggestion sounded somewhat challenging but after an hour of googling and playing around it Access, it worked just fine. Thanks!