I need to write an MS Access Report which, for a given supplier record, first lists all invoice records for that supplier and then lists all branch records for that supplier. (Note that although invoice records and branch records are both related to supplier records, invoice records and branch records are in no way related to each other.)

If I only had to list all invoice records, I would know how to do it. But with this 'double loop', I have no idea where to start. Any ideas would be welcome.

Probably the easiest way to do this would be with subreports. The record source of the parent report could be a list of all the suppliers and then you would link each of the subreports to the parent report by setting the LinkMasterFields and LinkChildFields to your supplier ID (or name or whatever your key is).

A little caveat that I just learned is that you can set the LinkMasterFields property of the subreport to be any field OR CONTROL (i.e. a calculation not in the query) but the LinkChildFields property must be set to a field in the subreport's recordsource NOT a control.

Let me know if you need any more help.

Okay, I would like to do this, but I don't know how. Is a subreport created just a regular report? I have created two reports, but don't know how to proceed next. Where do I set the LinkMasterFields and the LinkChildFields? I looked around but don't know where to do this. Also, do I do this in the parent report or the subreport, or in both? As you see, I'm new at this, so spelling it out would be very helpful. Thank you.

Ok, this I can help you with , keep asking questions like this and we will get along just fine.

Note: I just re-read the whole thread. I don' know which reports you have created, but this is what you will need.

1 parent form with information about the supplier
2 subforms, one for that supplier's invoices and one for branch records

So you will need to perform what I describe below twice, once for each subreport. You will probably just want to plop these reports down in the details section of the parent report and link on supplier id.

Using the Wizard
In Report Design mode, make sure that the Control Wizards button is checked in the toolbox, and click the subform/subreport button. And it will walk you through what you need.

Without the Wizard
In Report Design mode, uncheck the Control Wizards button, and click the subform/subreport button in the toolbox. Open Properties for the subreport you just put down and click on the Data tab. You will see all the properties you need.

Source Object - Select the subreport you created from the drop-down
LinkMasterFields - Type the name of your supplier id field (there is no drop-down here)
LinkChildFields - Type the name of the supplier id field in the subreport

Good luck!

Thank you so much. It is working. I have 2 subreports, and both are included in the parent report.

The only problem is that the two subreports overlap with each other, vertically. In other words, the second subreport begins above the end of the first subreport.

So how do I allocate sufficient room for each subreport, when I do not know in advance how many records each subreport will contain, and so I cannot know how much room to allocate for each?

Thank you.

Sorry, I don't have time to test this out right now, but try playing with the .cangrow and .canshrink properties of the subreport containers that you placed on the main report.