Missing Time periods

[richary]

Hi

I am currently developing a sales tracking database. As part of the reporting function I need to generate a graph showing number of sales, cumulative sales etc for various time periods (week, month, quarter, year).

What with the current state of the economy it is just possible D) that no sales would take place at all in a particular time period. At the moment my query just misses out that time period completely - but I need it to show zero. How do I do this?

Current SQL below:

SELECT Last(qryRunningQuery.Edate) AS LastOfEdate, Format([Edate],"mmm-yy") AS y, Last(qryRunningQuery.RunningPrice) AS LastOfRunningPrice
FROM qryRunningQuery
GROUP BY Format([Edate],"mmm-yy")
ORDER BY Last(qryRunningQuery.Edate);

Thanks

 

[namliam]

Add a dummy table to your database that contains all the periods that you need. Then "union" this table into your query qryRunningQuery.

In you qryRunningQuery, add something like
UNION
Select x, y
from YourDummyTable

Be sure to remove the semicolumn from the previous statement.

Hope this is clear enough, good luck.

 

[richary]

That work great - thanks!