Previous Record - If Type Not "*" Get Previous?

[misscrf]

I have a car database for tracking costs associated with many cars. My two main tables are tblCar and tblCarCost. I need to update the tblCarCost table for every current record, with the previous max mileage for that car. These are the tables:

tblCar -
PKCarID
FKCarNum
FKCarStatus
FKCarMake
FKCarModel
intCarYear
txtCarColor
intVinNum
txtLicense
memCarcomments

tblCarCost-
PKCarCostID
FKCar
FKCarCostType
CurCostAmount
intGallons
dtCostDate
intMileagePrev
intMileage
FKInstructor
memCostNotes

----------------
I made the following query to get the last max mileage for each car and it shows on the previous line.

SELECT tblCarCost.FKCar, tblCarCost.intMileage, qrymaxmile.MaxOfintMileage, tblCarCostType.txtCarCostType, qrymaxmile.PKCarCostID, tblCarCost.PKCarCostID
FROM tblCarCostType RIGHT JOIN (tblCarCost LEFT JOIN qrymaxmile ON tblCarCost.FKCar = qrymaxmile.FKCar) ON tblCarCostType.PKCarCostTypeID = tblCarCost.FKCostType
WHERE (((qrymaxmile.PKCarCostID)=[tblCarCost]![PKCarCostID]-1));

My issue is that mileage only gets entered in when there is a cost type (FKCarCostType) of gas. If not, the mileage is empty. So I need to be able to see if that is the case and if so, get the previous max mileage that was for gas.

Can anyone please help me to fix that query, so it will always fill in a previous mileage?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

What are tblCarCostType and qrymaxmile ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[misscrf]

tblCarCostType is the type of cost, ie, if it is gas or body work or an oil change, etc. It is a lookup table.

qrymaxmile is a query that pulls the max mileage by car.



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

So, I rephrase:
1) What are the columns of tblCarCostType and how do you know it's gas
2) What is the SQL code of qrymaxmile

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[misscrf]

Sorry. tblCarCostType has 2 columns PKCarCostTypeID - int, Identity primary ID, and txtCarCostType - the value.

I will have to get the sql of the qrymaxmile tonight. The app is at home.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Here is the sql of qrymaxmile:

SELECT tblCarCost.PKCarCostID, tblCarCost.FKCar, Max(tblCarCost.intMileage) AS MaxOfintMileage
FROM tblCarCost
GROUP BY tblCarCost.PKCarCostID, tblCarCost.FKCar;

Sorry it took so long!

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot