Crosstab column heading

[Chris Green]

I used to be quite a "power user" of Access up to about 7 years ago, then a different job pulled me in another direction altogether (Cycling Instructor!). I'm currently making a database for my employer to keep track of the timesheets for about 24 instructors and it's apparent to me that I've forgotten "quite a lot"!

Naturally I've tables of names and so on, and tables of their timesheet submissions by week number and month. What I'm trying to compile is a crosstab query that will provide me with a sanity check on how many (if any) timesheets I've had from each person per month so I can chase missing ones before the data goes to payroll.

A basic crosstab does the trick for anyone who's submitted at least one timesheet, but it fails to catch those who've sent nothing in. My set up is Column: Name, Row; Week number and Value: Sum of Hours.

I seem to recall that there's a way to "force" nil returns by doing something manual with the column headings but I can't for the life of me remember what it was!

Any advice gratefully received!

 

[jdraw]

I used to be quite a "power user" of Access up to about 7 years ago, then a different job pulled me in another direction altogether (Cycling Instructor!). I'm currently making a database for my employer to keep track of the timesheets for about 24 instructors and it's apparent to me that I've forgotten "quite a lot"!

Naturally I've tables of names and so on, and tables of their timesheet submissions by week number and month. What I'm trying to compile is a crosstab query that will provide me with a sanity check on how many (if any) timesheets I've had from each person per month so I can chase missing ones before the data goes to payroll.

A basic crosstab does the trick for anyone who's submitted at least one timesheet, but it fails to catch those who've sent nothing in. My set up is Column: Name, Row; Week number and Value: Sum of Hours.

I seem to recall that there's a way to "force" nil returns by doing something manual with the column headings but I can't for the life of me remember what it was!

Any advice gratefully received!

Chris,
I'm not a crosstab query user, but I toyed with your sample. I think , as long as you have identified all persons, the cross tab will give you results and show those who did not submit time sheet with blank entries. These blank entries indicate no timesheet submitted. Those with sum week's hours less than weekly max hours indicate
a) not all sheets submitted for the week, or
b) did not work full week....

My sample used 4 people - Jim, Bob, Tom, BonnieSue
I had time sheet entries for each in week 1, although their hours didn't necessarily add to a full week's hours[my sample 37.5]. (Jim 37.5, Bob 37.5, Tom 10, BonnieSue 0)

For week 2 - Jim 0, Bob 37.5, BonnieSue 37.5, no time sheet for Tom

Attached jpg shows the result.

Here is the SQL:

TRANSFORM Sum(CrossTab.Hours) AS SumOfHours
SELECT CrossTab.WeekNo, Sum(CrossTab.Hours) AS TotalHours
FROM CrossTab
GROUP BY CrossTab.WeekNo
PIVOT CrossTab.Name;


Hope it helps.

 

[Pat Hartman]

You need to create a query that joins the employee list to the hours query using a left join. This will give you a recordset with a record for each employee whether or not they submitted hours. You would then crosstab that query.

 

[Chris Green]

Many thanks guys - I've created a "halfway house" select query to give me my list of all staff, joining name in the staff table to name in the timesheets table forcing a list of everyone irrespective of whether they submitted timesheets or not (I used the second option in the Join Properties). Then I created a crosstab based on that.

I've now got Row= Name, Column = Week Number and Value= Sum Of Hours.

It works fine with the minor annoyance that to allow for those who didn't show any timesheets, the week numbers are <>,1,2,3,4 (and sometimes 5 where needed) so I've just narrowed the 1st column till you can't see it!

It wasn't how I remembered doing it from before, but heh, it works and it gives us our sanity check on timesheets that may or may not need chasing before payroll get their teeth into it!

 

[DCrake]

Simple Software Solutions

When I use cross tab queries that are date dependant, say for year to date activity. I like to show all 12 months in the cross tab even if there is only, say, three months in to the financial year. How I accomplish this is by selecting Column Heading property for the column data and enter the periods manually. For example

Apr 2008, May 2008, Jun 2008, etc, etc, Mar 2009

Now in my column heading for the field it groups by Format(InvDate,"mmm yyyy")

So even if there is no data for a particular month the column heading is still displayed and blank or null values are displayed. This makes sure the layout is constant and all column headings appear.


codeMaster:

 

[Chris Green]

When I use cross tab queries that are date dependant, say for year to date activity. I like to show all 12 months in the cross tab even if there is only, say, three months in to the financial year. How I accomplish this is by selecting Column Heading property for the column data and enter the periods manually. For example

Apr 2008, May 2008, Jun 2008, etc, etc, Mar 2009

Now in my column heading for the field it groups by Format(InvDate,"mmm yyyy")

So even if there is no data for a particular month the column heading is still displayed and blank or null values are displayed. This makes sure the layout is constant and all column headings appear

codeMaster:

Thanks for that - I knew I wasn't dreaming. You've confirmed that there is a way to force headings by inserting the correctly spelled names of columns manually, and I've introduced this into my existing effort mentioned in the above post as weeks 1 2 3 4 and 5 even though we seldom get a "5-week" month. At least it no longer shows the <> as a column heading to allow for those people with no pay claims.

I think it's better for me to have the week numbers as the column headings. My first thought to have the staff names as a column heading could be very laborious with 24 of them, plus the fact that the list would need keeping up to date manually - unless I could create some kind of comma-separated list that could be pasted into place in the Properties Box for the column whenever there's a staff change to be made.
.

 

[DCrake]

Simple Software Solutions

Hows your VBA?

What you can do is to loop through all your staff names in the respective table and concat them with quotes and commas. Then prior to running the query you could use the querydef.sql property to re-write the sql statement. Especially the part for the column headings and then run the query. This should do the trick.

David

 

[Chris Green]

Hows your VBA?

What you can do is to loop through all your staff names in the respective table and concat them with quotes and commas. Then prior to running the query you could use the querydef.sql property to re-write the sql statement. Especially the part for the column headings and then run the query. This should do the trick.

David

Well, I did say I hadn't touched this for several years David, and VBA is on my list of things to re-acquaint myself with. Sounds like a nice little project to let myself back in gently!