calculating total job costs.

[Homegrownandy]

every job I have in the database has additional costs as a child record. There are multiple additional costs for each job.



Our clients want a breakdown of what these are (this is done) but also the total cost for each job.

EG:
Job 1 = x £300, Y £400

the client wants to see

Job 1 = £700

so.. I cant get my head around what I need to do in the query to combine like this and list all the jobs and totals.

The naming I have for any table/query doesn't matter, neither does my business plan or my relationships, before people start asking for them haha. This is a theory question so the answer should be applicable to any database.

relations between child and parent records and how to manipulate child record costs into a total. that's all I want if someone could point me in the right direction.

Thanks.

[ItsMe]

I would probably create an alias in a query that sums the additional costs. Then, add the additional costs to the original cost.

[Homegrownandy]

It doesn't need to be added to the original cost, they are paid no problems up front. but thanks for the tip I will research your suggestion now.

[ItsMe]

A totals query may provide the results you desire. You can create a totals query by clicking the Sigma symbol from within the query designer. When you add fields to your query, you can Sum a specific field by changing "Group By" to "Sum".

[Homegrownandy]

Yes, I actually found that whilst looking up alias querys, It will help a few different projects I have on at the moment. I have another question..

http://prntscr.com/8yydc2 <--query
http://prntscr.com/8yybav <-- Results

is there a way to concatenate text strings from the child records?

EG

job 1 = "description 1", "description 2"

that way they still get all the information but its in one text block with one price. My brain isn't working today and I cant figure out where to even begin.

[Homegrownandy]

http://allenbrowne.com/func-concat.html

Think this is what I'm after, Ill give it a go.

[ItsMe]

That should do it. Consider the example provided by Allen where he uses CompanyID. If your query retrieves duplicate values of CompanyID, I would not call the function from the query. Instead, call the function from a control. If you are using a report and calling the function from a particular control within a Report does not work, you can use VBA in the Report's On Load and On Format events.

From Allen's site ...

For the example above, you could set the ControlSource of a text box to:
     =ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])

[Homegrownandy]

Okay thanks mate, I do have this working now. But as always it needs to be more complex.

the child records here all have a "type". I wish to not include one of these types... lets call it "type 1"

I can do this by putting in the field in the query design and adding the criteria:

not type 1

But in doing so It ruins the previous work of combining these records and splits them back up haha.

a work around could be 2 querys.... one to not include "type 1" then use that as a record source. But I was wondering if you think its possible in one query.

[ItsMe]

Sounds like you need two queries. If an element of the Sum includes records with a type value equal to 1, then you will need to sum on a separate query.

[Homegrownandy]

That's what I thought, Thanks for that