Count records by ID that are Null

[mcupito]

I am trying to figure out how many payouts remain for each award per year, so I can say "you have been paid out 1 out of 3 installments."

The data will look like this:
PayoutID PayoutDate PayoutUnits CheckRequestDate AwardID
1111 2/7/2014 5 2/6/2014 1234
1112 2/7/2015 5 1234
1113 2/7/2016 5 1234

I am trying to use DCount for the number of payouts remaining. (when CheckRequestDate IS Null) .. When I try

DCount("AwardID","PayoutTbl","CheckRequestDate Is Null")

It gives me a count of the entire table (43) when in this case it should return 2.

The SQL is here:

SELECT Sum([PayoutTbl]![PytUnits]*[NAV_Tbl]![NetAssetValue]) AS PytGross, CheckTbl.CheckDate, Sum(PayoutTbl.PytUnits) AS SumOfPytUnits,
 CheckTbl.TaxFederal, CheckTbl.TaxState, [CheckTbl]![TaxFicaR]+[CheckTbl]![TaxFicaM] AS [Fica Tax], 
CheckTbl.TaxLocal, AssociateTbl.EmployeeID, AssociateTbl.AstFirstName, AssociateTbl.AstLastName, AssociateTbl.HomeStreet, AssociateTbl.HomeCity,
 AssociateTbl.HomeState, AssociateTbl.HomeZip, DCount("AwardID","PayoutTbl","CheckRequestDate Is Not Null") AS Expr1, PayoutTbl.PytID
FROM AssociateTbl INNER JOIN ((NAV_Tbl INNER JOIN (CheckTbl INNER JOIN PayoutTbl ON CheckTbl.CheckNo = PayoutTbl.CheckNo) ON NAV_Tbl.NAV_Date = PayoutTbl.PytNAV) 
INNER JOIN AwardTbl ON PayoutTbl.AwardID = AwardTbl.AwardID) ON AssociateTbl.EmployeeID = AwardTbl.EmployeeID
GROUP BY CheckTbl.CheckDate, CheckTbl.TaxFederal, CheckTbl.TaxState, [CheckTbl]![TaxFicaR]+[CheckTbl]![TaxFicaM],
 CheckTbl.TaxLocal, AssociateTbl.EmployeeID, AssociateTbl.AstFirstName, AssociateTbl.AstLastName, AssociateTbl.HomeStreet,
 AssociateTbl.HomeCity, AssociateTbl.HomeState, AssociateTbl.HomeZip, DCount("AwardID","PayoutTbl","CheckRequestDate Is Null"), PayoutTbl.PytID;

I know it's extensive, however the Count is the only thing calculating incorrectly.

[Rabbit]

The DCount in your SQL doesn't match the DCount in the code block above it.

[mcupito]

Sorry! I had accidentally tried something else and did not realize it. It is updated.

[Rabbit]

But you updated it to "Is Not NULL". I thought you were trying to count all the records that are null?

[mcupito]

Either way will work, really. They are both going to be used the same. I will update them back to Null due to the question title. Sorry for the confusion.

[NeoPa]

I'm pretty sure that whatever you want must match the [AwardID] of the current record. That said, it isn't clear how your data indicates whether or not a payment has been made or not.

As such, I would expect the DCount() code to be similar to :

DCount("*","PayoutTbl","([AwardID]=" & [AwardID] & ") AND (...)")

The ellipses need to be determined and replaced when we know what else we're looking for.

PS. You'd need to GROUP BY [AwardID] but set the DCount() as an expression. It shouldn't need to appear in the GROUP BY clause.

Excellent. Then you want something like :

DCount('*','PayoutTbl','([AwardID]=' & [AwardID] & ') AND ([CheckRequestDate] Is Null)')

The quotes used are correct for SQL.

[mcupito]

A payment has been made when the CheckRequestDate has a date (Is Not Null) .. I will look into your solution when I return from lunch. Thanks, @NeoPa.

[NeoPa]

Excellent. Then you want something like :

DCount('*','PayoutTbl','([AwardID]=' & [AwardID] & ') AND ([CheckRequestDate] Is Null)')

The quotes used are correct for SQL.

[mcupito]

Works magical, NeoPa. Thank you. I would have never guessed the AwardID aspect.

It seems like it is taking a performance hit, why do you think that is?

[NeoPa]

Always a pleasure M :-)

[mcupito]

@NeoPa how would the code differ if I included the AwardDate in the query? I was just informed the format needs to be changed for the query, and the SQL you wrote is giving me incorrect data now that I included the AwardDate in the query.

