Need help to get (a common) particular Output format

  • Thread starter Thread starter Gcook888
  • Start date Start date
G

Gcook888

I have primary table with 2 fields that make up primary key and also the
foreign key into a detail table. It is a 1 to many relationship.

So instead of a straight inner join in which the primary record is repeated as
many times as there are detail records I want the report output to go like:


PrimaryRec1 Detail1 for PrimaryRec1
Detail2 for PrimaryRec1
:
:
DetailN for PrimaryRec1

PrimaryRec2 Detail1 for PrimaryRec2
Detail2 for PrimaryRec2
:
:
DetailM for PrimaryRec2

:
:

If I GROUP BY on my primary records it seems I can only get the min or max (or
something, just 1) detail.

My RecordSource is the INNER JOIN of the 2 tables.

Thanks a lot if this is clear and you can help.
 
You would not group by in your query if you want all the details. Sort and
Group by your primary records in your report and set some of the text boxes
to Hide Duplicates.
 
Gcook888 said:
I have primary table with 2 fields that make up primary key and also the
foreign key into a detail table. It is a 1 to many relationship.

So instead of a straight inner join in which the primary record is repeated as
many times as there are detail records I want the report output to go like:


PrimaryRec1 Detail1 for PrimaryRec1
Detail2 for PrimaryRec1
:
:
DetailN for PrimaryRec1

PrimaryRec2 Detail1 for PrimaryRec2
Detail2 for PrimaryRec2
:
:
DetailM for PrimaryRec2

:
:

If I GROUP BY on my primary records it seems I can only get the min or max (or
something, just 1) detail.

My RecordSource is the INNER JOIN of the 2 tables.
Click to expand...

I think you're confusing a group by query with a report's
Sorting and Grouping.

Get rid of the Group By in the report's record source query.

Then open the report in design view and use the View menu to
display the Sorting and Grouping window. Specify the field
you want to group on under Field/Expression. If you have
multiple fields that are to be treated as a single entity,
then use an expression instead of a field name, yours might
be something along these lines:
=firstpkfield & "/" & secondpkfield

Use the properties in the bottom part of the window to
specify if you want a header and/or footer section for the
group.
 
Back
Top