Multiple Columns / Crosstab

  • Thread starter Thread starter neirhart
  • Start date Start date
N

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.
 
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;
 
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],
Click to expand...
Click to expand...
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.

neirhart said:
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;


KARL DEWEY said:
Post the SQL of your crosstab query.
Click to expand...
Click to expand...
 
Back
Top