GroupBy Query Problem

[william]

Hello

Having trouble trying to get a query to work.

What I'm trying to do is create a query based on an Inspections table
that will return the most recent inspection completed for each room.
One room can have many inspections done on different dates. So I built
query, clicked Totals, and have the following three fields: Room
(GroupBy), InspectionDate (Max) and Room (Count). This gives me what
I'm looking for. For example Room 9A, 12/26/05, 6 (6 total inspections
for Room 9A). The problem is that I need the InspectionID of the most
recent inspection to appear as well. I don't know how to include that
in a query using Group By. Is there a way to do this or am I going
about this incorrectly to begin with.

Any help is greatly appreciated.

William

 

[TC]

I assume you have something like:

tblInspection
ID < primary key
Room
iDate
etc.

If so, here's one way to do what you want:

(untested)

SELECT *
FROM tblInspection AS i1
WHERE i1.idate =
( SELECT max (i2.idate)
FROM tblInspection AS i2
WHERE i2.room = i1.room )

That would assume that you can't exmine the same room several times on
the same day. (If you /did/, you'd get all of those inspecations.)

HTH,
TC

 

[william]

Thank you TC. That worked perfectly!

If it's not too much trouble, could you please explain how your code
works? I understand SELECT FROM, WHERE, and MAX, but not sure what the
i1 and i2 represent. Are they table variables? If so, what do the
naming conventions mean?

Thanks again,

William

 

[TC]

Thank you TC. That worked perfectly!

No probs, glad it helped

If it's not too much trouble, could you please explain how your code
works? I understand SELECT FROM, WHERE, and MAX, but not sure what the
i1 and i2 represent.

You've proably noticed how in queries, Access prepends the name of the
table, to the name of each field from that table:

SELECT [MyTable].f1, [MyTable].f2, ... FROM MyTable

This gets really messy when you are joining several tables, and the
table names are long. The alternative is to define an "alias name" for
one or more tables. You do that using the AS clause. Then you can
prepend the alias name(s), instead of the full table name(s):

SELECT t.F1, t.F2, t.F3 FROM MyTable AS t

This makes things much cleaner & neater. But it is purely a symbolic
convenience. You could remove all the AS statements, and replace the
alias names with the relevant full tables names, and the SQL would work
precisely the same.

As for the statement I gave you:

SELECT *
FROM tblInspection AS i1
WHERE i1.idate =
( SELECT max (i2.idate)
FROM tblInspection AS i2
WHERE i2.room = i1.room )

That just says: "get me every row where the date in that row is the
maximum date for the room in that row".

This is an example of a single statement referring to the same table
/twice/. So, when the statement referes to a field, say idate, it's
critical for the statement to know /which instance of the table/ that
field is being taken from. I used the alias names for that purpose (ie.
to mak that clear to Access).

HTH,
TC