Solved Return empty records with a zero for each [ID] (1 Viewer)

[dawsonrhodes]

Hey all,

Not sure how to title this, as I'm not sure what to even call this. Essentially the database I'm building is for transaction records, the query in question totals the transactions and sums them for each person. I'll attach a screenshot below.

----------------

The query

----------------
The result

---------------

The issue here is that at 4AM everyday the query will "reset" and only show records from 4AM-3:59AM the next day.

When this happens, there are no records associate with the "PatronID", therefore no records sum, and thus the first total form (screenshot 1) goes blank.

This isn't a huge deal, but it's a quality of life feature I'd like to fix.

------

So what I am asking is, how could we make it so that it always shows each [PatronID] (from tbl.Patrons) with a "SumOfTotal" of 0, that way a PatronID is still associated, and when I go to the parent form, this subform has a PatronID to match it to.

Let me know if I need to explain better anywhere!

Cheers!

 

[dawsonrhodes]

=Nz([SumOfTotal],"0")

Could something like this fix the issue on the form? The main issue is that again, if there is no PatronID in the query, It results in zero.

Just brainstorming, not sure how to proceed!

UPDATE: Does not work, as I mentioned it all comes down to no PatronID to link it to.

=Nz([PatronID],"0")

Also doesn't work

 

[arnelgp]

another possible solution is to Create a Union Query to your Total query:

Select PatronID, SumOfTotals From yourTotalQueryName ORDER BY 1 DESC
UNION
SELECT TOP 1 0, 0 FROM anyTableName ORDER BY 1 DESC

you use this New query in place of the Original in your form/report.

 

[dawsonrhodes]

another possible solution is to Create a Union Query to your Total query:

Select PatronID, SumOfTotals From yourTotalQueryName ORDER BY 1 DESC
UNION
SELECT TOP 1 0, 0 FROM anyTableName ORDER BY 1 DESC

you use this New query in place of the Original in your form/report.

Hey arnelgp!

Thanks for coming to my rescue again. I've done this, and it works kind of - I'll attach a screenshot below and the code

Select PatronID, SumOfTotal From qryTransactionsExtendedAmountSum ORDER BY 1
UNION SELECT TOP 1 0, 0 FROM tblPatrons
ORDER BY 1;

The issue is that it only shows patron ID 51, as they have a SumofTotal, whereas PatronID 50 is not shown as they have no transactions.

How can I make it so it shows every PatronID, even if they have a SumOfTotal of 0?

Thank you again!

 

[arnelgp]

create a Query.
using your patron table, Left Join it to your Total Query:

Select A.patronID, Nz(B.SumOfTotals, 0) As Totals From yourPatronTable As A Left Join yourTotalQuery As B
On A.PatronID = B.PatronID;

use this as source of your form/report (disregard my previous suggestion).

 

[dawsonrhodes]

create a Query.
using your patron table, Left Join it to your Total Query:

Select A.patronID, Nz(B.SumOfTotals, 0) As Totals From yourPatronTable As A Left Join yourTotalQuery As B
On A.PatronID = B.PatronID;

use this as source of your form/report (disregard my previous suggestion).

Absolute genius you are. Thank you very much!

 

[dawsonrhodes]

create a Query.
using your patron table, Left Join it to your Total Query:

Select A.patronID, Nz(B.SumOfTotals, 0) As Totals From yourPatronTable As A Left Join yourTotalQuery As B
On A.PatronID = B.PatronID;

use this as source of your form/report (disregard my previous suggestion).

One last question about this, how would I format it as currency from the query, the field on the form is formatted but does not show the $ like the one beside it that is Unbound and DefaultValue = 0?

EDIT: Format(NAME,"Currency") worked!

 

[arnelgp]

while on Query design, click on the Column you want to format, on Property Sheet->format->Currency (you can manually type Currency).

 

[dawsonrhodes]

while on Query design, click on the Column you want to format, on Property Sheet->format->Currency (you can manually type Currency).

I've tried that with no luck, my solution was

TotalFormatted: Format([Total],"Currency")

Not sure why this didn't work in the property sheet?

 

[arnelgp]

 

[dawsonrhodes]

View attachment 91871

Hey there, mine is like that as well, but still no luck - it is pulling from a Sum on another query, could that cause this?

 

[arnelgp]

can you type "currency" (without quote) directly to the Format property?

 

[dawsonrhodes]

Here is the results of both, the third collum is the solution I came up with. This isn't a big deal at all, but is a mystery to me!

 

[arnelgp]

you convert it to Double, so it will align to the Right:

Total: CDbl(Nz([SumOfTotal], 0))

 

[dawsonrhodes]

you convert it to Double, so it will align to the Right:

Total: CDbl(Nz([SumOfTotal], 0))

Tried this and it worked, cheers!