Multiple Columns / Crosstab

[neirhart]

I need to create a "timesheet"...Employee names down the left side of page;
dates of the week across top and for each date, a column for ST, OT, and DT
(these have numeric values). I tried the crosstab query (built on several
other crosstab queries) which sort of worked but it only included dates
through which I had already entered data. When I entered new data on new
dates, it did not add a column; it simply through that new information into
the wrong column.

 

[Duane Hookom]

You could create a crosstab using relative dates. There is a solution
described at http://www.tek-tips.com/faqs.cfm?fid=5466 which uses monthly
columns. You should be able to modify that report to fit daily columns.

 

[KARL DEWEY]

Post the SQL of your crosstab query.

 

[neirhart]

SELECT [All Info Table Query].Employee, [All Info Table Query].Title, [All
Info Table Query].[W/E], [All Info Table Query].Date, [All Info Table
Query].[Job #], [All Info Table Query].[Job Name], [All Info Table
Query_Crosstab ST].[Total Of Straight Time], [All Info Table Query_Crosstab
ST].[4/27/2009] AS [All Info Table Query_Crosstab ST_4/27/2009], [All Info
Table Query_Crosstab ST].[4/28/2009] AS [All Info Table Query_Crosstab
ST_4/28/2009], [All Info Table Query_Crosstab ST].[4/29/2009] AS [All Info
Table Query_Crosstab ST_4/29/2009], [All Info Table Query_Crosstab OT].[Total
Of Overtime], [All Info Table Query_Crosstab OT].[4/27/2009] AS [All Info
Table Query_Crosstab OT_4/27/2009], [All Info Table Query_Crosstab
OT].[4/28/2009] AS [All Info Table Query_Crosstab OT_4/28/2009], [All Info
Table Query_Crosstab OT].[4/29/2009] AS [All Info Table Query_Crosstab
OT_4/29/2009], [All Info Table Query_Crosstab DT].[Total Of Double Time],
[All Info Table Query_Crosstab DT].[4/27/2009] AS [All Info Table
Query_Crosstab DT_4/27/2009], [All Info Table Query_Crosstab DT].[4/28/2009]
AS [All Info Table Query_Crosstab DT_4/28/2009], [All Info Table
Query_Crosstab DT].[4/29/2009] AS [All Info Table Query_Crosstab DT_4/29/2009]
FROM [All Info Table Query_Crosstab DT] INNER JOIN ([All Info Table
Query_Crosstab OT] INNER JOIN ([All Info Table Query_Crosstab ST] INNER JOIN
([All Info Table Query] INNER JOIN [Employee Listing] ON [All Info Table
Query].Employee = [Employee Listing].Employee) ON [All Info Table
Query_Crosstab ST].Employee = [Employee Listing].Employee) ON [All Info Table
Query_Crosstab OT].Employee = [Employee Listing].Employee) ON [All Info Table
Query_Crosstab DT].Employee = [Employee Listing].Employee;

 

[neirhart]

I'll check it out; thanks!

You could create a crosstab using relative dates. There is a solution
described at http://www.tek-tips.com/faqs.cfm?fid=5466 which uses monthly
columns. You should be able to modify that report to fit daily columns.

 

[KARL DEWEY]

You have way too much in there.
Why do you have [Job #], [Job Name], and [Total Of Straight Time]? You said
you are only looking for time. A crosstab query will total.

[All Info Table Query_Crosstab ST].[4/27/2009] AS [All Info Table Query_Crosstab ST_4/27/2009], [All Info Table Query_Crosstab ST].[4/28/2009] AS [All Info Table Query_Crosstab ST_4/28/2009],

Why do you have queries with daily fields? A crosstab will extract the data
into columns of dates.

What are the table structures where you store the actual ST, OT, and DT
entries for the employee on a daily/weekly basis? That is what you need to
use in the crosstab query.

SELECT [All Info Table Query].Employee, [All Info Table Query].Title, [All
Info Table Query].[W/E], [All Info Table Query].Date, [All Info Table
Query].[Job #], [All Info Table Query].[Job Name], [All Info Table
Query_Crosstab ST].[Total Of Straight Time], [All Info Table Query_Crosstab
ST].[4/27/2009] AS [All Info Table Query_Crosstab ST_4/27/2009], [All Info
Table Query_Crosstab ST].[4/28/2009] AS [All Info Table Query_Crosstab
ST_4/28/2009], [All Info Table Query_Crosstab ST].[4/29/2009] AS [All Info
Table Query_Crosstab ST_4/29/2009], [All Info Table Query_Crosstab OT].[Total
Of Overtime], [All Info Table Query_Crosstab OT].[4/27/2009] AS [All Info
Table Query_Crosstab OT_4/27/2009], [All Info Table Query_Crosstab
OT].[4/28/2009] AS [All Info Table Query_Crosstab OT_4/28/2009], [All Info
Table Query_Crosstab OT].[4/29/2009] AS [All Info Table Query_Crosstab
OT_4/29/2009], [All Info Table Query_Crosstab DT].[Total Of Double Time],
[All Info Table Query_Crosstab DT].[4/27/2009] AS [All Info Table
Query_Crosstab DT_4/27/2009], [All Info Table Query_Crosstab DT].[4/28/2009]
AS [All Info Table Query_Crosstab DT_4/28/2009], [All Info Table
Query_Crosstab DT].[4/29/2009] AS [All Info Table Query_Crosstab DT_4/29/2009]
FROM [All Info Table Query_Crosstab DT] INNER JOIN ([All Info Table
Query_Crosstab OT] INNER JOIN ([All Info Table Query_Crosstab ST] INNER JOIN
([All Info Table Query] INNER JOIN [Employee Listing] ON [All Info Table
Query].Employee = [Employee Listing].Employee) ON [All Info Table
Query_Crosstab ST].Employee = [Employee Listing].Employee) ON [All Info Table
Query_Crosstab OT].Employee = [Employee Listing].Employee) ON [All Info Table
Query_Crosstab DT].Employee = [Employee Listing].Employee;

Post the SQL of your crosstab query.