Query, Show Zeroes

[jsic1210]

Okay, this is a complicated one. I'm not sure there's an actual solution. Each month, we have monthly excess usage charges from our clients. If our client does not have any excess usage, data is not entered for that customer in the database for that month. There is one entry per month per customer - the month end usage charge. So the table looks like this:

Customer     Monthly Charge     Month Ending
ABC Co.            $4,000       06/30/2013

I'm trying to make a query that summarizes by region (Region is queried from a different table). If no customers in North America have excess usage charges in June 2013, I still want a line in the query to show as:
North America $0 6/30/2012
Is there a way to do this in a query?

 

[pr2-eugin]

I hope you are suing JOINS, if so.. Change the INNER JOIN to LEFT/RIGHT JOIN, depending on the which side the table is placed.. Then use a simple IIF to place a ZERO if the field returns Null.

 

[namliam]

Yes there is a way, assuming (and the easiest way) you have a table that contains the regions and all of the regions.

In which case "simply" create a outer join query between the region table and this query and you have what you are looking for.

 

[jsic1210]

Thank you both for your responses, but I'm still having trouble making it work. Here is my SQL statement:

SELECT tblCPURegionPlans.[Region Plan], qryCPUMonthlyByRegion.[Billing Effective Date], nz(qryCPUMonthlyByRegion.[Monthly Charge],0)*1 AS [Month Charge]
FROM tblCPURegionPlans LEFT OUTER JOIN qryCPUMonthlyByRegion ON tblCPURegionPlans.[Region Plan] = qryCPUMonthlyByRegion.[Region Plan]
GROUP BY tblCPURegionPlans.[Region Plan], qryCPUMonthlyByRegion.[Billing Effective Date], nz(qryCPUMonthlyByRegion.[Monthly Charge],0)*1

 

[namliam]

Bar the fact that you shouldnt need the Group by part that SQL should return all values from your tblCPURegionplans and show any monthly charges associated with those plans.... dunno why that wouldnt be working...

SELECT tblCPURegionPlans.[Region Plan]
     , qryCPUMonthlyByRegion.[Billing Effective Date]
     , nz(qryCPUMonthlyByRegion.[Monthly Charge],0)*1 AS [Month Charge]
FROM            tblCPURegionPlans 
LEFT OUTER JOIN qryCPUMonthlyByRegion ON tblCPURegionPlans.[Region Plan] = qryCPUMonthlyByRegion.[Region Plan]

 

[jsic1210]

I think because there are no entries for any North American customers with the Billing Effective Date of 8/31/13 in the original table (on which qryCPUMonthlyByRegion is based). So it's not pulling in anything for that region for 8/31, if that makes sense.
The reason I had the Group By is because my original query had some calculated fields, but I stripped down my SQL statement for simplicity.

 

[pr2-eugin]

Possible to UPLOAD a stripped version of your DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[jsic1210]

Reading through my posts, I'm realizing I did a poor job explaining. Let me see if I can reword it. Let's say I have the following entries:

Company     Region          Billing Effective Date     Monthly Charge
ABC Co.     N. America                  07/31/2013            $1,000
XYZ Co.     Europe                      07/31/2013            $2,000
XYZ Co.     Europe                      08/31/2013            $3,000

I still want my query to have a record show N. America 8/31/2013 $0

pr2-eugin,
Yes, I can post a stripped down DB.

 

[jsic1210]

Okay, I attached a stripped down version. As you can see, in both queries, qryCPUMonthlyByRegion and qryCPUPendingCharges, I cannot see a line for North America for $0 on 8/31/13. It doesn't matter which query that line displays, so either one is good. Thanks!