Finding One Example From Several Categories

  • Thread starter Thread starter T. Wilson
  • Start date Start date
T

T. Wilson

Here is my table...

EquipmentID
MaintenanceProcedure
DatePerformed
Mileage

Sample Data...

01001 Oil Change 01/01/2004 73142
01001 Lube 01/01/2004 73142
01002 Oil Change 01/03/2004 32114
01001 Tire Rotation 01/14/2004 74567
01001 Oil Change 02/01/2004 76092
01001 Lube 02/01/2004 76092

Assuming that I pass a value of 01001 to the query so that it selects only
records relating to that piece of equipment, I am left with...

01001 Oil Change 01/01/2004 73142
01001 Lube 01/01/2004 73142
01001 Tire Rotation 01/14/2004 74567
01001 Oil Change 02/01/2004 76092
01001 Lube 02/01/2004 76092

What I would like my query to show is the following...

01001 Lube 02/01/2004 76092
01001 Oil Change 02/01/2004 76092
01001 Tire Rotation 01/14/2004 74567

that is, only records relating to EquipmentID 01001, sorted alphabetically
by MaintenanceProcedure, with only the most recent instance of that
procedure displayed.

The article referenced at www.mvps.org/access/queries/qry0020.htm seems to
indicate that this type of thing is trivial, I don't know, I must have been
looking at the problem too long if that is the case. Any help will be
appreciated.

Thanks,

Todd Wilson
 
OK,

After taking a nap, I came up with this solution that seems to work...

I created a Totals Query with the following fields and conditions...
MaintenanceProcedure - GroupBy
DatePerformed - Max
Mileage - Max
EquipmentID - Max
EquipmentID - Where [Forms]![Form1]![EquipmentID] (This field is not
displayed - Show set to No)

Max -ing the Mileage and EquipmentIDs does not seem to cause a problem, as
there is only one applicable entry for each. Please suggest
improvements/problems with my solution - I'm always trying to learn.

Thanks,

Todd
 
Your solution is OK IF the later DatePerformed alwasy has the highest
mileage (which makes sense in this case).

The other way to do this, to ensure that you are getting the mileage that
matches the date is:

1. Create a totals query with
MaintenanceProcedure - GroupBy
DatePerformed - Max

2. Create a query which joins the query in step 1 with your table on the
MaintenanceProcedure and DatePerformed fields.

Hope that helps

Paul

T. Wilson said:
OK,

After taking a nap, I came up with this solution that seems to work...

I created a Totals Query with the following fields and conditions...
MaintenanceProcedure - GroupBy
DatePerformed - Max
Mileage - Max
EquipmentID - Max
EquipmentID - Where [Forms]![Form1]![EquipmentID] (This field is not
displayed - Show set to No)

Max -ing the Mileage and EquipmentIDs does not seem to cause a problem, as
there is only one applicable entry for each. Please suggest
improvements/problems with my solution - I'm always trying to learn.

Thanks,

Todd

T. Wilson said:
Here is my table...

EquipmentID
MaintenanceProcedure
DatePerformed
Mileage

Sample Data...

01001 Oil Change 01/01/2004 73142
01001 Lube 01/01/2004 73142
01002 Oil Change 01/03/2004 32114
01001 Tire Rotation 01/14/2004 74567
01001 Oil Change 02/01/2004 76092
01001 Lube 02/01/2004 76092

Assuming that I pass a value of 01001 to the query so that it selects only
records relating to that piece of equipment, I am left with...

01001 Oil Change 01/01/2004 73142
01001 Lube 01/01/2004 73142
01001 Tire Rotation 01/14/2004 74567
01001 Oil Change 02/01/2004 76092
01001 Lube 02/01/2004 76092

What I would like my query to show is the following...

01001 Lube 02/01/2004 76092
01001 Oil Change 02/01/2004 76092
01001 Tire Rotation 01/14/2004 74567

that is, only records relating to EquipmentID 01001, sorted alphabetically
by MaintenanceProcedure, with only the most recent instance of that
procedure displayed.

The article referenced at www.mvps.org/access/queries/qry0020.htm seems to
indicate that this type of thing is trivial, I don't know, I must have been
looking at the problem too long if that is the case. Any help will be
appreciated.

Thanks,

Todd Wilson
Click to expand...
Click to expand...
 
Back
Top