Utterly Stuck with SQL

[Abyss]

I am trying to create a database in MS Access, and am completely stuck at one point. It would really be appreciated if anyone could help:

Basically a tenant has multiple "requirements", which are just attributes that they need.

A building has "buildingAttributes", which are attributes that a building has.

For a specific tenant, I want to be able to list the buildingIDs that meet the tenants requirements.

It keeps feeling like i'm 90% of the way there, but it keeps failing to work .

(Section of entity relationship diagram relevent to problem)

Any help would be really appreciated

Thanks, Andrew.

 

[Abyss]

HTML, Graphics and Programming would be a better place for this

Doh! Cheers. Didn't realise there was even such a forum (just checked and my software tab is minimised). Could a mod possibly move it for me please?

 

[Abyss]

Thanks for the help everyone.

The last one seems the most promising, but I can't get it to work.

I assume you meant

from buildingAttribute b, requirements r,
as there is no such thing as b.attributeid.

Even changing that doesn't get it working though

 

[Abyss]

This is what I have so far:

SELECT DISTINCT (b.buildingid)
FROM building b, buildingAttribute ba, requirements r
WHERE ba.AttributeID = r.AttributeID
AND r.TenantID = [Enter TenantID]
AND 1 NOT IN 
(select 1 from requirements r2, building b2
                  where r2.tenantid = r.tenantid
                  and r2.attributeid NOT IN
                        (select ba2.AttributeID from buildingattribute ba2
                         where b2.buildingid = b.buildingid));

Doesn't work though

 

[Abyss]

Thats brilliant JIMA, cheers! The above code was pretty much spot on bar a few modifications to get it to work how I wanted it to. I can finally get onto implementing the rest of the system!

Thanks again!

Also thanks to everyone else who provided ideas and offers to help solve the problem.

Andrew.