Complicated Nested Queries/References

[Epidural]

Hi,



I've been struggling for a while now on a series of similar problems. I am trying to create a database which crosschecks specifications against building codes. My problem is that it's not feasable to put all of the information about a Building into the Room table, though many codes require information about the Building in order to classify the room.

So my question is this: How would I count (or list/get details from) a room in the Rooms table, for use in an SQL statement about the Buildings table. For example (a simplification):
If there are up to 5 rooms in the building, do at least 2 rooms contain a fire escape?

My Rooms table contains information about if the room has a fire escape, as well as references its parent Building ID. How would I query the rooms to count the Rooms within all buildings, and subsequently query all Buildings which have less than 2 rooms with fire escapes?

I need it to return a list of the buildings which do not conform to this code.

Now this is literally my second day of programming/working with Access, but I've yet to find an answer dispite hours of searching. If I can get the simplified code for this, I think I can adapt it for the 100+ building codes/references I'll need to implement. It would be perfect to find an SQL-only way of doing this, without going into modules etc...

Thanks in advance

[Robeen]

So my question is this: How would I count (or list/get details from) a room in the Rooms table, for use in an SQL statement about the Buildings table. For example (a simplification): If there are up to 5 rooms in the building, do at least 2 rooms contain a fire escape?

If I understand you correctly, you could get this data from your Rooms table.

1. Create a query on the Rooms table.
2. Select the BuildingID & HasFireEscape fields.
3. Group on the BuildingID field and Count on HasFireEscape field:
i. Click on the large yellow 'Totals' button on the top of your screen
ii. Both fields will now have 'Group By' in the 'Total' row.
iii. Change the 'Group By' under HasFireEscape to 'Count'.
4. In the Criteria row under HasFireEscape - put < 2.

This should show you all BuildingIDs that have less than two rooms with Fire Escapes.

I hope this helps.

[Epidural]

This should show you all BuildingIDs that have less than two rooms with Fire Escapes.

I hope this helps.

It certainly helps.. but I don't truly understand what's going on there. I've been programming extensively with MySQL, but these sorts of statements were always just worked around using some clever (or lazy) PHP. Now that I'm in Access it's a little different.

If I understand this right (based on the SQL code), the Group By (as I've never used it before) is taking the query and counting the fire escapes for each building ID, and is then combining them under 1 result for each building ID. How would I now use this information to display the details from the Building table? For instance, it displays the Room's BuildingID along with the total (insufficient) number of Fire Escapes, but how do I now take that list of IDs and query the Buildings table?

For instance if building IDs 3, 4, and 5 are displayed with 0, 1, and 1 fire escapes each, how do I display the Building Name (contained in the building table) for those 3 Building IDs?

[June7]

Build a report using Grouping & Sorting with aggregate calcs in header and footer sections. This will allow display of detail records and summation data.

[Epidural]

I'm looking for a way to do it using queries alone. I'm not familiar with Reports and their formatting, nor do I need summation data. All that is required is a list of Names for those buildings when I click on a building code query. From there, if needed later on, I can create a report using these queries or a series of these queries (for instance, which buildings fail only this code or which do not fail any code, etc).

EDIT: The end result should not show which rooms are not equipped with fire escapes, etc. This example in particular would benefit from that, but others are not the same. I need a general way to use that Group By data (particularily the resulting distinct Building IDs) to draw up the details of those buildings.

Would a nested query work? Somthing along the lines of:
SELECT * FROM Buildings
WHERE Buildings.ID IN( SELECT ParentBuilding, Count(Rooms.FireEscape) GROUP BY (ParentBuilding) HAVING (Count(Rooms.FireEscape)<2) )
AND IN ( SELECT ParentBuilding, Count(Rooms.ID) GROUP BY (ParentBuilding) HAVING (Count(Rooms.ID)>4) )

Hopefully this would find everything in the buildings table, displaying those buildings whom have more than 4 rooms, but less than 2 fire escapes. However, I'm finding that the given statements do not seem to work (they are not copy-pasted but sytax similar).

[June7]

Don't know if two IN clauses will even work but think Buildings.ID would have to be stated with both IN clauses for any possibility of success. Want to provide sample data for testing? Could be Excel or Access file. Follow instructions at bottom of my post.

[Epidural]

I think I got this going: I used a combination of what Robeen said, Inner Joins (which would have been useful to know about initially... teaching yourself does have its downfalls) and the IN() statement. The final product would be as follows for anybody who wants to know:

SELECT * FROM Buildings
WHERE Buildings.ID IN(
SELECT Rooms.Buildings FROM Buildings
INNER JOIN Rooms ON Buildings.ID = Rooms.Building
GROUP BY Rooms.Building
HAVING (COUNT(FireEscape) < 2)
)

Basically it creates a list of IDs based on my criteria, then crosschecks the Buildings to display the information I want based on that list.

I'd like to note that this is in no way the most efficient or correct way to do it, but for me it was the most flexible (as there are about 100 more like it to code).

Thanks to all that posted.