Using Max([ShipDate]) and Group By [EquipId] for latest Equip location

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

Guest

I am pretty inexperienced, & until now have been using Last() in a group by query to return the most recent equipment transfer (and therefore current location) for each piece of equipment in my database. When I began noticing unexpected query results and did a little research, it became apparent that Last() is not the way to go, so I've been experimenting with Max([ShipDate]) but I'm having a problem with this now. I have two tables involved in tracking transfers. tblEquipTransfer, the "parent" table, has a primary key [TransId] and main fields [ShipDate] & [JobNo]. tblEquipTrans, the "child" table, has the foreign keys [EquipId] (looked up from Equipment table) and [TransId] which is linked to the main record in tblEquipTransfer. I joined the two tables and have no problem obtaining the most recent [ShipDate] for each [EquipId], but I need the primary key [TransId] in my results in order to pull in all the other applicable fields. I can't use Max for [TransId], because transfers are occasionally entered out of date sequence, so Max([ShipDate]) and Max([TransId]) can each return a value from a different row. I tried joining [EquipId] and [ShipDate] to get [TransId], but we can have equipment transferred more than once on a given date. (At least when I was using Last() I got a distinct row, even though it wasn't always for the most recent date or TransId.) Any suggestions would be welcome!

THIS IS THE QUERY I WAS USING:
SELECT Last(EquipTransfer.ShipDate) AS LastOfShipDate, Last(EquipTrans.TransId) AS LastOfTransId, Last(EquipTransfer.JobNo) AS LastOfJobNo, Equipment.EquipId, Equipment.EquipType, Equipment.SerialNo, Last(EquipTrans.ToMaster) AS LastOfToMaster, Last(EquipTrans.Comments) AS LastOfComments, EquipTrans.SlctEqpId, Last(EqpTrnsVndr.VendorId) AS LastOfVendorId, Last(EquipTrans.RMA) AS LastOfRMA, Last(EqpTrnsVndr.Mas)
AS LastOfMas, Last(qlkpJobNo.Manager) AS LastOfManager, Last(qlkpJobNo.MajorLoc) AS LastOfMajorLoc
FROM ((EquipTransfer INNER JOIN qlkpJobNo ON EquipTransfer.JobNo = qlkpJobNo.JobNo) LEFT JOIN EqpTrnsVndr ON EquipTransfer.TransId = EqpTrnsVndr.TransId) INNER JOIN (Equipment INNER JOIN EquipTrans ON Equipment.EquipId = EquipTrans.SlctEqpId) ON EquipTransfer.TransId = EquipTrans.TransId
GROUP BY Equipment.EquipId, Equipment.EquipType, Equipment.SerialNo, EquipTrans.SlctEqpId
ORDER BY Last(EquipTransfer.ShipDate) DESC;

THIS IS AS FAR AS I’VE BEEN ABLE TO GO USING MAX.
SELECT EquipTrans.SlctEqpId, Max(EquipTransfer.ShipDate) AS MaxOfShipDate
FROM EquipTransfer LEFT JOIN EquipTrans ON EquipTransfer.TransId = EquipTrans.TransId
GROUP BY EquipTrans.SlctEqpId;
 
Try something like the following untested SQL.

SELECT ET.ShipDate, ET.TransId, ET.JobNo,
E.EquipId, E.EquipType, E.SerialNo, ET.ToMaster,
ET.Comments, ET.SlctEqpId, ET.VendorId,
ET.RMA, EqpTrnsVndr.Mas
qlkpJobNo.Manager, qlkpJobNo.MajorLoc
FROM ((EquipTransfer AS ET INNER JOIN qlkpJobNo ON ET.JobNo = qlkpJobNo.JobNo)
LEFT JOIN EqpTrnsVndr ON ET.TransId = EqpTrnsVndr.TransId)
INNER JOIN (Equipment AS E
INNER JOIN ET ON E.EquipId = ET.SlctEqpId)
ON EquipTransfer.TransId = EquipTrans.TransId
WHERE ET.SHIPDATE =
(SELECT Max(EquipTransfer.ShipDate)
FROM EquipTransfer
WHERE EquipTransfer.TransId = ET.TransId)
ORDER BY ET.ShipDate DESC;



