Solved

Query - do I need temp table?

Posted on 2015-02-01
7
178 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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 34

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 33

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 33

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

861 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now