Sorting information in a query

[Dan]

I have in a table, a list of experiments and beside that,
the date that maintenence was I have, in a table, a list of
experiments. The next column is the date maintenance was
performed on the experiment. This means that in the first
column any one experiment is listed several times (ie. More
than one record per experiment). I need my query to find
the last time maintenance was performed on each experiment.

I am able to get the query to list the experiments but I am
only able to get it to find the last time ANY maintenance
was done (ie. The most recent date in the table, not for
each experiment)

This sounds simple to do but I have not been able to find
it. I am not incredibly database savvy yet so any help
someone could throw my way would be greatly appreciated.

Thanks,
Dan

 

[Dan]

I have, in a table, a list of experiments. The next column
is the date maintenance was performed on the experiment.
This means that in the first column any one experiment is
listed several times (ie. More than one record per
experiment). I need my query to find the last time
maintenance was performed on each experiment.

I am able to get the query to list the experiments but I am
only able to get it to find the last time ANY maintenance
was done (ie. The most recent date in the table, not for
each experiment)

This sounds simple to do but I have not been able to find
it. I am not incredibly database savvy yet so any help
someone could throw my way would be greatly appreciated.

Thanks,
Dan

 

[tina]

to do it in a simple query:
create a new query that uses the "list of experiments"
table.
add the experiment field and date field to the design grid.
on the menu bar, click View, Totals. now it's a Totals
query.
in the "Total:" row in the design grid, set the experiment
field to Group By (the default value).
set the date field to Max.
that should give you the most recent date for each
experiment.

fyi, suggest you review your table design. a normalized
design would have a (parent) table containing the list of
experiments along with any other unique information
pertaing to each experiment; and a (child) table
containing each maintenance date for each experiment,
along with any other unique information pertaining to each
date's maintenance.

hth

 

[Dan]

Thanks a lot Tina,
I actually had my tables set up just as you said so your
directions worked perfectly. ...just didnt' realize I
needed the parent in there.

Thanks again.
dan

 

[Matt]

Hey Dan,

Try a SELECT DISTINCT statement in your query, and sort the table by
date in a DESCENDING order - the DISTINCT bit will only find one row
for each experiment, and sorting by reverse date order will make sure
it's the latest row for each.

eg.

SELECT DISTINCT [ExperimentId], [Date] FROM

ORDER BY DATE DESC