SQL Efficiency 1

[LochDhu]

For those SQL guru's out there - I was wondering if/how I can be more efficient with my SQL statements. I have the following four Access tables.


tblBuildings
Bldgid - Primary Key
BldgDesc - description of building

tblRoomMaster
RoomID - Primary Key
BldgID - Foreign key (tblBuildings)
RoomUse - Foreign Key (tblRoomUse)
SqFt - Square Footage of Room

tblRoomUse
RoomUse - Primary Key
Assignable - Yes/No

tblFloors
FloorId - Primary Key
BldgID - Foreign key (tblBuildings)
SqFt - Square Footage of Floor


I need a query that displays 'For Each Building' the total square footage of all the rooms where Assignable = Yes; and the total Square Footage of all the Floors for that particualr Building.

BuildingID - Assignable Sq Ft - Total Floor Sq Ft.



Can I accomplish this with a single SELECT statement? Or use nested queries? Or another method?

I can create 2 queries each Grouped by tblBuildins.bldgid to get the results I want.

Assignable Sq Ft:
SELECT tblBuildings.BldgID, sum(qryRoomMaster.SqFt) as ASF
FROM (tblBuildings INNER JOIN qryRoomMaster ON tblBuildings.BldgID = qryRoomMaster.Bldgid) INNER JOIN tblRoomUse ON qryRoomMaster.RoomUse = tblRoomUse.RoomUse
WHERE (((tblRoomUse.Assignable)=-1))
Group by tblBuildings.BldgID;


Floor Sq FT:
SELECT tblBuildings.BldgID, sum(tblFloors.SqFt) as FloorSqFt
FROM tblBuildings INNER JOIN tblFloors ON tblBuildings.BldgID = tblFloors.BldgID
Group by tblBuildings.BldgID;

...but I was wondering if I could do it one step.

Thanks for your time,

SDF

 

[gol4]

Untested but this may be close

SELECT tblBuildings.BldgID, sum(qryRoomMaster.SqFt) as ASF,
(select sum(x.SqFt) as FlrSqFt FROM tblBuildings as x where
x.bldid = tblBuildings.BldgID
group by x.bldid)
FROM (tblBuildings INNER JOIN qryRoomMaster ON tblBuildings.BldgID = qryRoomMaster.Bldgid) INNER JOIN tblRoomUse ON qryRoomMaster.RoomUse = tblRoomUse.RoomUse
WHERE (((tblRoomUse.Assignable)=-1))
Group by tblBuildings.BldgID;


may not need the group by in the sub but I put it there to be sure

Let me know if it works.

 

[gol4]

Whoops,
SELECT tblBuildings.BldgID, sum(qryRoomMaster.SqFt) as ASF,
(select sum(x.SqFt) as FlrSqFt FROM tblBuildings as x where
x.bldid = tblBuildings.BldgID
group by x.bldid)as FloorSqFt
FROM (tblBuildings INNER JOIN qryRoomMaster ON tblBuildings.BldgID = qryRoomMaster.Bldgid) INNER JOIN tblRoomUse ON qryRoomMaster.RoomUse = tblRoomUse.RoomUse
WHERE (((tblRoomUse.Assignable)=-1))
Group by tblBuildings.BldgID;

Noticed I forgot an alias so made the correction probaly one of many you will need to make

 

[gol4]

Whoops again, pasted from your post and noticed I got the wrong table.

SELECT tblBuildings.BldgID, sum(qryRoomMaster.SqFt) as ASF,
(select sum(tblfloors.SqFt) as FlrSqFt FROM tblfloors where
tblfloors.bldid = tblBuildings.BldgID
group by tblfloors.bldid)as FloorSqFt
FROM (tblBuildings INNER JOIN qryRoomMaster ON tblBuildings.BldgID = qryRoomMaster.Bldgid) INNER JOIN tblRoomUse ON qryRoomMaster.RoomUse = tblRoomUse.RoomUse
WHERE (((tblRoomUse.Assignable)=-1))
Group by tblBuildings.BldgID;

OK....this time I reviewed before hitting submit

 

[LochDhu]

Awesome! That works fine. Now, is there any way to add another field containing the total Room Sq FT for assignable = 0?

BuildingID - Assignable Sq Ft - Total Floor Sq Ft. - Non assigned SQ FT

Thanks again gol4!