Showing Months that have not data

[puforee]

We have a data base to track our training customers End of Course Critiqe information. I have a requirement to be able to show the last 4 months of data from Now(). This works. I have now been given a new task to show all 4 "Month Names" even when there is not data for one or more of the months. So this is what they want (basically):


Data More data.........fields
Jan Null Null for all fields (no records)
Feb 25 21 etc......
March 32 11 etc......
April 2 55 etc......

I am not sure how to include the month that has null data. This is to make the show and tell at meeting consistant. This is actually down about 12 times so they want all 4 months to show on each presentation.

I am using criteria in a query to gather data from "Now()" to (basically) "Now() - 4 months". I have the crteria set for whole months worth of data so I don't get partial months.

Any Ideas?

 

[dhookom]

Basically you need to have a dataset with all months. Then use an outer join (LEFT or RIGHT) to include all of the records from the all months dataset.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Duane...thanks for the suggestion...I would like it to carry it a little further please. So, I built a table with 2 fields. Month 3 letter name and Month number. I then queried the table based on current month (Now) and set a parameter to return current month -3 (basically). I got what I wanted Aug, Jul, Jun, May. Now the issue becomes...how do I get a return if the (Now) month is 2 Feb. How would it return Nov, Dec, Jan, Feb. Then I thought, in this senario the year will have to change also so Nov 2014, Dec 2014, Jan 2015, and Feb 2015.

I am trying to do this by dealing with dates. Is there a way I can deal with getting the four months by limiting the query output? Like...if the month was Feb I could set the starting month to Nov and just return 4 records. Or...is there an easier way that you might know.

Thanks,

 

[dhookom]

Can you tell us what you mean by "This is actually down about 12 times so they want all 4 months to show on each presentation"?

Can you share an existing query SQL view?

I would actually build a table or query that returns all Year and Month numbers.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Duane,
The 12 times means I will 12 different sets of data to query using what we come up with to calculater the months. This is based on different catagories of Critique Sheets. This will not figure into our queries we are working on...I will just have to use the output in many different places. All 12 catagories will always be queried at the same time (day) so the month selection will work for all.

I am working on what you suggested about the years and months table. Is there a way to set this up without manually entering the data.

I will share sql with you when get to the next step. I built, manually, a table for 6 years. I also assigned a sequence number to each record...1 through 72. So, if I know the Year and Month (Now(), I can select the starting sequence number and return it and the 3 before it. My only issue is the table stops at Dec 2020 and I would rather have it continue as long as it is needed.

Thanks,

 

[dhookom]

You can use code or an append query to create additional year/months. Another method is to build the records in Excel and then paste into a table.

You could create a query like:

SELECT Yr, Mth, myExistingQuery.*
FROM tblYrMth LEFT JOIN myExistingQuery on Yr = myExistingQuery.MyYear AND Mth = myExistingQuery.MyMonth

Duane
Hook'D on Access
MS Access MVP