S
Sprinks
I’ve created the crosstab query below which calculates hours worked by
project and project manager since the last invoicing. I wish to provide a
form, to be used by the firm’s principal containing this data AND a total
dollar value for each project, which sums all
[TotalsHoursWorkedByAGivenProjectManager] * [HisBillingRate]. For example,
with CD’s billing rate at $150/HR, Project 04051 below should show $21,000.
The principal will decide what to bill, which may be equal to the above
value, or less depending on how we quoted the project. He will record the
amount to be billed and press a button, which will change the Status field of
all related records in the TimeRecords table, and insert a record in the
Invoice table that contains the ProjectNumber, InvoiceAmount, and the Date.
A report will be printed or an email sent to the OfficeManager who will then
create the invoice in QuickBooks.
I can’t figure out how to:
- get this TotalDollar amount on the Crosstab form (created by the Crosstab
form wizard)
- use the form created by the Crosstab wizard. The expand/contract buttons
are confusing for my purpose, and the Grand Total column doesn’t really show
a Grand Total, but simply all the entries for that row.
I’d like to use a regular form, but can’t figure out how to make it flexible
so that it doesn’t have to be modified each time we hire someone.
Can anyone help?
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;
Crosstab
Number Project CD FF JF KS
04051 Canaan Valley Institute 140
05133 Gilder House 43
05136 Abington Presbyterian 96
05138 Loyola Dorm 20 4
05139 Children's Zoo 30 17
05140 Artesian Offices 22
project and project manager since the last invoicing. I wish to provide a
form, to be used by the firm’s principal containing this data AND a total
dollar value for each project, which sums all
[TotalsHoursWorkedByAGivenProjectManager] * [HisBillingRate]. For example,
with CD’s billing rate at $150/HR, Project 04051 below should show $21,000.
The principal will decide what to bill, which may be equal to the above
value, or less depending on how we quoted the project. He will record the
amount to be billed and press a button, which will change the Status field of
all related records in the TimeRecords table, and insert a record in the
Invoice table that contains the ProjectNumber, InvoiceAmount, and the Date.
A report will be printed or an email sent to the OfficeManager who will then
create the invoice in QuickBooks.
I can’t figure out how to:
- get this TotalDollar amount on the Crosstab form (created by the Crosstab
form wizard)
- use the form created by the Crosstab wizard. The expand/contract buttons
are confusing for my purpose, and the Grand Total column doesn’t really show
a Grand Total, but simply all the entries for that row.
I’d like to use a regular form, but can’t figure out how to make it flexible
so that it doesn’t have to be modified each time we hire someone.
Can anyone help?
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;
Crosstab
Number Project CD FF JF KS
04051 Canaan Valley Institute 140
05133 Gilder House 43
05136 Abington Presbyterian 96
05138 Loyola Dorm 20 4
05139 Children's Zoo 30 17
05140 Artesian Offices 22