EASY SQL Question

[duluter]

I have two tables: tblFarm and tblTract. The relationship between them is that multiple tracts may be associated with a single farm. However, there may be farms that have no tracts associated with them. I need to build a report that returns all the farms in the database and also shows all the tracts associated with each farm. The report will be based on a query, but I'm not sure what the query should look like. It needs to return all of the farms, regardless of whether or not they have any tracts registered to them, and all of the tracts registered to each farm.

tblFarm : FarmID[pk], FarmName
tblTract : TractID[pk], TractName, FarmID[fk]

Thanks,

Duluter (a noob)

 

[georgedwilkinson]

Use the visual query designer, select both tables and the appropriate rows, right click on the line between the tables, and select all in Farm. This will create what is called an outer join (qv).

 

[duluter]

Thanks.

It's the perfect reply. Thank you.


Duluter

 

[duluter]

A little bit harder outer join

What if I make things a little more complicated?

tblFarm
tblTract
tblSection

I need an outer join between tblFarm and tblTract. And I also need an outer join between tblFarm and tblSection. TblSection works just like tblTract -- a farm may have multiple sections registered to it, but some farms may have no sections registered to them. My report needs to show all farms and all associated tracts and sections for each farm.

How do I create a query with two outer joins like this?


Duluter

 

[georgedwilkinson]

A double outer join?

SELECT tblFarm.Farm, tblTract.Tract, tblSection.Section
FROM (tblFarm LEFT JOIN tblTract ON tblFarm.ID = tblTract.Farm) LEFT JOIN tblSection ON tblTract.ID = tblSection.Tract;

I get:

qryDoubleOuterJoin
Farm Tract Section
George's Farm Back 40
George's Farm Frontage
Fred's Farm Front 40 Barn Area
Fred's Farm Front 40 Irrigated
Fred's Farm Beulah's Land
Sam's Farm

You can use the visual designer to nest the outer joins, as long as you don't break some pretty complex rules.

HTH

 

[duluter]

Great. Now we're getting somewhere.

I now have a query with nested left joins, which gives me all the farms, all of the tracts registered to those farms, and all of the sections registered to those farms. Note that tracts and sections do not have a relationship to each other--they are independently associated with the farm. A farm can have 0, 1, or more tracts, and 0, 1, or more sections.

I think this now becomes a Report question rather than a Query question, so I may be in the wrong forum, but...

How do I get the report to show me all of the farms with each farm's tracts and sections nested under it? It's not obvious to me, even though I now have a query that has all of this information in it.

The report should look approximately 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


As stated above, the tracts and sections have nothing to do with one another--they're two separate lists.


Thanks,

Duluter

 

[georgedwilkinson]

Seems to be a logical disconnect. Your report prototype seems to indicate that tracts and sections are related but you said they're not.

Have you fiddled with the report wizard? You can do all kinds of things there and don't have to write outer joins or anything. Microsoft has some report tutorials on the Office site that should help you sort it out, if it's not intuitive using the wizard.

 

[duluter]

Perhaps the format of my response made it appear that there is a logical disconnect. Consider the Tracts list to be completely separate from the Sections list, even though they're side by side to save space.