RE: Form For Crosstab Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 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?
.

Relevant Pages

  • RE: VBScript Problem with Access Query (Unspecified error)
    ... some records from the multiple tables using Inner Join. ... absolutely fine when i execute the query in MS access 2003. ... Set conn = CreateObject ... JOIN AllEmployees ON SenderToDom.Sender = AllEmployees.Email) INNER JOIN ...
    (microsoft.public.scripting.vbscript)
  • RE: Form For Crosstab Query
    ... I had planned for the command button to execute an Update query. ... regular form could work, but if we add a new project manager, the form would ... FROM (TimeRecords INNER JOIN Staff ON TimeRecords.StaffID = Staff.StaffID) ...
    (microsoft.public.access.forms)
  • Re: Report Queries
    ... those tables, they will turn up on multiple rows of the query, and so ... you want to group your report by some fields. ... FROM ((TPhysicians INNER JOIN Attendees ON TPhysicians.TPhysicians = ...
    (microsoft.public.access.queries)
  • Query duplicates results due to many-to-many relationships
    ... I have a query with fields from different tables. ... the record multiple times. ... FROM TblShift INNER JOIN (TblTypeofForce INNER JOIN ((TblNationality INNER ... INNER JOIN TblIncident ON TblCity.CityID = TblIncident.CityID) INNER JOIN ...
    (microsoft.public.access.gettingstarted)
  • 2 Form Questions for the Group...
    ... Is there a command that will allow me to match the ... conditions of one field to that of multiple fields or values? ... In other words, using this command, I could eliminate the need for multiple ... Within the query, ...
    (microsoft.public.access.queries)