Group by for Latest Equipment Repair

[Guest]

Could someone please help me with the following? I need to return the last
installed part(s) in a piece of equipment. Here are the tables;

Equipment – EquipUID
Repair Order – RepairOrderUID, EquipUID, RepairOrderDate, RepairOrderType
Parts Used – PartLineUID, RepairOrderUID, PartUID
Master Parts – PartUID, PartType

Each piece of equipment can have 1-n Repair Orders, each Repair Order can
have 1-n parts used, all parts used are in the Master Parts Parent table.
The part type installed must be “1†and the Repair Order must be of type “Iâ€.
It does not matter the exact date of the installation just that it is the
latest one. A piece of equipment may have more than one part type of “1â€
installed in the latest repair order.

I’ve tried several different Group By queries and end up getting stuck at
the same spot, I end up with the Equipment UID and all parts installed not
just the latest one or I get all of the Repair Orders and all of the parts
installed not just the latest one.

Thanks in advance,

Jim

 

[[MVP] S.Clark]

Do it in two queries. The first returns the max InstallID per Repair order.

Select Max(InstallID) as LastPart, RepairOrderID from RepairOrder Group By
RepairOrderID

Next, link this saved query to the PartsUsed table by RepairOrderID and
LastPart.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Guest]

Steve,

I'm not sure where you found "InstallID per Repair order" but if this is the
RepairOrderUID then the max of the RepairOrderUID is not necessarily the
latest repair order for the equipment, i.e. the users do not enter the repair
orders as they occur they are entered in batch in a random order. I think to
find the latest parts installed the RepairOrderDate must be used. When I
group on RepairOrderUID and Max of RepairOrderDate all records are returned
because the RepairOrderUID makes the record unique.

Thanks again,

Jim