INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Showing Months that have not data

Showing Months that have not data

(OP)
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?

RE: Showing Months that have not data

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

RE: Showing Months that have not data

(OP)
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,

RE: Showing Months that have not data

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

RE: Showing Months that have not data

(OP)
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,

RE: Showing Months that have not data

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:

Quote (SQL)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close