Query - do I need temp table?

Dear experts -
I have a query in which I summarize weekly totals of hours by employee.
Of course, some weeks, employees will not have a total.
I want to show ALL weeks in a given period, and then the total for that person.

I imagine I can do this using a temp table populated with one record for each week in question, and then do a join with the query of totals. Thus, I could show all weeks, and if the total is zero for that person in a given week, it will show zero.

My question is whether there's a better/simpler way, without using a temp table.

Thanks -
terpsichoreAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
This technically isn't a temp table since it isn't deleted and recreated.  When working with a large enough population, you are likely to have an entry for every week.  However, when working with a single individual, there will be gaps so you do need a way to provide structure so the "gaps" can be replaced with 0.  

Build a table with the week starting and ending dates (this also solves the problem of how you handle year crossover weeks.  Then every November (or whenever makes sense), add another year to the table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
Well, yes and no. It's a common practice to use a calendar table. Then you can use this table to join in your data. So you need a table, but not a temporary one.

Simply prefil the table with five or ten years - just ensure that it covers your current and near future use-cases. Such a table will not take much space.
0
terpsichoreAuthor Commented:
thanks so much (this answer was first).
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

IrogSintaCommented:
Another option is to create a table with 52 records numbered from 1 to 52.  These are your week numbers.  You won't ever have to add anymore to this table.  Then you can use a query that will always provide the correct beginning and ending dates for each week no matter what year it is.  You will use this query to join with your existing table.  Here's what the SQL for the query should be:
SELECT WeekNum, DateAdd("ww",[weeknum]-1,DateSerial(Year(Date()),1,1))-Weekday(DateSerial(Year(Date()),1,1),1) AS BegWeek, [BegWeek]+7 AS EndWeek
FROM tblWeeks
The above will give you the following:
imgThe week range is from Sunday to Saturday.  If you wanted your week to start from another day, you would change the number 1 which I have highlighted in bold in the query to a number between 1 to 7.  For instance, change it to 2 if your week is from Monday to Sunday.

Ron
0
terpsichoreAuthor Commented:
many thanks, great idea
0
ste5anSenior DeveloperCommented:
@Ron: It's better to create a full calendar, instead a condensed one. Just store these as additional columns.
0
IrogSintaCommented:
@ste5an
Come to think of it, it would be better to use a full table since the query I provided would only work for the current year.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.