Form For Crosstab Query

[Guest]

For a timesheet 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?

 

[Guest]

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;

 

[Guest]

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

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;

For a timesheet 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?

 

[Guest]

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.

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

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;

For a timesheet 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?

 

[Guest]

Karl,

Thank you for your response.

Yes, I had planned for the command button to execute an Update query. My
problem is rather with the form itself. If I create a continuous form based
on the query you provided, the initials of each current project manager who
had hours in the period would be "hard-coded" into the form, forcing a form
change if we (or they, should I ever leave the company) hire a new PM. I'd
rather the form just *worked*.

The pivot table view doesn't allow a command button, which led me to the
idea of using code to *create* the form with code on-the-fly, based on the
results of the query.

Can you help me with this?

Thank you.

Sprinks

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.

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

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;


:

For a timesheet 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?

 

[Guest]

Sorry, I can not help with code.

Karl,

Thank you for your response.

Yes, I had planned for the command button to execute an Update query. My
problem is rather with the form itself. If I create a continuous form based
on the query you provided, the initials of each current project manager who
had hours in the period would be "hard-coded" into the form, forcing a form
change if we (or they, should I ever leave the company) hire a new PM. I'd
rather the form just *worked*.

The pivot table view doesn't allow a command button, which led me to the
idea of using code to *create* the form with code on-the-fly, based on the
results of the query.

Can you help me with this?

Thank you.

Sprinks

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.

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

:

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;


:

For a timesheet 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?

 

[Guest]

OK. Thanks for getting back to me, and especially for providing the query;
it was the key to making this work.

Sprinks

Sorry, I can not help with code.

Karl,

Thank you for your response.

Yes, I had planned for the command button to execute an Update query. My
problem is rather with the form itself. If I create a continuous form based
on the query you provided, the initials of each current project manager who
had hours in the period would be "hard-coded" into the form, forcing a form
change if we (or they, should I ever leave the company) hire a new PM. I'd
rather the form just *worked*.

The pivot table view doesn't allow a command button, which led me to the
idea of using code to *create* the form with code on-the-fly, based on the
results of the query.

Can you help me with this?

Thank you.

Sprinks

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.

:

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

:

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;


:

For a timesheet 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?

 

[Guest]

I'm confused. Isn't the crosstab query based on a query that is based on
your tables? Why wouldn't this update your crosstab query?

jaybird

OK. Thanks for getting back to me, and especially for providing the query;
it was the key to making this work.

Sprinks

Sorry, I can not help with code.

Karl,

Thank you for your response.

Yes, I had planned for the command button to execute an Update query. My
problem is rather with the form itself. If I create a continuous form based
on the query you provided, the initials of each current project manager who
had hours in the period would be "hard-coded" into the form, forcing a form
change if we (or they, should I ever leave the company) hire a new PM. I'd
rather the form just *worked*.

The pivot table view doesn't allow a command button, which led me to the
idea of using code to *create* the form with code on-the-fly, based on the
results of the query.

Can you help me with this?

Thank you.

Sprinks


:

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.

:

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

:

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;


:

For a timesheet 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?

 

[Guest]

Ah, my ignorance is once again on display... I'm looking for a way around
this problem myself.

Jaybird

I'm confused. Isn't the crosstab query based on a query that is based on
your tables? Why wouldn't this update your crosstab query?

jaybird

OK. Thanks for getting back to me, and especially for providing the query;
it was the key to making this work.

Sprinks

Sorry, I can not help with code.

:

Karl,

Thank you for your response.

Yes, I had planned for the command button to execute an Update query. My
problem is rather with the form itself. If I create a continuous form based
on the query you provided, the initials of each current project manager who
had hours in the period would be "hard-coded" into the form, forcing a form
change if we (or they, should I ever leave the company) hire a new PM. I'd
rather the form just *worked*.

The pivot table view doesn't allow a command button, which led me to the
idea of using code to *create* the form with code on-the-fly, based on the
results of the query.

Can you help me with this?

Thank you.

Sprinks


:

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.

:

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

:

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;


:

For a timesheet 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?

 

[Guest]

Jaybird,

See my reply to your other post on this topic.

Sprinks

Ah, my ignorance is once again on display... I'm looking for a way around
this problem myself.

Jaybird

I'm confused. Isn't the crosstab query based on a query that is based on
your tables? Why wouldn't this update your crosstab query?

jaybird

OK. Thanks for getting back to me, and especially for providing the query;
it was the key to making this work.

Sprinks

:

Sorry, I can not help with code.

:

Karl,

Thank you for your response.

Yes, I had planned for the command button to execute an Update query. My
problem is rather with the form itself. If I create a continuous form based
on the query you provided, the initials of each current project manager who
had hours in the period would be "hard-coded" into the form, forcing a form
change if we (or they, should I ever leave the company) hire a new PM. I'd
rather the form just *worked*.

The pivot table view doesn't allow a command button, which led me to the
idea of using code to *create* the form with code on-the-fly, based on the
results of the query.

Can you help me with this?

Thank you.

Sprinks


:

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.

:

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

:

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;


:

For a timesheet 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?