Calculations in a subform from another table.

[ThePixelMines]

I'm preparing a database to manage billing and assets for various projects and clients. Specifically, I'm trying to calculate billing charges in a timecard subform.

Right now I have a subform that details the project, hrs/qty of service, and a serviceID that links it to a another table that has different charges for different services. See the relationships here. For instance:

3D modeling/animation is $150/hr
Spot Tag is $25/unit
Graphic composition is $75/hr
etc.

The BillingRate is found in the Services table. What I need is a total amount text field calculated per line in TimeCardHours based on ServiceID and BillableHours entered. I can't seem to get it to work.

I tried a simple calculation but I can't get the BillingRate over to my TimeCardHours subform properly to do the calculation.

Any ideas?

Check it, Fool!

http://www.thepixelmine.com

 

[Remou]

Why not base the form on a query that includes both tables and performs the calculation? If this does not suit, how about DlookUp?

 

[ThePixelMines]

I'm sorry...I should have explained just how new I am at this.

I've eventually figured out that I had to get my calculations done in a query that the form or subform referred to. This is what I've been able to concoct so far:

SELECT TimeCardHours.ProjectID, TimeCardHours.DateWorked, TimeCardHours.BillableHours, TimeCardHours.WorkDescription, TimeCardHours.ServiceID, Services.BillingRate, [BillableHours]*[BillingRate] AS [Billing Amount]
FROM Services INNER JOIN TimeCardHours ON Services.ServiceID = TimeCardHours.ServiceID;

I went on to create another query that was able to (from the previous query) also give me Total Charges that I dropped in as a subform:

SELECT [Service Rate Query1].ProjectID, Sum([Service Rate Query1].[Billing Amount]) AS [Total Charges]
FROM [Service Rate Query1]
GROUP BY [Service Rate Query1].ProjectID;

I'm still trying to grasp some of the basics. I picked up Access about a week ago...so...it's slow going. As far as Dlookup, Visual Basic is a ways off yet.

Thanks for the clues, Remou.

Check it, Fool!

http://www.thepixelmine.com