Last Date... If Minor...If Major...Then ARRGGG! 1

[uncleG]

I would like to find the last entry (Most Recent Date) in tblAssetDetail where luwServiceType = "Minor" (a combobox with 5 different choices) then add the # of Days in tblasset.aServe2 to it then store the resulting new date in tblAsset.NexSerDueMin for use in other forms and reports. I had planned to trigger this event with the
AfterUpdate Event of tblAsset.luwServiceType on frmAsset.
Secondly I would like to do the same procedure above for a second choice in the same combobox, luwServiceType = "Major" then add the # of days in tblasset.aserve to it then update it to tblasset.NextSerDueMaj. The other 3 Combobox entry's will require no action.

After many,many tries and several days I still can not see what I am doing wrong. I can not seem to single out the Most recent Date and as you can see below and don't
have a clue as to how to get the results into my Table where I need it.

tblAsset pkey is aID ; 1 to many with tblAssetDetail pkey ServiceRecordID link field between tables is aID

QryAsset2
SELECT tblAsset.aID, tblAsset.aName, Max(tblAssetDetail.ServiceDate) AS MaxOfServiceDate, tblAssetDetail.luwServiceType, [tblAssetDetail.ServiceDate]+[tblAsset.aServe2] AS NextSerDueMin
FROM tblAsset INNER JOIN tblAssetDetail ON tblAsset.aID = tblAssetDetail.aID
GROUP BY tblAsset.aID, tblAsset.aName, tblAssetDetail.luwServiceType, [tblAssetDetail.ServiceDate]+[tblAsset.aServe2]
HAVING (((tblAssetDetail.luwServiceType)="minor"))
ORDER BY Max(tblAssetDetail.ServiceDate) DESC;

Result of QryAsset2
ID Name MaxOfServiceDateluwServiceType NextSerDueMin
2 Okuma LC-40 9/9/2005 Minor 9/11/2005
1 BackRoom G2 7/13/2005 Minor 8/13/2005
1 BackRoom G2 6/1/2005 Minor 7/2/2005

Thanks, UncleG

 

[PHV]

And this ?
SELECT tblAsset.aID, tblAsset.aName, Max(tblAssetDetail.ServiceDate) AS MaxOfServiceDate, tblAssetDetail.luwServiceType, Max([tblAssetDetail.ServiceDate]+[tblAsset.aServe2]) AS NextSerDueMin
FROM tblAsset INNER JOIN tblAssetDetail ON tblAsset.aID = tblAssetDetail.aID
WHERE tblAssetDetail.luwServiceType = 'minor'
GROUP BY tblAsset.aID, tblAsset.aName, tblAssetDetail.luwServiceType
ORDER BY Max(tblAssetDetail.ServiceDate) DESC;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[uncleG]

WOW, Thanks PHV that gives me my Dates just fine, here is a star. Now that the query provides the desired results how do I get them into the underlying tables?
Thanks,
UncleG