Query returning data for every day of a period.

  • Thread starter Thread starter Annette
  • Start date Start date
A

Annette

I have a query that tracks time spent at a location. The query returns
a date and sum of hours. Some days, however, there is no work
performed. As a result, the query may return data for 20 days in a 30
day month. How can I have the query return time for 30 days with the
missing days showing a time of 0 so I am able to graph the data
showing the ups and downs to account for every day in a period.
 
I have a query that tracks time spent at a location. The query returns
a date and sum of hours. Some days, however, there is no work
performed. As a result, the query may return data for 20 days in a 30
day month. How can I have the query return time for 30 days with the
missing days showing a time of 0 so I am able to graph the data
showing the ups and downs to account for every day in a period.
Click to expand...

You can do this with the help of a little auxiliary table. It could contain a
date field with one record for every day in the span of time of interest; you
can use an Outer Join query to join this to your existing query ("show all
records in tblCalendar and matching records in ...") to get one record per
day.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Back
Top