Last Repair Record

[Guest]

This seems simple enough but I just can't figure it out.

I have three tables, Repairs, Parts, and Items. The Repairs table contains
a Repair ID (PK) for each repair and an Equipment ID (FK), and Date of
repair. Many repairs can be done to the same piece of equipment. The Parts
table contains the parts used in the repair, Parts ID (PK), Item ID (FK),
Repair ID (FK). The Items table contains Item ID (PK), Item Type.

What I want is the Equipment ID, Repair ID, and Item ID for the latest Date
of Repair for each Equipment ID. I have a query that gives me the Equipment
ID, Repair ID, Date of Repair, and Item ID for a specific Item Type but, I
can’t get the group by/max/last/first to give me just the latest one per
piece of equipment…

Thank you in advance,

Jim

 

[Jeff Boyce]

Jim

If I'm following, your Repairs table looks something like:

trelRepairs
RepairID
EquipmentID
DateOfRepair

What happens when you run a Totals query against this table, grouping by
EquipmentID and using Max for DateOfRepair?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Multiple queries will be required to get the results you want. You have
spaces in your field names and that is a bad idea.

Save the following as qLastRepair
SELECT [Equipment Id], Max([Date of Repair]) as LastRepaired
FROM Repairs
GROUP BY [Equipment id]

Now use that in a query along with the Repair and Equipment tables.
SELECT Repairs.[Repair Id]
, Repairs.[Equipment ID]
, Repairs.[Date of Repair]
, Equipment.[Item ID]
FROM (Repairs INNER JOIN Equipment
On Repairs.[Equipment ID] = Equipment.[Equipment ID])
INNER JOIN qLastRepair
On Repairs.[Equipment id] = qLastRepair.[Equipment ID]
AND Repairs.[Date of Repair] = qLastRepair.LastRepaired


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Jeff,

I get the latest Date of Repair for each Equipment ID in the table. Which
is a good start but the specific Item Type I’m looking for may not be used in
the "last" repair. I'm sorry if this is getting messy. Let me try to
clearly state what I’m trying to do, given any date in time, I want to know
the manufacturer of item type = x for each piece of equipment.

Thanks again,

Jim

 

[Guest]

Jeff & John,

Thanks for your help. It got me going in the right direction. It's
working...

Thanks again...

Jim

Multiple queries will be required to get the results you want. You have
spaces in your field names and that is a bad idea.

Save the following as qLastRepair
SELECT [Equipment Id], Max([Date of Repair]) as LastRepaired
FROM Repairs
GROUP BY [Equipment id]

Now use that in a query along with the Repair and Equipment tables.
SELECT Repairs.[Repair Id]
, Repairs.[Equipment ID]
, Repairs.[Date of Repair]
, Equipment.[Item ID]
FROM (Repairs INNER JOIN Equipment
On Repairs.[Equipment ID] = Equipment.[Equipment ID])
INNER JOIN qLastRepair
On Repairs.[Equipment id] = qLastRepair.[Equipment ID]
AND Repairs.[Date of Repair] = qLastRepair.LastRepaired


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

This seems simple enough but I just can't figure it out.

I have three tables, Repairs, Parts, and Items. The Repairs table
contains
a Repair ID (PK) for each repair and an Equipment ID (FK), and Date of
repair. Many repairs can be done to the same piece of equipment. The
Parts
table contains the parts used in the repair, Parts ID (PK), Item ID (FK),
Repair ID (FK). The Items table contains Item ID (PK), Item Type.

What I want is the Equipment ID, Repair ID, and Item ID for the latest
Date
of Repair for each Equipment ID. I have a query that gives me the
Equipment
ID, Repair ID, Date of Repair, and Item ID for a specific Item Type but, I
can't get the group by/max/last/first to give me just the latest one per
piece of equipment.

Thank you in advance,

Jim