Multiple 'Detail' sections required?

[Tim L]

I'm really at a loss on this one. If someone knows that is has already been discussed please let me know the link, I've not managed to locate anthing suitable but perhaps that's because I'm not really sure on what is required.

I have a 'work' database that has the following tables: Jobs; Comments; Parts.

I can produce a report that contains all of the Part OR all of the Comments, but not both.

The tables are linked by a Job.ID field, and I can get a query to run that will return all of the Comments and Parts. Some jobs have few parts and lots of comments, whilst for some it is the other way round...

I have tried using the Footers And Headers in Sorting and Grouping but only ever get the first record of one of the Tables, depending on which one I chose to put in the 'Detail' section, its the other one that only shows one entry for.

Hopefully I've described this clearly enough.

Any solutions provided will need to work in Access 97 (Yes 97! - I wish they'd upgrade...)

Tim

 

[Pat Hartman]

You have two separate 1-many relationships. You cannot create a single query to retrieve this data in any meaningful way. You need to use a main form for the Jobs data and two subforms. One for comments and one for Parts. Place both subreports in the detail section of the main report below the Jobs fields. Make sure that the can shrink/can grow properties of all controls and the detail section are set to yes.

 

[Tim L]

You have two separate 1-many relationships. You cannot create a single query to retrieve this data in any meaningful way. You need to use a main form for the Jobs data and two subforms. One for comments and one for Parts. Place both subreports in the detail section of the main report below the Jobs fields. Make sure that the can shrink/can grow properties of all controls and the detail section are set to yes.

Thanks Pat, I'd not considered using a SubReport, DOH! I've used SubForms many times!

Thanks again.

Tim