How to build a query for the following output?

[A.J.M. van Rijthoven]

I Have a table with the following fields:
InlegID autonumber, index
InlegNetID number, related to other table for names
Inlegnetnummer number, related to other table for numbers
Inlegdoor number, related to other table with names
Inlegopmerkingen memo
InlegDatum date
Inlegtijd time

It's part of a program used in te operation theatre to see who filled
the trays with instruments in the sterilisation department.
Each tray has a name and number. Sometimes we have 16 trays with the
same instruments so we have numbered all the trays.

I want an actual view/status of all the trays (InlegNetID).

So each tray (inlegNetID) has to be displayed once,
each traynumber has to be displayed once
I'm only interested in the latest date (InlegDatum) and time
(InlegTijd) of this tray with this specific number. So if there were 4
records from 19 nov. I want only the record displayed with the latest
time.
The memofield (Inlegopmerkingen) contains valuable information about
defect or missing instruments on the tray with specific number.)

What kind of query should I use and what do I have to do to get the
output that I want?

 

[Sharkbyte]

From your description, I would suggest you begin with creating a SELECT
query, and use the Totals button (The funny looking E). This will open up
grouping options such as Max (for date and time) as well as Group By (for
tray, etc.).

One note, Group By does not work for a Memo field type. So you will either
have to exclude this field, or choose something like First or Last.

Good luck.

Sharkbyte

 

[Luke Dalessandro]

AJM,

You're going to have a bit of a problem because you're storing the date
and the time separately. You need to combine them into one "datetime" so
that you can find the most recent event for a tray.

I made a demo with a table

Table: TABLE1 ( ID autonumber PRIMARY KEY, trayid long, name
varchar(50), [date] date, [time] time )

So in your query, you only have two fields, the first is the trayid,
which you "group by" and the second is the calculated combined date and
time, which you set as "max". In my little demo I made, the SQL looks like:

SELECT Table1.trayid, Max([date]+[time]) AS maxdate
FROM Table1
GROUP BY Table1.trayid;

This gives you one record per tray, with the max (most recent) date
something happened to it.

Then the problem is that you probably want to know more about the record
associated with that maxdate. To do this you build a second query that
"JOINS" the results from the first query back into the table. In my
little example:

SELECT Table1.ID
FROM Query1 INNER JOIN Table1 ON (DateValue(Query1.maxdate) =
Table1.date) AND (Query1.trayid = Table1.trayid) AND
(TimeValue(Query1.maxdate) = Table1.time);

Will select the record ID of the record with the most recent date for
the tray. The only thing that is confusing is that you need to split the
calculated "maxdate" into date and time values to join correctly with
the table.

This can be a slightly confusing and roundabout process, but it's the
only way in Access to do it (you can also "alias" that first query but
it doesn't make a difference).

Good Luck,
Luke