Form Design Help (Pivot Table)

[linkaccess]

I have a table that lists employees, the date and their daily status.

tblEmployeeDailyStatus
DailStatusID (PK)
DailyStatusDate
Employee
DailyStatusCode (DSC)

I would like to create a form that lists the schedule in this format.

01/01/2013 01/02/2013 01/03/2013 01/04/2013 01/05/2013 etc.

Employee 1 DSC1 DSC2 DSC3 DSC1 DSC2
Employee 2 DSC1 DSC2 DSC3 DSC1 DSC2
Employee 3 DSC2 DSC1 DSC3 DSC1 DSC3
Employee 4 DSC3 DSC1 DSC3 DSC1 DSC3
etc.

I would like for managers to look at the schedule and be able to change the DSC for any employee on any given date (via drop down).

Any suggestions?

 

[KenHigg]

That looks like a bad idea because the days will run on off the right side... ?

 

[linkaccess]

I'll control the date range to include the current date + 30 days. The user should be able to scroll across the available dates.

 

[CJ_London]

I think the maximum no of columns you can have is twenty in a pivot (for use in a form) - I could be wrong but be aware.

The way to do this is use a cross tab query as the record source for the form, but you have to predefine the headings which are done by right clicking on the builder window, select properties and complete column headings - or in SQL it looks like this, highlighted in red.

TRANSFORM Sum(Table2.Percentage) AS SumOfPercentage
SELECT Table2.SLSalesManName, Table2.Description
FROM Table2
GROUP BY Table2.SLSalesManName, Table2.Description
[COLOR=red]PIVOT Table2.PriceLevels In ("WH","RET","CONS","WH/LESS");[/COLOR]

If the user is selecting a date range or based on today, you will need to build the sql in VBA based on the starting point.

However you also want to change the data, and this is not possible because the query uses group by and is not updateable.

So what you actually need to do is create a make table query using the crosstab as it's source to create a temporary table and have the form based on the temporary table (form will need to be closed whilst you are making the table).

And when the data is updated, have a vba routine to update your original source table.

It is doable, but quite complex and not very efficient (although I understand what you are trying to achieve) so you might want to consider doing things in a different way.