Help calculating the sum of all costs associated with a unique ID

[g3machining]

Hello everyone,

My main form is used for quoting purposes.

The subform I am having trouble with is calculating my labor costs.

I am calculating costs based upon each operation.

All the operations and associated costs are tied together by a unique quote ID.

I want to calculate the sum of all the Labor costs associated with that quote ID.

Thanks in advance!

 

[Ranman256]

Then run a query that sums all costs on the Id.

 

[MarkK]

You have not given enough information.

 

[viennese_finger]

Hi - I have a similar issue - perhaps I could give more details of my problem, see if that helps??

i have a table, which lists number of a specific type of kit is used in a project

tblProjects

• ProjectID
• KitUsed
• Quantity

and I have a separate table, where I store the costs of using each kit

tblKits

• KitID
• KitName
• KitCost

There is a relationship between tblProject.KitUsed and tblKits.KitName

I want to have a form where I can see from the KitUsed what the total project cost will be. the calculation should be number of kits (tblProject.Quantity) * cost of kit (cost of appropriate kit, as entered intotblProjects, but the value comes from tblKits.KitCost).

I have looked at queries and calculated fields but not got the right answer yet...

Any help will be much appreciated - I hope I have given enough detailed info for you to understand this (relatively simple) problem.

Cheers,
Matt

 

[jdraw]

You will probably want to allow for changes in the Cost of a Kit over time. So add a field to Projects--such as AgreedToCostOfKit and store the values of Quantity and Cost in the Project table.
If you rely on the cost from the Kit table, you will have difficulty with Project History information.

There is a subtlety in this that is often overlooked.

KitCost in Kit table represents the current cost of the Kit
AgreedToCostOfKit in the project table is the cost of that Kit when it was assigned to the Project.
Note: AgreedToCostOfKit can also represent a Sale Price/Clearance Price/Loyalty Program Price etc.

You might even want to know the DateOfProject in the Project table?? But we don't have enough info to advise.

 

[viennese_finger]

Thanks for the quick reply - all that sounds great - I am doing a larger design which incorporates dates and lots of other info - I wanted to boil down to this single issue to start with (there are others too). I have another table above projects (tblEnquiries) that I view as a form with tblProjects as a subform. This manages the connection between Enquiry and Project (there may be more than one project for each enquiry and all need quotes/costings).

How would I go about storing the current/agreed KitCost values in the tblProjects table (or would they be stored ina field at the form level), and also calculating the total from current cost and quantity? (I guess that last bit is simple enough...). Is that enough info to advise? If not let me know please.

Cheers again,
Matt

 

[jdraw]

Perhaps it would be helpful if you told us about the "business" involved in the larger design.
My view is that you look at the big picture and build a model. It doesn't have to be detailed, but it should have the major pieces of your "larger design" and the relationships identified. That way, you know where the pieces fit and you won't overlook something(s). You can build an artist's concept as a reference model, then get more detailed/specific with your current effort. And your current effort will be part of the bigger design. It may be a small box or 2 on the big picture, but more detailed in your current effort.

If you have a plan, you choose to implement it according to your needs/priorities.

Data is stored in tables. Forms are much like a window through which you see or capture data.

 

[viennese_finger]

Thanks - I run a biotech research lab where we run contract research Projects on behalf of other labs. The projects have lots of options involved, lots of picking from lists, and other entered data too. We record each project enquiry currently using Excel, use lots of dropdown lists and VLOOKUPs there to calculate overall project costs. We then communicate those costings back to the client and then they either accept or reject the costs and the project either continues or perhaps goes back to the drawing board (fresh enquiry?).

I'm trying to streamline the quoting/costing procedure, by moving the Excel model over to an Access db. The forms on the Access db will form the basis of project costings, and I will probably use wordMerge to email out quotes based on these project costings. Unless Access can do that for me...