Calculated Total with beginning Quantity

[jzwp22]

Looking at your database, you were editing the wrong query. The query qryYTDPointSummaryForAllDrivers just gathers all the points (sum of lost & gained or 0 if none: see the IIF() function). This query uses a left join to the driver table so that you get a record for every driver even if they had no point transactions

SELECT tblDrivers.pkDriverID, tblDrivers.LastName, tblDrivers.FirstName, IIF(IsNull(qryYTDPointSummaryByDriver.YTDPoints),0,qryYTDPointSummaryByDriver.YTDPoints) AS YTDPoints
FROM tblDrivers LEFT JOIN qryYTDPointSummaryByDriver ON tblDrivers.pkDriverID = qryYTDPointSummaryByDriver.fkDriverID;


The query you want to edit is the qryYTDPointStatusForAllDrivers which I do not see in your database.

That query looks like this:

SELECT qryYTDPointSummaryForAllDrivers.pkDriverID, qryYTDPointSummaryForAllDrivers.LastName, qryYTDPointSummaryForAllDrivers.FirstName, 95+qryYTDPointSummaryForAllDrivers.YTDPoints AS YTDPointStatus
FROM qryYTDPointSummaryForAllDrivers;


You can substitute the Dlookup() in place of the 95 (everything else remains the same). Here is the text of the query with the Dlookup()

SELECT qryYTDPointSummaryForAllDrivers.pkDriverID, qryYTDPointSummaryForAllDrivers.LastName, qryYTDPointSummaryForAllDrivers.FirstName, Dlookup("lngPointResetValue","tblPointReset", "pkPtResetID=1")+qryYTDPointSummaryForAllDrivers.YTDPoints AS YTDPointStatus
FROM qryYTDPointSummaryForAllDrivers;

 

[michellecahal]

Of course I was editing the wrong query.... It's been a Monday all week. Thank you.

Looking at your database, you were editing the wrong query. The query qryYTDPointSummaryForAllDrivers just gathers all the points (sum of lost & gained or 0 if none: see the IIF() function). This query uses a left join to the driver table so that you get a record for every driver even if they had no point transactions

SELECT tblDrivers.pkDriverID, tblDrivers.LastName, tblDrivers.FirstName, IIF(IsNull(qryYTDPointSummaryByDriver.YTDPoints),0,qryYTDPointSummaryByDriver.YTDPoints) AS YTDPoints
FROM tblDrivers LEFT JOIN qryYTDPointSummaryByDriver ON tblDrivers.pkDriverID = qryYTDPointSummaryByDriver.fkDriverID;


The query you want to edit is the qryYTDPointStatusForAllDrivers which I do not see in your database.

That query looks like this:

SELECT qryYTDPointSummaryForAllDrivers.pkDriverID, qryYTDPointSummaryForAllDrivers.LastName, qryYTDPointSummaryForAllDrivers.FirstName, 95+qryYTDPointSummaryForAllDrivers.YTDPoints AS YTDPointStatus
FROM qryYTDPointSummaryForAllDrivers;


You can substitute the Dlookup() in place of the 95 (everything else remains the same). Here is the text of the query with the Dlookup()

SELECT qryYTDPointSummaryForAllDrivers.pkDriverID, qryYTDPointSummaryForAllDrivers.LastName, qryYTDPointSummaryForAllDrivers.FirstName, Dlookup("lngPointResetValue","tblPointReset", "pkPtResetID=1")+qryYTDPointSummaryForAllDrivers.YTDPoints AS YTDPointStatus
FROM qryYTDPointSummaryForAllDrivers;

 

[jzwp22]

No problem. Everyday seems like Monday where I work. I do the Access stuff on the side to keep myself sane.