How do I get my query to list months that have no data records?

[Guest]

I want to do a query of data and sum totals by month. Some months don't have
any records so they don't show up on my query. I want to have all months
show up so I can use the query in a report or download into Excel and account
for all 12 months of each year. How do I make the empty months show up on my
query?

 

[Marshall Barton]

I want to do a query of data and sum totals by month. Some months don't have
any records so they don't show up on my query. I want to have all months
show up so I can use the query in a report or download into Excel and account
for all 12 months of each year. How do I make the empty months show up on my
query?

You will have to have a table for all the months and use an
outer join to your current table. I would make a
suggestion, but you didn't provide enough details.

 

[Van T. Dinh]

A bit of complications since a Query cannot display data if data is not
there.

What you can do is to use a Query "Q1" to create a "dummy" Record for "zero"
for each month in the required year(s). You can then use a Union Query to
combine rows from Q1 and your actual Table so you have at least one row for
each month.

You can then do a Total Query which should have a total for each month (the
month without real Records showing zero) in the required year(s).