RE: Form For Crosstab Query

Sounds like you want to update multiple records in the table that are
rolled-up in the form.
A command button could run an update query if your form include enough
information to identify the records needed to be updated. The textboxes for
the record identification need not be visible.

"Sprinks" wrote:

Karl,

Thank you very much. I've new to crosstab queries, and have been trying to
figure this out for weeks.

My next step is to place the query on a continuour form, and add a command
button for each row that allows the principal to change the status of the
records being summarized from 2 (confirmed, but not billed) to 3 (billed). A
regular form could work, but if we add a new project manager, the form would
have to be edited, while a pivot table view does not show a command button.
My tentative solution would be to create a form "on-the-fly" for the columns
generated by the current crosstab query, but I have no idea how to go about
that.

Can you steer me in the right direction?

Thank you.

Sprinks

"KARL DEWEY" wrote:

Add the Billing rate to the Staff table. Here is the edited SQL.

TRANSFORM Sum(TimeRecords.Hours) AS SumOfHours
SELECT TimeRecords.ProjectNumber, Projects.Project, Sum(([Hours]*[Billing]))
AS [Billing Total]
FROM (TimeRecords INNER JOIN Staff ON TimeRecords.StaffID = Staff.StaffID)
INNER JOIN Projects ON TimeRecords.ProjectNumber = Projects.ProjectNumber
WHERE (((TimeRecords.Status)=2) AND ((Len([TimeRecords].[ProjectNumber]))>1))
GROUP BY TimeRecords.ProjectNumber, Projects.Project
PIVOT Staff.Initials;


"Sprinks" wrote:

For a time*** application, I have the following query which crosstabulates
the hours worked by each project manager by project:

TRANSFORM Sum(TimeRecords.Hours) AS SumOfHours
SELECT TimeRecords.ProjectNumber, Projects.Project
FROM (TimeRecords INNER JOIN Staff ON TimeRecords.StaffID = Staff.StaffID)
INNER JOIN Projects ON TimeRecords.ProjectNumber = Projects.ProjectNumber
WHERE (((TimeRecords.Status)=2) AND ((Len([TimeRecords].[ProjectNumber]))>1))
GROUP BY TimeRecords.ProjectNumber, Projects.Project
PIVOT Staff.Initials;

This currently displays something like:

ProjectNumber Project CD FF JF KS
SF
-----------------------------------------------------------------------------------
06123 Hershey Cancer Center 5 18
06125 Museum of Art 15 15 20
06351 Free Library
25

I would like to add an additional column that sums the multiple of each
project manager's hours multiplied by their Billing Rate. I do not want to
hardcode either their rate nor our current array of project managers.

I thought a Pivot Table form would be able to handle it, but it seems cludgy
and doesn't seem to permit what I'm after. Does anyone have any ideas?
.

Loading