Filtered Report from multiple tables

[DStrong]

I am running this DB as a Access 2000 db and on Win XP Pro machines.

I have a few tables that all have data that I need on one report. I have a
main table I will call the MainTable and for this example SubTable1 and
SubTable2.

The ManTable have a primary key called RecordID.
The SubTables all have a primary key for each record in it and a field
called REC ID that is a relationship to RecordID.
Each SubTable can have multiple records associated with the REC ID.

What I am attempting to do is have a report display the MainTable data then
all records that are tied to the RecordID fromt he SubTables.

What I have tried is to do a query to pull in all the data from all tables,
this did not work too well. So I built a report for MainTable, a report for
SubTable1 and SubTable2. Then I opened MainTable Report and embedded
SubTable1. The problem is that all the data from SubTable1 displays on the
each record from my MainTable. I only want to see the data from SubTable1
that relates to the RecordID from the MainTable.

Does this make sence to anyone? Has any ever successfully done something
like this before?

Please Help.

 

[Jeff Boyce]

In order to get all the data for the report, the query HAS to connect the
Main table data to each instance of the SubTable data. But you don't need
to display it (again and again) in the report.

For example, if you build your report definition to use Sorting & Grouping,
you can group by the RecordID (i.e., Main record), and display however many
SubTable records are available in the report's Detail section.

Good luck

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[DStrong]

Jeff-
Thanks, the problem I am having is that I only want a report from just one
record from my MainTable and any associate data from my SubTables. I have not
been able to figure out how to accomplish this. I am relatively new to Access
and not the best with it. Please offer a resource that I can go to for more
detailed steps or if you care to, please offer some more steps here. Thanks.

 

[Jeff Boyce]

David

A common approach to ordering "just one" record in a report is to use a
form!

Create a form that has an unbound combobox that lists all records (main
table). For example, if your main table held student information, the
combobox would list the students.

Add a command button to the form, and in the Click event for that button,
use the DoCmd.OpenReport command.

Check Access HELP for the exact syntax, because one of the pieces of that
syntax allows you to specify a "WHERE" clause, in effect, 'filtering' the
report. You'll need to use that portion to set the RecordID =
Me.cboYourCombobox to have it use the selected record in the combobox.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Duane Hookom]

I think the solution is to use the Link Master/Child properties of the
subreport controls to "tie" the subreport records to a particular record in
the main report.

 

[DStrong]

Duane-
That was the help that I needed! It took a bit to find that in the
properties, but once I did it all workes just as I had hoped it would.

Now my last step is to open only one from that I am on from a form. I have a
form that allows the users to enter the new data to the DB and I want them to
be able to print out a hardcopy of the info they entered.

Also, just an astheics question, is there a way to put a rectangle box
around the subreport that is dynamic in resizing?

 

[DStrong]

OK, I post my thanks and one follow-up question then I solve it on my own. I
have the form opening just a single report record issue soleved.