Michelle said:
I am pretty inexperienced, & until now have been using Last() in a group by query to return the most recent equipment transfer (and therefore current location) for each piece of equipment in my database. When I began noticing unexpected query results and did a little research, it became apparent that Last() is not the way to go, so I've been experimenting with Max([ShipDate]) but I'm having a problem with this now. I have two tables involved in tracking transfers. tblEquipTransfer, the "parent" table, has a primary key [TransId] and main fields [ShipDate] & [JobNo]. tblEquipTrans, the "child" table, has the foreign keys [EquipId] (looked up from Equipment table) and [TransId] which is linked to the main record in tblEquipTransfer. I joined the two tables and have no problem obtaining the most recent [ShipDate] for each [EquipId], but I need the primary key [TransId] in my results in order to pull in all the other applicable fields. I can't use Max for [TransId], because
transfers are occasionally entered out of date sequence, so Max([ShipDate]) and Max([TransId]) can each return a value from a different row. I tried joining [EquipId] and [ShipDate] to get [TransId], but we can have equipment transferred more than once on a given date. (At least when I was using Last() I got a distinct row, even though it wasn't always for the most recent date or TransId.) Any suggestions would be welcome!

THIS IS THE QUERY I WAS USING:
SELECT Last(EquipTransfer.ShipDate) AS LastOfShipDate, Last(EquipTrans.TransId) AS LastOfTransId, Last(EquipTransfer.JobNo) AS LastOfJobNo, Equipment.EquipId, Equipment.EquipType, Equipment.SerialNo, Last(EquipTrans.ToMaster) AS LastOfToMaster, Last(EquipTrans.Comments) AS LastOfComments, EquipTrans.SlctEqpId, Last(EqpTrnsVndr.VendorId) AS LastOfVendorId, Last(EquipTrans.RMA) AS LastOfRMA, Last(EqpTrnsVndr.Mas)
AS LastOfMas, Last(qlkpJobNo.Manager) AS LastOfManager, Last(qlkpJobNo.MajorLoc) AS LastOfMajorLoc
FROM ((EquipTransfer INNER JOIN qlkpJobNo ON EquipTransfer.JobNo = qlkpJobNo.JobNo) LEFT JOIN EqpTrnsVndr ON EquipTransfer.TransId = EqpTrnsVndr.TransId) INNER JOIN (Equipment INNER JOIN EquipTrans ON Equipment.EquipId = EquipTrans.SlctEqpId) ON EquipTransfer.TransId = EquipTrans.TransId
GROUP BY Equipment.EquipId, Equipment.EquipType, Equipment.SerialNo, EquipTrans.SlctEqpId
ORDER BY Last(EquipTransfer.ShipDate) DESC;

THIS IS AS FAR AS I’VE BEEN ABLE TO GO USING MAX.
SELECT EquipTrans.SlctEqpId, Max(EquipTransfer.ShipDate) AS MaxOfShipDate
FROM EquipTransfer LEFT JOIN EquipTrans ON EquipTransfer.TransId = EquipTrans.TransId
GROUP BY EquipTrans.SlctEqpId;
Click to expand...
 
I finally had success using the TOP 100 PERCENT* SQL statement trick suggested in another post on the record source for the aggregate query that groups on the EquipId and uses the Last() function to return the last transfer. I did discover that some of the inconsistencies I was getting were because I had set the default value of the ShipDate field to Now(), which also includes a time value, instead of Date(). As a result, if someone entered a transfer and typed in the date manually, the ShipDate wouldn't contain time info, and this caused problems with sort order, which is supposed to be by ShipDate (DESC) then by TransId (DESC). For example, if record 1587 was entered using the default Now() value, while 1588 and 1589 had the ship date entered manually, I would get the following sort results.
ShipDate TransI
11/19/03 158
11/19/03 158
11/19/03 158
As a result, a leak detector received in our shop from Florida on 11/19 in the morning (TransId 1587) and then sent to the Vendor for repair that same afternoon (TransId 1589) would appear to still be located in our shop. Filter by selection is also affected by the time value being included in some records and not others. What a dummy I've been

----- John Spencer (MVP) wrote: ----

Try something like the following untested SQL

SELECT ET.ShipDate, ET.TransId, ET.JobNo
E.EquipId, E.EquipType, E.SerialNo, ET.ToMaster
ET.Comments, ET.SlctEqpId, ET.VendorId
ET.RMA, EqpTrnsVndr.Ma
qlkpJobNo.Manager, qlkpJobNo.MajorLo
FROM ((EquipTransfer AS ET INNER JOIN qlkpJobNo ON ET.JobNo = qlkpJobNo.JobNo)
LEFT JOIN EqpTrnsVndr ON ET.TransId = EqpTrnsVndr.TransId)
INNER JOIN (Equipment AS E
INNER JOIN ET ON E.EquipId = ET.SlctEqpId)
ON EquipTransfer.TransId = EquipTrans.TransI
WHERE ET.SHIPDATE =
(SELECT Max(EquipTransfer.ShipDate
FROM EquipTransfer
WHERE EquipTransfer.TransId = ET.TransId
ORDER BY ET.ShipDate DESC



Michelle Ives wrote
I am pretty inexperienced, & until now have been using Last() in a group by query to return the most recent equipment transfer (and therefore current location) for each piece of equipment in my database. When I began noticing unexpected query results and did a little research, it became apparent that Last() is not the way to go, so I've been experimenting with Max([ShipDate]) but I'm having a problem with this now. I have two tables involved in tracking transfers. tblEquipTransfer, the "parent" table, has a primary key [TransId] and main fields [ShipDate] & [JobNo]. tblEquipTrans, the "child" table, has the foreign keys [EquipId] (looked up from Equipment table) and [TransId] which is linked to the main record in tblEquipTransfer. I joined the two tables and have no problem obtaining the most recent [ShipDate] for each [EquipId], but I need the primary key [TransId] in my results in order to pull in all the other applicable fields. I can't use Max for [TransId], becaus
Click to expand...
transfers are occasionally entered out of date sequence, so Max([ShipDate]) and Max([TransId]) can each return a value from a different row. I tried joining [EquipId] and [ShipDate] to get [TransId], but we can have equipment transferred more than once on a given date. (At least when I was using Last() I got a distinct row, even though it wasn't always for the most recent date or TransId.) Any suggestions would be welcome
THIS IS THE QUERY I WAS USING
Click to expand...
SELECT Last(EquipTransfer.ShipDate) AS LastOfShipDate, Last(EquipTrans.TransId) AS LastOfTransId, Last(EquipTransfer.JobNo) AS LastOfJobNo, Equipment.EquipId, Equipment.EquipType, Equipment.SerialNo, Last(EquipTrans.ToMaster) AS LastOfToMaster, Last(EquipTrans.Comments) AS LastOfComments, EquipTrans.SlctEqpId, Last(EqpTrnsVndr.VendorId) AS LastOfVendorId, Last(EquipTrans.RMA) AS LastOfRMA, Last(EqpTrnsVndr.Mas
AS LastOfMas, Last(qlkpJobNo.Manager) AS LastOfManager, Last(qlkpJobNo.MajorLoc) AS LastOfMajorLo
FROM ((EquipTransfer INNER JOIN qlkpJobNo ON EquipTransfer.JobNo = qlkpJobNo.JobNo) LEFT JOIN EqpTrnsVndr ON EquipTransfer.TransId = EqpTrnsVndr.TransId) INNER JOIN (Equipment INNER JOIN EquipTrans ON Equipment.EquipId = EquipTrans.SlctEqpId) ON EquipTransfer.TransId = EquipTrans.TransId
GROUP BY Equipment.EquipId, Equipment.EquipType, Equipment.SerialNo, EquipTrans.SlctEqpId
ORDER BY Last(EquipTransfer.ShipDate) DESC;
THIS IS AS FAR AS I’VE BEEN ABLE TO GO USING MAX.
Click to expand...
SELECT EquipTrans.SlctEqpId, Max(EquipTransfer.ShipDate) AS MaxOfShipDate
FROM EquipTransfer LEFT JOIN EquipTrans ON EquipTransfer.TransId = EquipTrans.TransId
GROUP BY EquipTrans.SlctEqpId;
Click to expand...
 
If it helps, you can strip the time out using the DateValue function. Create
a computed field that's only the date using that function, and sort on that
computed field, not your existing ShipDate field.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Michelle Ives said:
I finally had success using the TOP 100 PERCENT* SQL statement trick
Click to expand...
suggested in another post on the record source for the aggregate query that
groups on the EquipId and uses the Last() function to return the last
transfer. I did discover that some of the inconsistencies I was getting
were because I had set the default value of the ShipDate field to Now(),
which also includes a time value, instead of Date(). As a result, if
someone entered a transfer and typed in the date manually, the ShipDate
wouldn't contain time info, and this caused problems with sort order, which
is supposed to be by ShipDate (DESC) then by TransId (DESC). For example,
if record 1587 was entered using the default Now() value, while 1588 and
1589 had the ship date entered manually, I would get the following sort
results.
ShipDate TransId
11/19/03 1587
11/19/03 1589
11/19/03 1588
As a result, a leak detector received in our shop from Florida on 11/19 in
Click to expand...
the morning (TransId 1587) and then sent to the Vendor for repair that same
afternoon (TransId 1589) would appear to still be located in our shop.
Filter by selection is also affected by the time value being included in
some records and not others. What a dummy I've been!
----- John Spencer (MVP) wrote: -----

Try something like the following untested SQL.

SELECT ET.ShipDate, ET.TransId, ET.JobNo,
E.EquipId, E.EquipType, E.SerialNo, ET.ToMaster,
ET.Comments, ET.SlctEqpId, ET.VendorId,
ET.RMA, EqpTrnsVndr.Mas
qlkpJobNo.Manager, qlkpJobNo.MajorLoc
FROM ((EquipTransfer AS ET INNER JOIN qlkpJobNo ON ET.JobNo = qlkpJobNo.JobNo)
LEFT JOIN EqpTrnsVndr ON ET.TransId = EqpTrnsVndr.TransId)
INNER JOIN (Equipment AS E
INNER JOIN ET ON E.EquipId = ET.SlctEqpId)
ON EquipTransfer.TransId = EquipTrans.TransId
WHERE ET.SHIPDATE =
(SELECT Max(EquipTransfer.ShipDate)
FROM EquipTransfer
WHERE EquipTransfer.TransId = ET.TransId)
ORDER BY ET.ShipDate DESC;
Click to expand...
group by query to return the most recent equipment transfer (and therefore
current location) for each piece of equipment in my database. When I began
noticing unexpected query results and did a little research, it became
apparent that Last() is not the way to go, so I've been experimenting with
Max([ShipDate]) but I'm having a problem with this now. I have two tables
involved in tracking transfers. tblEquipTransfer, the "parent" table, has a
primary key [TransId] and main fields [ShipDate] & [JobNo]. tblEquipTrans,
the "child" table, has the foreign keys [EquipId] (looked up from Equipment
table) and [TransId] which is linked to the main record in tblEquipTransfer.
I joined the two tables and have no problem obtaining the most recent
[ShipDate] for each [EquipId], but I need the primary key [TransId] in my
results in order to pull in all the other applicable fields. I can't use
Max for [TransId], becauseMax([ShipDate]) and Max([TransId]) can each return a value from a different
row. I tried joining [EquipId] and [ShipDate] to get [TransId], but we can
have equipment transferred more than once on a given date. (At least when I
was using Last() I got a distinct row, even though it wasn't always for the
most recent date or TransId.) Any suggestions would be welcome!Last(EquipTrans.TransId) AS LastOfTransId, Last(EquipTransfer.JobNo) AS
LastOfJobNo, Equipment.EquipId, Equipment.EquipType, Equipment.SerialNo,
Last(EquipTrans.ToMaster) AS LastOfToMaster, Last(EquipTrans.Comments) AS
LastOfComments, EquipTrans.SlctEqpId, Last(EqpTrnsVndr.VendorId) AS
LastOfVendorId, Last(EquipTrans.RMA) AS LastOfRMA, Last(EqpTrnsVndr.Mas)qlkpJobNo.JobNo) LEFT JOIN EqpTrnsVndr ON EquipTransfer.TransId =
EqpTrnsVndr.TransId) INNER JOIN (Equipment INNER JOIN EquipTrans ON
Equipment.EquipId = EquipTrans.SlctEqpId) ON EquipTransfer.TransId =
EquipTrans.TransId
 
Back
Top