Report using many to many relationship

[Bob M]

I have 4 tables set up as follows:

tblEmployee
EmployeeID
FullName
Department

tblInventory
EmployeeID
IDTag
Description
SerialNumber

tblSoftwareModuleUser (Join table)
EmployeeID
PackageID

tblSoftwareModule
PackageID
PackageName

tblEmployee is linked to both tblInventory and tblSoftwareModuleUser via EmployeeID. TblSoftwareModule is linked to tblSoftwareModuleUser via packageID.

I need to have a report which shows each user on a separate page with the info from the tblEmployee table with the equipment from tblinventory which she/he is currently using and all the software residing on the machine. I'm having trouble supressing duplicates on the report. Since I have the join table because of the many to many relationship between tblEmployee and tblSoftwareModule the inventory fields tend to repeat for every software module.
Any help would be greatly appreciated.

 

[pdx_man]

You need to use the Sorting and Grouping options in the Report. So, your first group would be Employee (Have a Group Header) Check this out under Access Help.

 

[Bob M]

Report with many to many

I've already tried the sorting and grouping. Sorry I didn't mention that. I have Employee sirst and that works fine. The problem come in with Inventory and Software. When I put Inventory second and software thrid, software repeats. When I reverse that the inventory repeats.

I have also tried hiding repeat records in the format option for the fields.

 

[Bob M]

Report with many to many

I should also mention that when I report Inventory and Software separate with the User info, the reports work fine. The problem comes in when I try to combine the Inventory and Software on the same report with the user info.

 

[pdx_man]

Are you putting the controls in the group headers when using the Sorting and Grouping? They will group on each unique value, so ...
Can you post your DB here in A97 format?

 

[Bob M]

Report many to many

I'm using Access 2002 I can't post it in Access 97. I'll need to take it home where I have 97 and post it from there. I can send you the report. It may give you a little insight into my problem.

 

[Pat Hartman]

You have two many-to-many relationships and they are not related to each other. You cannot build a query that combiles them.

You'll need to use subreports to solve the problem. Main report will be based on employee data and you'll have one subreport for hardware and a second for software.

 

[Bob M]

Report with many to many

Thanks Pat. I followed your suggestion on using subreports and it worked beautifully once I got past some formatting issues.

Thanks so much for all your help!