Solved

Query - do I need temp table?

Posted on 2015-02-01
7
192 Views
Last Modified: 2016-02-11
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 -
0
Comment
Question by:terpsichore
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40582604
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
 
LVL 34

Expert Comment

by:ste5an
ID: 40582605
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
 

Author Closing Comment

by:terpsichore
ID: 40582616
thanks so much (this answer was first).
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 29

Expert Comment

by:IrogSinta
ID: 40582646
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
 

Author Comment

by:terpsichore
ID: 40583440
many thanks, great idea
0
 
LVL 34

Expert Comment

by:ste5an
ID: 40585810
@Ron: It's better to create a full calendar, instead a condensed one. Just store these as additional columns.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40586162
@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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question