Grouping costs from multiple subforms in an Estimate 1

[Hulm1]

I have a form (estimate) which has 4 subforms. And each of those subforms is a continuous form (say Mobilisation, Installation, Materials and Travel) and items which may be entered on each of the subforms potentially has different cost codes (using CostcodeID).
Example: Subform "Mobilisation" may have Cranes (costcodeID 1), Delivery (costcodeID 1), Labour (costcode ID 4) and Subcontractors (costcode 5). Meanwhile subform "Installation" may also have labour (costcodeID 4) and subcontractors (costcodeID 5).

For each estimate I would like a summary of all costs for each CostCodeID. I would also like a summary of the Sales price for each CostCodeID. Unfortunately, the calculations to get the sales price for each subform are different as some are rentals (and therefore need number of months) and some have exchange rate calculations, so I really need to look up the final sales figure exression (rather than do the calculation again in a query).

The forms all work well. But extracting that information is a bit beyond me.
Help?

 

[MajP]

The reason I suggested building subform queries external to the subform was so that you could reuse them over again. You can use those queries as the recordsource for subreports along with the union query. So I would use the queries as your recordsource.

 

[Hulm1]

Yes thanks. Sorry, I should posted a second note to say that I realised shortly after that it was a stupid question!

Thanks again

 

[MajP]

Advising you to rename your expressions and modify the subform queries, was only to make reuse and further development easier.
Your original question

For each estimate I would like a summary of all costs for each CostCodeID. I would also like a summary of the Sales price for each CostCodeID.

Could all have been done in a single union query without modifying anything else. It just would have been a lot less clear, and provided less reuse and modularization.

 

[Hulm1]

I problem has arisen with the union query. Some of the subforms (2 of them) include an extra expression: MonthlyPrice. By necessity this is the same as SalesPrice on the other forms (TotalCost + Margin Value). SalesPrice on these two forms however, is MonthlyPrice=(totalcost + margin value) multiplied by the rental period on the main form estimate.

When I run the union query, it looks for the rental period of course. Can you advise how I might deal with that?
thank you

 

[Hulm1]

I think I may know the answer to my last question. Perhaps in all the qrySubforms SalesPrice remains the same. For the two rental ones should have a TotalSalesPrice which is SalesPrice x Rental Period?

On the other hand the union query based on cost codes would then not include those TotalSalesPrice for the rental subforms and therefore the Aggregate cost for the codes would not work. ?

 

[MajP]

In a union query you can combine any fields as long as the same data type.

select
costCodeID,
TotalCost + MarginValue as TotalPrice
From
qryA
Union
Select
costCodeID,
(TotalCost + MarginValue) * RentalPeriod as TotalPrice
From
qryB

You can also put in literals if you needed more fields from one table than another
Assume you want to show the rental period column, but not all queries have a rental period. And you want a costType

select
costCodeID,
TotalCost + MarginValue as TotalPrice
Null as RentalPeriod,
"Mobilisation" as costType
From
qryA
Union
Select
costCodeID,
(TotalCost + MarginValue) * RentalPeriod as TotalPrice,
RentalPeriod,
"Building Rental" as costType
From
qryB

 

[Hulm1]

Ok this looks like something I can play with. Especially as for the fittings I may have 50. I don't want all 50 listed so I need to group those under types with a Subtotal for each(lets say a, b, c, and d). I can do that easily with an aggregate query, putting in null as values for missing [Item] and [CostCode].

With regard to the RentalPeriod problem however, I just modified the qryAllCosts as follows:

Select
[estimateID], [costCodeID], [Item], [TotalCost], Null as [MonthlyPrice], [salesPrice]
From
qrySubFormMobilisation;

UNION SELECT
[estimateID], [costCodeID], [Item], [TotalCost], [MonthlyPrice], [salesPrice]
From
qrySubFormRental;
etc.

However, it is still looking for the rental period which enables the subformRental to calculate the SalesPrice. Trouble is, this is on the Main Form: Estimate.

How can I refer to that form? Continued thanks for your help.

 

[MajP]

can you post the sql for "qrySubFormRental"?

 

[Hulm1]

Yup here it it

SELECT Estimate_Plant_Rental.*, zmtCostCode.CostCode, zmtUnitType.UnitType, zmtCurrency.CurrencyName, ([rate]-([rate]*[discount]))/[currencyvalue] AS DiscountRate, [DiscountRate]*[quantity] AS TotalCost, [TotalCost]/(1-[Margin])-[TotalCost] AS MarginValue, [MarginValue]/[TotalCost] AS Markup, [TotalCost]+[MarginValue] AS MonthlyPrice, [MonthlyPrice]/[Quantity] AS UnitRate, [Monthlyprice]*forms!Estimate.rentalperiod AS SalesPrice
FROM ((Estimate_Plant_Rental LEFT JOIN zmtCostCode ON Estimate_Plant_Rental.CostCodeID = zmtCostCode.CostCodeID) LEFT JOIN zmtUnitType ON Estimate_Plant_Rental.UnitTypeID = zmtUnitType.UnitTypeID) INNER JOIN zmtCurrency ON Estimate_Plant_Rental.CurrencyID = zmtCurrency.CurrencyID;

 

