Need A Running Total

[ztirffritz]

I'm trying to get a running total in a query, but I'm struggling to get it to work the way I think it should.

I have 4 fields in the query as follows:

1) ToolNum(ShortText)
Values: A-J

2) PullDate: (Date/Time)
Values: 6/1/2013 - 12/10/18

3) ReleaseDate: (Date/Time)
Values: 7/1/2013-12/10/18 (most records are null in this field)

4) RCount: Expression
RCount: Sum(IIf(("PullDate"<DMax("ReleaseDate","qryPullLog ") Or (IsNull([ReleaseDate]=True))),1,0))

I'm trying to get the RCount field to be a running count. Eventually, I'll want RCount to reset to 0 when ReleaseDate isn't null, and I'll want to display an alert on a form when RCount >= 20.

Currently, RCount results in "1" or "0" so at least part of the logic is working with 'IIF', but I'm obviously missing something with 'SUM'. What I've read indicates that it should be performing a running tally in the RCount field.



For background, the PullDate is the date when a tool is used. ReleaseDate is the date when maintenance has been performed on the tool. RCount is the number of cycles the tool has been subjected to between maintenance intervals (ie ReleaseDate). When a tool is used the intention is that an employee will scan the tool's barcode and record a 'PullDate' entry. This should increment 'RCount' by 1. When the running total >= 20 I'll have a field on a related form display an alert that maintenance is due. When maintenance is performed it should enter a record for 'ReleaseDate' and set 'RCount' back to 0.

[ztirffritz]

I've attached the DB. The query I'm working on is qryReleaseCount. This is obviously still very firmly in the development stage. I need find a way to count the tool cycles, generate an alert, and reset it to 0 when maintenance is performed. All the data is generally generic just for proving concepts during development.

[Micron]

Example of desired output might help. PullDate as you have it, is a textual string and probably should be [PullDate]. Also, the IIF is missing the alternative for when the first part is not true. The OR part is, I think, ambiguous as it's basically like you're saying give me A or B. I doubt that's what your intent is. Also, AND or OR logical operators often have to be grouped to get the desired affect. Then, if there is only one ReleaseDate, you can only make one evaluation of your expression because < Null is invalid because Null cannot be compared to anything.

I'm not sure where you'd begin given all that and not knowing what some valid results might be.

[ztirffritz]

OK, I must have been punch drunk when I wrote this. I had a <...when it should have been >. Also, the "isnull"...part shouldn't have been in there at all. That was me trying to figure out why I was getting 0s instead of 1s (see < vs > comment earlier). Now I just need to figure out why it isn't summing all the 1s.

[Micron]

Now I just need to figure out why it isn't summing all the 1s.

In simple terms, you are trying to SELECT a 1 or 0 depending on the result of an expression, AND sum it in the same field. Not sure you can, but you can SELECT 1 or 0 in one field and in another field, SUM that field if you don't mind getting the sum in every record. You can also turn totals on for a query in datasheet view (summation character, ribbon) but I don't believe you can access that total outside of the query datasheet view.

I don't see why you're using DMax on another query when that query and the one you're trying to sum in both use the same table. Why not just DMax on the table in your summing query?
EDIT - upon reviewing your prior posts (and still not being 100% certain of the meanings) can you not just DCount the date field in tblPullLog and GroupBy PartNum?

[Micron]

double posted by accident - removed second post for now

[ztirffritz]

I haven't been able to get DCount to work. I'll keep trying.

[Micron]

SELECT tblPullLog.PartNum, Count(tblPullLog.PullDate) AS CountOfPullDate,
DCount("PullDate","tblPullLog") AS SumOfPulls FROM tblPullLog
GROUP BY tblPullLog.PartNum, DCount("PullDate","tblPullLog");

Something like this?