Group by for Latest Equipment Repair

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Back
Top