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?

 

[dhookom]

It's difficult to suggest a solution without know how your tables are set up. It looks like you have them incorrectly created so that each of the subforms is based on a different table. I would expect a single table for estimating. You would have a single lookup table for costcodes.

I'm not sure why CostCodeID 1 might be either Cranes or Delivery. It should be one or the other.

Duane
Hook'D on Access
MS Access MVP

 

[Hulm1]

Thanks but no that really would not work. Indeed that suggestion seems to counter the whole relational database approach. The table would be huge.

One estimate can have 60 different items for fittings, 8 different items for mobilisation, 12 different items for labour etc. etc.

Concerning costcodeID, there are several things which would have the same cost code. If every item had a different cost code I wouldn't need a cost code for summarising costs into categories at all.

I can see that I did not explain very well. If you still disagree, I can zip up my db if you are interested.

 

[dhookom]

I don't have time to accept a file from you. I thought you might explain or provide your table structures. If you need to total across multiple tables, you might consider a union query.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Faq700-6905 will help you document your tables and relations.

 Indeed that suggestion seems to counter the whole relational database approach.

Really? Without any additional data, Duane appears to be suggesting a normalized structure to your unnormalized structure. Without any further information, there is still nothing to suggest why there is not a single cost code table. Trust me Duane knows a little about normalizing a database. If you doubt that, you may want to check out one of his 10,000 posts. But since you provided originally very little information we can only guess

Not sure your definition of huge (100k, 1M records?). If we are not talking in the 10's of thousands then it is not an an issue.

Post your db, I will take a look. But still go ahead and use the FAQ to document your db structure so others can look at it and provide suggestions.

It is really hard to give any suggestion without understanding the tables, and seeing some notional data. This just hints at a non normalized design
"so I really need to look up the final sales figure exression (rather than do the calculation again in a query)"
If you can not do a query to represent what is on a form, that suggests to me a possible normalization issue. But again we are just guessing until you show more information.

 

[Hulm1]

Ok no problem at all.
I have a main table: tblEstimate. The primary key is EstimateID.
I have four subtables: tblMobilisation, tblInstallation, tblMaterials and tblTravelSubsistence. Each of the four tables listed above has EstimateID as the foreign key. They also each have CostCodeID as well as of course MobilisationItem, MaterialsItem etc.

I have a table zmtCostCodes. CostCodeID is the primary key.
I also have the following tables: zmtMobilisationItem, zmtMaterialsItem.

Forms:
As described in my first post.
Each of the subforms are based on the tables mentioned above.
For subforms Mobilisation and Materials, the MobilisationItem and the MaterialsItem fields are combo boxes where the rowsource is zmtMobilisationItem and zmtMaterialsItem respectively. For these tables, the cost code is fixed.
For Subforms Installation and TravelSubsistence, however, these InstallationItem and the TravelSubsistence items are not Combo boxes, they are entered manually as they could be many things. As such, the Costcode for these needs to be entered manually (or realistically will be based on a combo box whose row source if zmtCostCode).

Does the above make sense to you?

 

[MajP]

Take a look at the FAQ and post your table structure. Either run the code or post as:

tblName
fieldOne (Primary Key, type integer)
fieldTwo (type boolean)
...
fieldN (foriegn key to tableB)

Is there a problem with doing that? A few table names, and how you have them as subforms really does not provide enough information.

 

[MajP]

Or post your db on a file sharing site. I use 4shared.com, but there are many other free ones.

 

[dhookom]

Nothing I have read so far would cause me to break the costs into multiple similar tables.

Duane
Hook'D on Access
MS Access MVP

 

[Hulm1]

MajP
I have uploaded a draft database as suggested (to Fileden). Please note that some of the table don't exactly match the names of the forms nor my post above. I believe it is straightforward enough but if not, let me know and I will revise it. As I have some vba there would obviously be a bit of a knock on effect I would need to sort out if I changed the names now.

http://www.fileden.com/files/2010/2/8/2754006/Quote_Draft2.rar

Thanks

 

[MajP]

Sorry. That link is private, and can not access it.

 

[Hulm1]

Apologies. It was set to Private

http://www.fileden.com/files/2010/2/8/2754006/Quote_Draft2.rar

 

[MajP]

Ok. Here is what you need to do.

You have to Clean up all of your queries and give them some real names. Make these queries the recordsource for the sub forms. If not this is just going to be painful.

Here is the recordsource for your mobilisation subform. Really hard to read. Give real names to every expression.

SELECT 
 Estimate_Mobilisation.*, 
 zmtCostCode.CostCode, 
 [rate]*[quantity] AS Expr1, 
 [Expr1]/(1-[Margin])-[expr1] AS Expr2, 
 [Expr2]/[Expr1] AS Expr3, [Expr4]/[quantity] AS Expr5,   
 [Expr1]+[Expr2] AS Expr4
FROM Estimate_Mobilisation 
 LEFT JOIN (zmtMob_Demob LEFT JOIN zmtCostCode ON zmtMob_Demob.CostCodeID = zmtCostCode.CostCodeID) ON Estimate_Mobilisation.MobDemobID = zmtMob_Demob.MobDemobID;

