parallel one to many multi-table query

[Guest]

I’m having trouble with a multi-table query. I’d like to pull records from
tblProducer based on data in tblFarmstead and tblLivestock. To be in the
database, a producer must have one or more farmsteads, or one or more
livestock system, or one or more of each. The relationships are shown below

tblProducer.PrimaryKey––––>>tblFarmstead.ForeignKey––>>etc
|
|––––––––––––––>>tblLivestock.ForeignKey––>>etc

I’m trying query for all the records in tblProducer for which there are
records in either tblFarmstead, or tblLivetstock, or both, that meet certain
criteria.

When I try to query based on the relationship above, it’s clear Access is
first finding records in tblProducer that have matching records in
tblFarmstead AND tblLivestock. It then applies the criteria.

I can do this in two queries, and then append records, etc. I just thought I
should be able to do this in one query.

Any suggestions are appreciated.

 

[Marshall Barton]

I’m having trouble with a multi-table query. I’d like to pull records from
tblProducer based on data in tblFarmstead and tblLivestock. To be in the
database, a producer must have one or more farmsteads, or one or more
livestock system, or one or more of each. The relationships are shown below

tblProducer.PrimaryKey––––>>tblFarmstead.ForeignKey––>>etc
|
|––––––––––––––>>tblLivestock.ForeignKey––>>etc

I’m trying query for all the records in tblProducer for which there are
records in either tblFarmstead, or tblLivetstock, or both, that meet certain
criteria.

When I try to query based on the relationship above, it’s clear Access is
first finding records in tblProducer that have matching records in
tblFarmstead AND tblLivestock. It then applies the criteria.

I can do this in two queries, and then append records, etc. I just thought I
should be able to do this in one query.

Use outer joins and check if there are any matching records
in the other tables.

SELECT tblProducer,flda, tblProducer.fldb, . . .
FROM (tblProducer
LEFT JOIN tblFarmstead
ON tblFarmstead.ForeignKey = tblProducer.PrimaryKey)
LEFT JOIN tblLivestock
ON tblLivestock.ForeignKey = tblProducer.PrimaryKey
WHERE tblFarmstead.ForeignKey Is Not Null
OR tblLivestock.ForeignKey Is Not Null

 

[Guest]

Thank you very much. It worked like a charm. This will help me with another
database I'm working on as well. I have trouble wrapping my brain around the
logic of outer joins sometimes.