Data Compilation

[Guest]

Here's a challenge for you guys, I couldn't find any answers for this one.

I'm working with a rather extensive database at my job. I have several
tables that I am running different queries on to get different SETS of
information.

In each set of information, the only thing they share in common is an ID
number field.

Two catches. First, there are several hundred IDs I'm working with in each
set. Second, while some of the sets give me one row of information, others
may give me as many as 100 for each ID number.

My problem is this: I want to compile a form FOR EACH ID number that puts
each set of information in its own section, as well. I want MS Access to do
this in one fell swoop, meaning that once I run the form, it will create
every form for every ID.

In other words, I want the form to print this:

ID 1
Query 1 for ID 1
Query 2 for ID 1
Query 3 for ID 1

ID2
Query 1 for ID 2
Query 2 for ID 2
Query 3 for ID 2

and so on.

Is this possible?

 

[Guest]

Hi Jay

You say you want each set to "print". No worries - create a report from a
new query (with each table in the design grid) and select ID in the grouping
options. You will need to also decide on which detail you bring into each
set so that the report can also be grouped by these if you need some form of
statisitical results.

If you want to use a form to view the information then create a main form
with the masterfield being the ID (which as you say is linked). Create a
subform(using the ID as the child field) and into this insert another subform
in datasheet view with the set you need. On the main subform use the header
section to contain the ID field and don't put anything else into it other
then the 2nd subform.

 

[Guest]

While I am new to it, it sounds like you are need to use Data Shaping.

Using the Shape statement results in parent/child relationship within the
recordset.

I have not used it enough to fully explain it but I "think" it will work for
what you are asking. I'm sure someone here can explain it more if you want.