Replace Null Values With Zeros/Put Dummy Values into an Empty Table In Access

I am trying to create a report of charges and credits based on a table of Charges and Credits that are parsed from a CSV file. The Charge Table and Credit Table look basically the same, with only one field being different, as shown below

BillingPeriod | ItemCode | ItemName | Charge(Credit) | IsAdjustment | AdjustmentDate | ReportTimestamp

To create this report I group the charges and credits and sum them based on the item codes I have. For example, one query I have working that sums ancillary charges for all billing months would be

SELECT Charges.BillingPeriod, Sum(Charges.Charge) as SumOfCharge
FROM Charges
WHERE (Charges.ItemCode Between 1301 and 1380)
GROUP BY Charges.BillingPeriod;

That is 开发者_JS百科all fine and dandy, but the problem arises when the Item Code is not found. I want these queries to be flexible enough that it will display a summary with or without a particular charge/credit, because you might not be charged for it one month, and you may be charged for it during another month.

The query in question that does not work is this one:

SELECT Credits.BillingPeriod, Sum(Credits.Credit) AS SumOfCredit
FROM Credits
WHERE (((Credits.ItemCode)=2217 Or (Credits.ItemCode)=2218 Or (Credits.ItemCode)=2241 Or (Credits.ItemCode)=2245 Or (Credits.ItemCode)=2260))
GROUP BY Credits.BillingPeriod;

Since we have not been credited for those particular item codes, they cannot be summed, and the result set is an empty table. Is there a way I could just maybe return a null billing period and a 0 for SumOfCredit if such a situation arises? I tried the Nz() function, as well as IIf(IsNull()) but that hasn't helped me.

Any help would be appreciated.

EDIT: I changed the query so that It looks like this:

SELECT Charges.BillingPeriod, SUM(Charges.Charge) AS SumOfCharge
FROM Charges
WHERE (((Charges.ItemCode) Between 1900 And 1995))
GROUP BY Charges.BillingPeriod
UNION
SELECT BillingPeriod, 0
FROM Charges
WHERE NOT EXISTS ( SELECT * FROM Charges WHERE (Charges.ItemCode Between 1900 And 1995));

This works, but it doesn't display ALL billing periods. For example, if one particular item code has a charge for 1/1/2011 and 2/1/2011, it will show those two records ONLY. I want it to show every appliccable billing period in the table. Charges where there are charges for the month, and zeroes where there arent. I tried adding a third UNION onto the end of the query, Selecting BillingPeriod, 0 again, but it adds a duplicate record for a month, which I don't want. Again, any help would be awesome.

Firstly, following the "less code is good" mantra, reduce your clumsy OR's into one IN:

WHERE Credits.ItemCode in (2217, 2218, 2241, 2245, 2260)

Next, union with a table of all non-existent ItemCodes:

SELECT Credits.BillingPeriod, Sum(Credits.Credit) AS SumOfCredit
FROM Credits
WHERE Credits.ItemCode in (2217, 2218, 2241, 2245, 2260)
GROUP BY Credits.BillingPeriod
UNION
SELECT null, 0
WHERE NOT EXISTS (SELECT * FROM Credits
    WHERE Credits.ItemCode in (2217, 2218, 2241, 2245, 2260));

I don't if Access allows no FROM clause - if it doesn't select from DUAL or some other one-row result set (eg FROM SYSTABLES WHERE ID = 1 or whatever)