Query Issues - Need field to be 0 instead of not displaying

[Michellephant]

Hello,



I am trying to make a query that includes 3 other queries. The primary key is customer number and I am trying to pull the sales for each customer by week, month, and quarter. If a customer has not purchased anything it does not display at all. Also, if they purchased something in the quarter or month but not in the past week it doesnt display. I would like the sales for the week, month, or quarter to display 0 if no transactions were made. It is not feasible to add a record for each customer with "0" since this would be updated daily. Please help!

[John_G]

The best option is to use the Nz function, to display 0 if a field is Null:

ColumnTitle:Nz([queryfieldname],0) as the field name in Query Design view.

[Michellephant]

Thank you for your reply. I put Nz([Current Month]![Orders],0) in the field of each, Current month being the Query name and Orders being the field but it is still not displaying 0's.

[darshit_goswami]

try:

IIF(IsError([Current Month]![Orders]),0,[Current Month]![Orders])

[June7]

Apply the period filter in a query then join that query to the Customers table using JOIN "Include all records from Customers ..."

Another approach would need a dataset that has a record for each customer for each period so that when records are filtered for a period every customer will still show. Dummy records is one way and I understand why you reject it. So this gets a little difficult, especially because date periods are involved and the period filtering drives off of the order dates.These datasets can be generated with queries that include tables without JOIN clause which results in a Cartesian relation - every record of each table joins with every record of other table so the result is every possible combination of customer and something, in this case a date period. Then that query can be joined to the actual data table/query ("Include all records from {the Cartesian query} and only those from {table/query} that match") by linking on the common customerID and date fields.

This same issue has been topic of numerous threads. Search on "Cartesian" as well as my username to narrow the results.

Other alternatives:

1. domain aggregate functions - I do this on one report to assure that I have an aggregate value 'record' displayed for every month of year even if there was no data in any month - each 'record' is really just a textbox with a DSum() expression that aggregates for each year/month - 12 textboxes

2. VBA and temp tables

Good Luck.

[Michellephant]

Thank you so much! I didn't know I could change the join Properties to show all Records from the primary key so thats what was messing me up. Your answer combined with John_G's solved my problem! Also thanks to darshit_goswami for trying to help! You guys are AWESOME! I was searching for an answer the past couple days and decided to post, Im glad I did!