Now create a readable query for your subforms

qrySubFormMobilization

SELECT 
 Estimate_Mobilisation.EstimateID, 
 zmtCostCode.CostCode, 
 [rate]*[quantity] AS TotalCost, 
 [TotalCost]/(1-[Margin])-[TotalCost] AS MarginValue, 
 [MarginValue]/[TotalCost] AS MarkUp, 
 [SalePrice]/[quantity] AS UnitSale, 
 [TotalCost]+[MarkUp] AS SalesPrice
FROM Estimate_Mobilisation 
LEFT JOIN 
 (zmtMob_Demob LEFT JOIN zmtCostCode ON zmtMob_Demob.CostCodeID = zmtCostCode.CostCodeID) ON Estimate_Mobilisation.MobDemobID = zmtMob_Demob.MobDemobID;

If you do this. Then on the subform in the control for "Total Cost" you bind it to "TotalCost" not some nebulous "Expr1"

Do this for every query on your subforms. And be very consistent in your naming conventions. So all the like calculated fields have the same name in each table. "TotalCost", "SalesPrice", etc.

I then would have queries for each subform.
qrySubFormMobilisation
qrySubFormPlantRental
qrySubFormPlantSale
...
qrySubFormDemobillisation.

now a Simple union query

qryAllCosts
Select
estimateID
costCode
TotalCost
salesPrice
From
qrySubFormMobilisation
Union Select
estimateID
costCode
TotalCost
salesPrice
From
qrySubFormPlantSale
...
Union Select
estimateID
costCode
TotalCost
salesPrice
from
qrySubFormDemobiliastion


Now a simple aggregate query on "qryAllCosts" to return the sum of TotalCost, sales price for each estimate by each cost code.

If you make all your names consistent in each query this becomes very simple.

 

[Hulm1]

You put some real time into that. Thankyou very much. I will work on this tonight. Do you believe my structure with several tables is correct? I have been a bit thrown by Dhookom's comments. I was sure that my approach was correct.

 

[MajP]

I think you are fine with the way you have it, because the other way would require some changes that may make it not worth the effort. However, you will want to become very conversant in Union queries to bring data from these tables back together.

If I look at an estimate item they all have these fields in common

   EstimateID  
   CostCodeID   
   Rate   
   Quantity
   Margin
   and Implicitly a Type of Estimate (Mobilisation, Demob..)
   
   but there are some additional fields in certain tables
   rental period
   discount

So if I was designing this from scratch I would have looked at a single Estimate Item table, but then some means to handle those fields unique for a specific estimate type. I could put in my generic estimate item table the fields rental period and discount, and if these did not apply the value would be zero. I would have to weigh the pros and cons of either design.

Pros
queries would be much simpler especially summary
required to maintain a single estimate table
Cons
Need a strategy to handle fields unique to a specific estimate type
Requires you to put a fk for each type of table. So a generic estimate item would have a fk field for each zmt table. It would work, but be pretty ugly. It is considered a normalized design, but looking at it you would not think so.

So keep it like you have it and normalize with union queries.

 

[Hulm1]

Ummm. I understand where you and Dhookom are coming from on this. Perhaps I will restructure. It's easy enough to do.

As for "Non relevant" fields, I guess I could simply default to "0" or "1" or, in the case of the currency, "£"

Actually I am pretty tempted to follow that route. Ugly but simpler. It's the union queries that put me off!!! Though I suppose I should learn them

 

[MajP]

The union queries are very simple and flexible, and I would not sweat them. Making names consistent will make this easier, but not even required. You can combine any two columns together to include two different calculated values or two fields with completely different names.

All you really have to do is the one Union I described and that will put all estimate items into a single query. Then you can run many queries off of that one query.

However, giving your sub form queries readable names will help a lot.

Before going down the other route, I would think about it. It does get tricky to have a generic estimate item that relates to different tables depending on the "type" of estimate item.

 

[MajP]

I went back and read my original post and it was not that clear. So this is what I would do.

For each subform
1) Go to the query editor for the subform's recordsource
2) Where ever you see something like
Expr1: [rate]*[quantity]
change to
TotalPrice:[rate]*[quantity]

to alias your fields with good names. This includes aliases within the calculation
Expr2: [Expr1]/(1-[Margin])-[expr1]
3)Once done changing the aliases you can switch to "SQL View" and you can now copy the new sql string.
4) Paste that sql string into a new query
5) Give the new query a good name
6) Use the new query as the recordsource for you subform
7) Bind your controls to the new aliased fields

Then use your new subform queries as the queries in your large union query.

 

[Hulm1]

After further consideration I think that my current structure is one I am happier with. It also better reflects that I could have labour charged for different job aspects (and helps analyse that later maybe).
Thanks so much for all your help. This is really great.
I was playing with the queries when your latest post came up so the extra clarity helps here. I'll let you know how I am getting on.

 

[Hulm1]

Thank you MajP that was really helpful and resolved my issues. The Union queries are, as you say pretty straightforward.
Quick question. For the report of the Estimate. Would you use the same calculations all over again the report (with subreports based on the subformqueries or call up the results of the form calculation?