2 Payouts are now 4, and 8 remaining payouts are now 16.

[NeoPa]

Included it in what way M?

Your question needs to be expressed more specifically.

[mcupito]

You can just say Mark if you want.. I feel odd being referred to as a letter haha..

As the query functioned prior to the new request, the code you wrote worked perfectly, and returned the correct information. I had also reversed the logic to find the count for "Is Not Null" and I added them both together to get the total number of payouts.

Pleasantly enough, the records are all going to be grouped by the date the awards were given instead of the way they were grouped. So, when I incorporated this into the query, all of numbers doubled for the DCount functions.

"2 Payouts are now 4" = Payouts were the number of "Is Not Null" CheckRequestDates
"8 remaining payouts are now 16" = The DCount you wrote, which retrieved the number of "Null" CheckRequestDate fields.

It seems as though adding in the AwardDate through a wrench into the query and I tried to play around with the DCount by changing fields, but I was only screwing it up worse.

What are your thoughts? And where did you learn the " '([AwardID]=' & [AwardID] & ') " piece?

Although this is my last MS Access project, it is good knowledge (for perhaps other users trying to do the same thing!)

[NeoPa]

I was being circumspect about your name Mark. If you share information with me that you haven't made public then I have to be careful not to make it public on your behalf. Just in case it's important to you to keep it private ;-) Now I'm clear you're happy then I will be less careful.

As for your requests for further clarification on related issues, I'm afraid I didn't really understand what you were saying clearly enough to make sense of it :-( I would need a very precise understanding for that.

Where did I learn about "'([AwardID]=' & [AwardID] & ')"?
I'm really not sure which bit you want explaining. The double-quotes are yours. The single quotes are used by SQL to delineate string literals. Everything between the quotes is passed as is, whereas the stuff outside of the quotes is interpreted by SQL as a reference or as SQL code. Thus 'XXX' & [AwardID] & 'YYY' comes out as (EG. When [AwardID]=324.) XXX324YYY. This is what SQL tries to interpret. In the actual SQL we passed it comes out as :

([AwardID]=324) AND ([CheckRequestDate] Is Null)

Clearly, every time [AwardID] changes the number changes - but the rest of the string stays the same.

You'll see how that makes sense in SQL. I hope this helps.

[mcupito]

NeoPa, maybe if I post the old data results vs the new, it will be more easily understood.

When I include the 'AwardDate' into my query, it throws the Paid out / Remaining / Total units off. (It doubles them)

I need to keep the values from the code you wrote, and not have them double. I hope this is clearer.

[NeoPa]

That helps very little I'm afraid Mark. The data's not legible even when zoomed in and it doesn't come with the associated SQL.

If you were to strip the SQL right down to only those elements that are important to the question and post both versions (as text in [code] tags) and include with that a limited set of data to go with it (One set for each SQL), then I'd have something from which I could try to work out what your question is.

[mcupito]

Sure - sorry for the delay.

In the query, if I

GROUP By AwardTbl.AwardUnits

then the numbers in these lines of SQL double:

Remaining: Sum(DCount('*','PayoutTbl',' ([PayoutTbl]![AwardID]=' & [PayoutTbl]![AwardID] & ') AND([CheckRequestDate] Is Null)'))
PaidOut: Sum(DCount('*','PayoutTbl','([PayoutTbl]![AwardID]=' & [PayoutTbl]![AwardID] & ') AND ([CheckRequestDate] Is Not Null)'))
Sum: Sum(DCount('*','PayoutTbl','([PayoutTbl]![AwardID]=' & [PayoutTbl]![AwardID] & ') AND ([CheckRequestDate] Is Null)')+DCount('*','PayoutTbl','([PayoutTbl]![AwardID]=' & [PayoutTbl]![AwardID] & ') AND ([CheckRequestDate] Is Not Null)'))
 

If I DON'T Group By [AwardTbl].[AwardDate], the numbers aren't doubled.

The unfortunate reality is that I need the AwardDate to group them by year on the report.

[NeoPa]

Ah. The penny is starting to drop.

I suspect the culprit in this case is that you're using Sum() of the DCount() calls, whereas they should be included as Expression in your QueryDef.

Alternatively, of course, if the DCount() should actually be a Count(), then this simplifies everything and also makes it work very much more efficiently. That may be a question for another thread of course.

[mcupito]

Ah! That's it. Thanks, NeoPa! Sorry for the long drawn-out contusion of an issue.

[NeoPa]

No worries Mark. We got there in the end ;-)