April 20, 2007 at 9:49 am
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 is here:
http://img442.imageshack.us/img442/7848/untitledfr9.jpg
My current thinking is something along the lines of:
SELECT B1.BuildingID FROM Building B1
WHERE
(
SELECT R3.AttributeID
FROM Tenant AS T3 INNER JOIN Requirements AS R3 ON T3.TenantID=R3.TenantID
WHERE (R3.TenantID=[enter TenantID])
)
IN
(
SELECT BA2.AttributeID
FROM Building AS B2 INNER JOIN BuildingAttribute AS BA2 ON B2.BuildingID=BA2.BuildingID
WHERE (B2.BuildingID=B1.BuildingID)
);
Although as I'm sure you can tell, I am somewhat an SQL novice.
Thanks in advance, Andrew.
April 22, 2007 at 12:08 pm
Shouldn't the Building table appear in the second Select clause too?
April 23, 2007 at 4:17 am
Maybe:
DECLARE @TenantID int
SET @TenantID = 1 -- ID for Tenant
SELECT B.*
FROM Building B
JOIN (
SELECT BA1.BuildingID, COUNT(*) AS MatchingAttributes
FROM Requirements R1
JOIN BuildingAttribute BA1
ON R1.AttributeID = BA1.AttributeID
AND R1.TenantID = @TenantID
GROUP BY BA1.BuildingID
) D1
ON B.BuildingID = D1.BuildingID
JOIN (
SELECT COUNT(*) AS TenantAtttributes
FROM Requirements R2
WHERE TenantID = @TenantID
) D2
ON D1.MatchingAttributes = D2.TenantAtttributes
April 27, 2007 at 7:46 am
Andrew, seeing as this is MS Access why not just use the query builder
I simplified the columns provided for the tables in this demo, but it will return the answers you are looking for (this is based on the ERD provided:
place this into a query using the SQL view and switch to design view to show the graphical representation of the query
SELECT Tenant.TenantID, Tenant.TenantName, Building.BuildingName, Building.BuildingAddress1
FROM Building INNER JOIN ((Tenant INNER JOIN Requirements ON Tenant.TenantID = Requirements.TenantID) INNER JOIN BuildingAttribute ON Requirements.AttributeID = BuildingAttribute.AttributeID) ON Building.BuildingID = BuildingAttribute.BuildingID
WHERE (((Tenant.TenantID)=[Enter ID]));
The MS Access SQL syntax does not match SQL Server exactly, but it's pretty close ... to allow you then to upgrade to a stored procedure or view if necessary
You didn't say if you were using MS Access as a data project or a JET database, the example I have provided is for a JET database but would not take much modification for SQL Server, mostly, the MS Access method of accepting parameters
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
