Pull only current record

[Maria]

I am using Access 2000

I created a Db with 2 Tables. One for equipment
information and one for calibrations that have been done
on the equipment. (One to Many based on serial # field) I
created a form with the equipment information at the top
including a subform in which to enter the calibrations.
But I can't seem to create a report that will only show me
the lastest calibration for each piece of equipment.

Does anyone have any ideas??????????

Aurora

 

[Steve Schapel]

Aurora,

I assume your Calibrations table has a fied for CalibrationDate or some
such? Ok, there would be a few approaches to this. Here's one which
might most readily make sense, which involves doing it in 2 steps.
Adapt the below according to your actual field names etc.
1. Identify the most recent Calibration for each Equipment, via a
Totals Query, as follows. Make a query based on your Calibration table,
put the SerialNumber and CalibrationDate fields into the QBE grid,
select Totals from the View menu, leave Group By in the Totals row of
the SerialNumber field, and put Max in the CalibrationDate field.
(Note: It is not a good idea to use a # as part of the name of a field,
so I have changed it in my example)
2. Make a new query, add both your tables, and also this first query.
Join the tables on the SerialNumber field, and join the Calibration
table and the first query on both the SerialNumber fields from both, and
also on the CalibrationDate/MaxOfCalibrationDate fields.
3. Put whatever fields you want in your report in the grid. Save the
query, and base your report on it. Does this give what you want?

 

[Tim Ferguson]

But I can't seem to create a report that will only show me
the lastest calibration for each piece of equipment.

Use MAX(CalibrationDate), or just sort on the date field.

Tim F

 

[fofa]

If you sort your calibrations by descending date, the newest will be at
the top. You can even use SELECT TOP 1 COL1, COl2 from Mytbl Where
Something and it will return only the first row (TOP n returns the
first n rows of the query).

 

[Steve Schapel]

Good idea, Fofa, in the case of an overall newest... but I don't think
this will be of help if you need the newest *for each equipment*.