Report Based on a Nested Left Join Query

[duluter]

This issue started out as a thread in the Query forum, but the focus of the question drifted more to reports, so I'm starting a new thread here.

I have three tables:

tblFarm
tblTract
tblSection

Farms can have 0, 1, or more tracts registered to them.
Farms can have 0, 1, or more sections registered to them.

Tracts and sections are independent of each other. Whether or not a farm has tracts registered to it is completely independent of whether or not the farm also has sections registered to it.

I need to create a report that lists summary data for all of the farms, like this:


FARM A

__ Acres: 25 -------------- 'IGNORE THESE THREE FIELDS, I JUST PUT
__ Legal Description: Blah - 'THEM IN HERE TO GIVE AN IDEA THAT ARE
__ County: Jefferson ------ 'MORE FIELDS IN THE REPORT.

__ Tracts ------- Sections
__ 4573 --------- 23
__ 8743 --------- 24
__ 1980 --------- 25
__ 1876
__ 0097

* Repeat for all farms in the database *

As stated above, the tracts and sections have nothing to do with one another--they're two separate lists. (They're next to each other to save space.)

Because some farms have no tracts or sections (or only one or the other), I thought I needed to create a nested left join query to grab all the farms, all the tracts associated with those farms, and all the sections associated with those farms, like this:

SELECT tblFarm.SomeUnimportantFields, tblTract.TractNumber, tblSection.SectionNumber FROM (tblFarm LEFT JOIN tblTract ON tblFarm.pkFarmID = tblTract.FarmID) LEFT JOIN tblSection ON tblFarm.pkFarmID = tblSection.FarmID

But I can't really figure out how to use the recordset from this query to create the report shown above.

Any thoughts?


Duluter

 

[georgedwilkinson]

I know so little about reports that I'm not sure why I'm responding. But, like I said earlier, it looks like Tracts and Sections are related, though you said they weren't.

If that is true, I look at this problem in a new light. I'm thinking "sub-report". Look it up in help (better for you than if I make a feeble attempt to explain it).