One to Many, how handle in report ???

[gazolba]

I have to print a report on a record. This record may be pointed to by multiple other records in a different table.
I need to list all these other records that point to it, in the report.

e.g.
Main table:
ID = 999

Reference Table:
ID = 1 ref = 999
ID = 2 ref = 888
ID = 3 ref = 999

My report needs to list all the details in the Main Table plus ID 1 and ID 3 from the other table.

Would I do this in a sub-report? If so, how?

 

[dhookom]

Can't you create a query based on both tables with the Main table ID joined to the Reference table Ref? Then use this as the record source for a report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[gazolba]

I suppose I could but then I'd end up with a much bigger table since the records would be duplicated for each alternate reference. I thought there might be a better way.

 

[dhookom]

I guess I don't understand what you want your report to look like. Please enter a few sample records from your tables and then how you want them to appear in the report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[gazolba]

Main table:
ID = 900
ID = 999

Reference Table:
ID = 1 ref = 999
ID = 2 ref = 900
ID = 3 ref = 999
ID = 4 ref = 900
(there is an unlimited number of pointers to a main table record)

Report:
900
2
4
999
1
3

 

[dhookom]

Just create a report based on Reference that Groups by [Ref]. Place the Ref field in the group header and ID in the detail section.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[gazolba]

Well its not that simple. My illustration was greatly simplified. The report is much more complex than that and I have several groups of repeating items.

 

[dhookom]

I'm not sure how anyone can help if you don't provide the amount of detail that your solution requires.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.