[MajP]

Your subform query should inlcude a final inner join to join to your Estimate table. Then your query would have

select
Estimate.RentalPeriod
....
[Monthlyprice]*[Estimate.RentalPeriod]

No need to reference the form.

 

[Hulm1]

Did that but when I run the query it asks for the RentalPeriod. This is what I did. Obviously I dun something wrong!

SELECT Estimate_Plant_Rental.*, Estimate.RentalPeriod, zmtCostCode.CostCode, zmtUnitType.UnitType, zmtCurrency.CurrencyName, ([rate]-([rate]*[discount]))/[currencyvalue] AS DiscountRate, [DiscountRate]*[quantity] AS TotalCost, [TotalCost]/(1-[Margin])-[TotalCost] AS MarginValue, [MarginValue]/[TotalCost] AS Markup, [TotalCost]+[MarginValue] AS MonthlyPrice, [MonthlyPrice]/[Quantity] AS UnitRate, [Monthlyprice]*forms!Estimate.rentalperiod AS SalesPrice
FROM (((Estimate_Plant_Rental LEFT JOIN zmtCostCode ON Estimate_Plant_Rental.CostCodeID = zmtCostCode.CostCodeID) LEFT JOIN zmtUnitType ON Estimate_Plant_Rental.UnitTypeID = zmtUnitType.UnitTypeID) INNER JOIN zmtCurrency ON Estimate_Plant_Rental.CurrencyID = zmtCurrency.CurrencyID) INNER JOIN Estimate ON Estimate_Plant_Rental.EstimateID = Estimate.EstimateID;

 

[MajP]

I was not clear

Take this out of the query. (You are already linked to the Estimate table so pull the rental period from there, not the form.)
[Monthlyprice]*forms!Estimate.rentalperiod AS SalesPrice

Replace this with
[Monthlyprice]*[Estimate.RentalPeriod] AS SalesPrice

I am referring to the table Estimate.



Never have a form, table, query with the same name.
Use something like
frmEstimate
tblEstimate

 

[Hulm1]

Ye Ha! Thanks. Actually I wasn't already linked to tblEstimate. But when I did, that worked great.

Pushing my luck now because I already owe you several crates of beer: Some of these price items will be rate only. So, I don't really want them to be totalled up. Just the Unit Rate shown.
I had thought I would add a field RateOnly Y/N in each table. Then I thought I would have an IF statement in the form which says that if RateOnly= True then SalesPrice = 0. I know I'm trying to do stuff way above my capability but I'm learning loads here! I'll understand though if you reckon I've had more than my due here.
Q

 

[MajP]

Which table would have the RateY/N field? Is it for certain costcodes and not others?

However if in query if you had that field

select
iif([RateOnly],0,[TotalCost] + [MarginValue]) as TotalPrice

This says
if rateOnly = true then TotalPrice = 0
else
TotalPrice = TotalCost + MarginValue


Using conditional formatting on a form or report you could hide SalesPrice if it equals zero.

 

[Hulm1]

Brilliant thanks. It applies to all fields really. Sometimes the client wants a rate only in case the quantity is variable. That will do it though!

 

[Hulm1]

I modified this slightly as follows in order to avoid the need for a RateOnly field.

IIf([Quantity]=0,"",[SalesPrice]/[quantity]) AS UnitPrice

The same for Markup. The reason is that it removes the "Error".
SalesPrice comes out as "0" anyway if quantity is "0"

I was then thinking I would put a label over the SalesPrice Field with "R/O" in it (for Rate Only). I thougt I would do the following:

If[Quantity]=0, then
me.salesprice.visible = false
else
me.salesprice.visible = true

If[Quantity]=0, then
me.Label.visible = True
else
me.Label.visible = False

End if
End if

This feels like a clumsy approach. As for conditional formatting, I can only see a way to use this for bold or colour etc.

 

[Hulm1]

Something just cropped up. Where Quantity >=1, Markup and UnitPrice are calculated as they should be, the formatting goes. I have them set for Currency, 2 decimal points but this seems to be overidden. This seems odd?

 

[dhookom]

You should not use an expression that might return a string or a number:

 IIf([Quantity]=0,"",[SalesPrice]/[quantity]) AS UnitPrice

Don't return a zero-length-string. Replace the "" with either 0 or Null.

 IIf([Quantity]=0,Null,[SalesPrice]/[quantity]) AS UnitPrice

or

 IIf([Quantity]=0,0,[SalesPrice]/[quantity]) AS UnitPrice

Duane
Hook'D on Access
MS Access MVP

 

[Hulm1]

I should have known that.
Thank you