Query for Missing Days

[94Sport5sp]

Hi:

Client has asked me to create a report showing summary of monthly sales by day. That was easy. I created a query for the month the user selected and then summarized and group the data by day. Client like the result but would like to see zeros on the report for non sales days. Non sales days are days like holidays and there are no sales.

So I am looking for suggestions on how to accomplish this. I am thinking of creating an table with 31 days of zero values and then join the two tables in a query? Or, should I create a temporary table with code and then merge the two tables which the existing query which I can then use for the report? Or?

Thanks for your thoughts.

 

[jdraw]

This should work

SELECT  saledate, Sum( sumamt) AS X
FROM
 (SELECT saleDate ,  saleamount  as sumamt from tblSalesTest
UNION
 select NoSaleDate as saledate, NoSaleAmount  as sumamt from  tblZeroSales 
 Order by saledate)
GROUP BY saledate ;

tblSalesTest is

id autonumber PK
Saledate Date
SaleAmount Currency

and tblZeroSales is

id autonumber PK
NoSaleDate Date
NoSaleAmount Currency


Here's a routine to populate the tblZeroSales

'---------------------------------------------------------------------------------------
' Procedure : PopulateZeroSales
' Author    : Jack
' Date      : 03-04-2013
' Purpose   : To popuate tblZeroSales with sample data.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub PopulateZeroSales()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim dt As Date
   On Error GoTo PopulateZeroSales_Error

dt = #3/1/2013#
Set db = CurrentDb
Set rs = db.OpenRecordset("tblZeroSales")
For i = 0 To 30
  rs.AddNew
  rs!NoSaleDate = DateAdd("d", i, dt)
  rs!NoSaleAmount = 0
  rs.Update
Next i
rs.Close
db.Close

   On Error GoTo 0
   Exit Sub

PopulateZeroSales_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure PopulateZeroSales of Module AWF_Related"

End Sub

You will have to work out holidays, and months with less than 31 days. But this seems to be a base to start.

This was the tblSalesTest data for March

id SaleDate SaleAmount
1 3/1/2013 $50.00
3 3/4/2013 $20.00
4 3/6/2013 $23.00
5 3/19/2013 $60.00
6 3/1/2013 $160.00

Good luck.

 

[94Sport5sp]

Hi jdraw:

Thanks for the query and the code. Ok then, a table with a query is the better way. Holidays are simply zero values so they should not be a problem. As for short months I will use code to add a where clause to limit the list.

Thanks

 

[gemma-the-husky]

alternatively, you could take a different approach with this one.

assuming sales never change after being reported, you could actually construct a permanent daily sales table, with all dates, and populate it once as appropriate per day, to include any other daily sales info you need.

then you have a ready made table available for future use.

 

[94Sport5sp]

Hi gemma-the-husky:

Yes I had considered that option but I did not like the work involved. Currently the Client gets sales data from up to 14 sales areas but not all areas have sales. That would mean creating sales data for all sales areas which have sales (normal procedure) and then creating zero sales data for all areas not reporting.

Thanks for your thoughts