Access query on 1 to many relationship

[belangerg]

I have a one to many relationship between a building table and a building attributes table as shown below:

BLD_ID BLD_NAME
1 Warehouse1
2 Warehouse2


ATTRIBUTE_ID BLD_ID ATTRIBUTE
1 1 Loading Dock
2 1 Fenced Compund
3 2 Loading Dock
4 2 Retail Space

I want to create a query that would return all buildings that had a loading dock and a fenced compound.

 

[PHV]

What have you tried so far and where in your SQL code are you stuck ?
A starting point:
SELECT * FROM [building table]
WHERE BLD_ID IN (SELECT BLD_ID FROM [building attributes table] WHERE ATTRIBUTE='Loading Dock')
AND BLD_ID IN (SELECT BLD_ID FROM [building attributes table] WHERE ATTRIBUTE='Fenced Compund')

Another way:
SELECT B.*
FROM ([building table] B
INNER JOIN [building attributes table] C ON B.BLD_ID=C.BLD_ID)
INNER JOIN [building attributes table] D ON B.BLD_ID=D.BLD_ID
WHERE C.ATTRIBUTE='Fenced Compund' AND D.ATTRIBUTE='Loading Dock'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[belangerg]

Thanks - thats all I needed.

 

[fneily]

one to many relationship". Technically, you have a many-to-many relationship. Look at Loading Dock. A building can have many attributes and a single attribute(Loading Dock) can belong to many buildings.
But if there's no other info kept on the attributes, you wouldn't create another table. That's over normalizing. It's like directions, you wouldn't have a table with East, West, South, North.
Just info.

 

[belangerg]

Not really. A building can have 0,1 or many building attributes related to it. There are other fields on the building attribute table. A building attribute record relates to one and only one building (hence the building_Id field on the Building attribute table. I do have an attributes table that lists all the possible attributes a building can have.

 

[PHV]

I suspect you should have a schema like this:[tt]
tblBuilding
BLD_ID(PK) BLD_NAME
1 Warehouse1
2 Warehouse2

tblAttribute
ATT_ID(PK) ATT_NAME
1 Loading Dock
2 Fenced Compund
3 Retail Space-

tblBuildingAttribute
BLD_ID(FK) ATT_ID(FK) other fields
1 1 ...
1 2 ...
2 1 ...
2 3 ...
PKBLD_ID,ATT_ID)[/tt]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[belangerg]

That is correct. I do have a schema like that. Now, how do you write a query that lists all buildings that have loading docks and fenced compounds?

PHV has given me a solution. Is the a better way?

 

[fneily]

Not really. A building can have 0,1 or many building attributes related to it."
Really. An attribute can be assigned to more to one building. PHV has shown the way to resolve this many-to-many relationship.
"That is correct. I do have a schema like that."
So, I guess, you did it right, but for the wrong reasons.

(sometimes I should just keep my typing shut)