Schema that facilitates the calculation of task breakdowns dynamically (1 Viewer)

[timeto]

I build all kinds of stuff and I need to calculate the quantity of each task to charge my clients. Some tasks are measured by volume units, such as m3 (cubic meter, three dimensions multipled), others are measured by m2, others by kilogram (dimension x unit weight), others are simple, just a piece, and there are others, I remember having used 7, 8 different values. This is done in Excel quite easily, but how would I do this in SQL? Here's an example dataset:

For the terrain fill, I want to charge the filling that goes from A to C x-axis, and from 1 to 25 y-axis, "a" is length, "b" is width, "c" is depth, "reps" is repetitions of those measurements, "formula_id" is my hallucination, I want to enter there something like a*b*c*reps as text and I'm hoping that to be calculated after being evaluated somehow. This is my schema:

As you can see, this is likely not the best approach, it's hacky. However, my users, especially me, we loved doing this in Excel, but I can't wrap my head around this concept when I think of the form design. You see, "a, b, c" are variable, sometimes "a" could be length, sometimes it could be width, others it could be depth, it depends on the type of task. For example:

If I have to quantify the volume of a wall, I'd use "length", "width" and "thickness"
If I want to quantify excavation, I'd use "length", "width" and "depth"

My headers should vary, it's getting pretty complicated at this point. I need some guidance. I'm attaching the sample.

 

[timeto]

re-reading this post made me realize I'm being very vague about some things

I remember having used 7, 8 different values

What I mean by this is that my current idea of a schema only contemplates the hardcoded a, b, c and reps. I may need more.

My headers should vary

My header names within the form. That's what I mean, so I also need to account for this particular stuff. For instance, if I'm calculating a brick wall, I will probably need only Length and Height, but if in my table of breakdowns, I have a and b, how do I assign a header to each? that's what I was trying to convey. I don't know how to design something that helps me also assign the headers easily when I open the form.

 

[CJ_London]

would think you would want width as well - or some other definition such a single skin, 9" solid, 4" cavity etc.

so either assign a default value of 1 and disable (perhaps using conditional formatting) and/or lock if required the fields you don't need or you need code to assign a caption to the label.

I have an app used for estimating build projects. User uploads a scale drawing displayed in an image control. The set the scale and can then draw shapes (such as a rectangle for the wall on the drawing). They then draw another rectangle on the wall for a window. The user specifies the type of wall (the definition of which is in a separate table which has the quantity of each component required for a square metre) and the app calculates what is required allowing for the size of the window. For info, the window also has a type and includes things like cavity closers, type of window/window sill, lintel, etc.

 

[GaP42]

As things stand, it appears that you do not intend for the record of a task for a customer to persist - the idea looks to be when a request is made, you select the task, without referencing who made the request, and based on the associated "formula" certain values are entered that allow a calculation of the quantity to be made and perhaps the customer charge. Without some form of customer registration then the entered values for task and used quantity will just hang around / or periodically be removed (deleted).

That method of operation is not what a database would be used for,

Header names (as captions within a form) can be changed as you move from one record to another. However if you expect the column names to change as you move from record to record in a table (as shown in post #1) then that is a no go.

Considering Tasks - your current conception shows them as simple items like a material (steel bar/ light bulbs etc). It is important in your db design to accurately name and describe what each data collection and item is, to know its job within the db. You might find it easier to write the narrative of what happens and how you will use the db - assuming you need the data to persist and relate to customers/ quote-order / component - material. Materials can then have dimensions/quantity and charged amount which references a basecharge list, and quantitytype

 

[gemma-the-husky]

How is it done in excel quite easily, out of interest? I would have thought that anything you can express in excel can be expressed in access or vba, just as easily.

Can you provide an example?

 

[The_Doc_Man]

You appear to be worried about different units, but there is one and only one unit that counts, and I didn't see it explicitly included in the discussion. If this is a tool to help you compute charges on a work project, then the unit you REALLY need is charge per unit-type.

One way I've seen this done is that you have a list of units that occur in your work. Perhaps like a combo box that lists m3, m2, kilograms, labor hours, etc. So when building your list you have a number of particular units. In the second column of that combo box, you have a charge-per-unit for that particular unit. When "rolling up" the charges, you have the list of items in the estimate, but you build a JOIN of the estimated quantities with the units/charges table. At that point, you can compute anything you need in charges. Where there is a "double charge" because you also estimate X amount of labor to install Y amount of fascia board, you have a 3rd and 4th column that is non-zero to express the separate labor. Which should give you all that you need to make an excruciatingly detailed breakdown of costs of material and labor. What's the hard part? Assigning charges for each item. Tedious as all heck, but once it is done, you don't do it again wholesale. Might have to adjust individual prices if your costs go up, but they are in that table.

Please note that the above is ONE POSSIBILITY among many.

 

[timeto]

@CJ_London

would think you would want width as well - or some other definition such a single skin, 9" solid, 4" cavity etc.

Not sure I understand this. Are those brick definitions?

so either assign a default value of 1 and disable (perhaps using conditional formatting) and/or lock if required the fields you don't need or you need code to assign a caption to the label.

That would require that I have a hard-coded number of columns, I would like to explore a methodology that allows me to indicate only what each item in the breakdown would require. The methodology you refer to is definitely the simplest way to go about this, but I would no longer be able to handle formulas like ((a-b)+c)*d, I would have to add a lot of rows for simple calculations.

I have an app used for estimating build projects

It sounds like it's for a specific type of construction work. Since I do all trades, from masonry to structured cabling, from simple home plumbing to an entire building's fire protection system, even roads, my database can not possibly adapt to each specialty. So, I'm trying to find a way to make this flexible enough to adapt to any sort of measurement.

Thank you, I think I needed to add more details to my post.

 

[timeto]

@GaP42

it appears that you do not intend for the record of a task for a customer to persist

Hello, thanks for bringing that to my attention. I want to ensure clarity. In my tasks table, there's a foreign key (FK) linked to a task_groups table, which, in turn, has a FK connected to contract_details, and so on. The broader database structure is functioning well, with dedicated tables for pricing, quoting, and more. Currently, my focus is resolving the breakdown of quantities in my tasks table for weekly, bi-weekly, monthly, and other estimates.

Header names (as captions within a form) can be changed as you move from one record to another. However if you expect the column names to change as you move from record to record in a table (as shown in post #1) then that is a no go.

True that. The breakdown table should share the same headers for each task. Thanks for making me realize that.

your current conception shows them as simple items like a material (steel bar/ light bulbs etc)

They were vague examples indeed. A more accurate set of examples would be: "Reinforcement steel for concrete, X caliber, includes forging, etc" or "Installation of light bulb of X kind, Y brand, includes labor".

You might find it easier to write the narrative of what happens and how you will use the db

Sorry about not beind descriptive enough.

Basically:
When I get a project, I estimate the cost for the customer. Sometimes, they tell me what tasks to do, and other times, I suggest the tasks. I organize these tasks into categories using a self referencing table. These categories are linked to the project estimate. Each category has the tasks and each task is priced properly. There is no problem there.
Once the customer approves the estimate, the project moves to the construction phase. Here, the structure is similar to the estimate, but they are different tables, with a main construction part, categories, and specific tasks in each category. This helps keep things organized and clear from start to finish.

In each category, there's a specific task, but since the pricing is already set, my focus now is on managing the quantity of work for each task. I provide my client with estimates every X days. For example, if we agreed that a task requires a total of 50 units, I might complete 20 in the first week, 10 in the second, and another 20 in the third. The challenge isn't in figuring this out; it's about optimizing how I communicate it to the client. I aim to streamline the presentation of weekly or monthly estimates, showcasing what we've accomplished in specific areas during that time frame. For instance, I'll highlight the progress from one point to another, quantifying the work completed in each segment.

The challenge is that each task requires a different set of headers and a different calculation strategy.

 

[CJ_London]

Are those brick definitions?

No they are wall definitions - at least in the UK

but I would no longer be able to handle formulas like ((a-b)+c)*d,

investigate the eval function

It sounds like it's for a specific type of construction work.

its primarily for jobbing builders, carpenters, plasterers, landscapers typically for house building/renovating/extensions to provide estimates/quotes

I think I needed to add more details to my post.

I think you do

 

[timeto]

I think you do

Please refer to my response to @GaP42

I'll be back in a few hours to keep adding info and reply to the other posts. Thank you everyone.

 

[mike60smart]

It might help if you can upload an Excel sheet with various Tasks listed with calculations

 

[Pat Hartman]

The task definition table can contain a calculation. such as --- l * w * h * reps.
The four variables would be on the order table since the order would determine how many reps. Then you need a way to convert this into money.
You can then create a function that processes the calculation using the Eval() function.

I have an app where I did this but it was a long time ago and I haven't been able to turn up the actual code. But, I know you can do it.

 

[timeto]

If this is a tool to help you compute charges on a work project, then the unit you REALLY need is charge per unit-type

The remaining sections of the database handle pricing through a few dedicated workflows. To compute the total, it relies on the quantities supplied by the phase under discussion in this thread. Therefore, my focus is exclusively on a solution that adeptly and gracefully manages the breakdown of quantities.

One way I've seen this done is that you have a list of units that occur in your work. Perhaps like a combo box that lists m3, m2, kilograms, labor hours, etc.

Some software uses a fixed way of dealing with units, but my colleagues and I don't like it. Units can change a lot from one contract to another. For example, some people don't just say "meter"; they say "linear meter." Others might not use "Piece" but use abbreviations like "Pc" or "Pz." There's a lot of variety. Adding new units doesn't help much. We need to talk about our bids using our customer's words, not just the words our software uses. But, I have thought about making our units match the customer's units.

As for the rest of your advice, I apologize for not being clear that pricing is considered somewhere else. Thank you.

The task definition table can contain a calculation. such as --- l * w * h * reps.
The four variables would be on the order table since the order would determine how many reps. Then you need a way to convert this into money.
You can then create a function that processes the calculation using the Eval() function.

The money conversion is not done in this phase. I'm only interested in the quantities at this point of the workflow. And, as you will be able to see in the attached sample file, there may be columns that don't refer to L, W, H or reps. Thanks for jumping in.

@gemma-the-husky
@mike60smart
I have attached a sample file that shows how much these things can vary. It's not always just Length, Width, etc. I will need custom columns. As you can see, it's very easy in Excel, but it's hard to adapt to Access.

 

[gemma-the-husky]

I will look, but maybe you need 2 columns, one for quantity, and one for unit.

Whatever arguments you use to get the calculation result, you can pass to a function, and the function can calculate the return values.

So you could have the same function returning 12.6 metres, or 15 kW hrs. Something like that must be what you are doing in excel, surely?

Is that the sort of thing you mean?

 

[mike60smart]

The remaining sections of the database handle pricing through a few dedicated workflows. To compute the total, it relies on the quantities supplied by the phase under discussion in this thread. Therefore, my focus is exclusively on a solution that adeptly and gracefully manages the breakdown of quantities.

Some software uses a fixed way of dealing with units, but my colleagues and I don't like it. Units can change a lot from one contract to another. For example, some people don't just say "meter"; they say "linear meter." Others might not use "Piece" but use abbreviations like "Pc" or "Pz." There's a lot of variety. Adding new units doesn't help much. We need to talk about our bids using our customer's words, not just the words our software uses. But, I have thought about making our units match the customer's units.

As for the rest of your advice, I apologize for not being clear that pricing is considered somewhere else. Thank you.


The money conversion is not done in this phase. I'm only interested in the quantities at this point of the workflow. And, as you will be able to see in the attached sample file, there may be columns that don't refer to L, W, H or reps. Thanks for jumping in.

@gemma-the-husky
@mike60smart
I have attached a sample file that shows how much these things can vary. It's not always just Length, Width, etc. I will need custom columns. As you can see, it's very easy in Excel, but it's hard to adapt to Access.

Hi
It looks like you would need a specific Subform to enter dimensions & calculations depending
on the Specific Construction Element.

Walls
Slabs
Reinforced Steel
Marine Staircase
If you use a Subform for the selection of specific Elements then you can make visible the specific fields for the selection of Dimensions in a related Subform.

 

[timeto]

Whatever arguments you use to get the calculation result, you can pass to a function, and the function can calculate the return values.

The problem is that when I charge my customers, I have to be transparent about how I arrived to a certain quantity. I will not show them a formula or the code that processes the columns. What I will show is values arranged in a tabular way, by columns.

If I'm calculating surface area, they expect me to talk about two dimensions. But also, if this dimensions repeat, they would prefer to see a column that multiplies instead of another row with the same two dimensions. Thus, we show 3 columns for the "breakdown" and an additional for total.

When calculating in kilograms, I include extra details. Each steel rod is intended to have a specific length and a defined bend length as well. Precision is crucial, and we need to adhere closely to both the structural plans and the weight specifications provided by our steel rod supplier. So, we must show columns that adeptly break that down.

All these little details add up quickly, that's why we must be so precise.

 

[timeto]

If you use a Subform for the selection of specific Elements then you can make visible the specific fields for the selection of Dimensions in a related Subform.

Yes, and I need to express that in a database schema that, when reporting, I can see my data tabulated properly, with varying column names.

 

[Pat Hartman]

For example, some people don't just say "meter"; they say "linear meter." Others might not use "Piece" but use abbreviations like "Pc" or "Pz." There's a lot of variety.

You need to have your own internal units of measure. Then you have a cross reference table that relates the customer's units to the one you use in your internal calculations.

 

[timeto]

You need to have your own internal units of measure. Then you have a cross reference table that relates the customer's units to the one you use in your internal calculations.

I have arrived to that conclusion too. I need to match them.

Now, assuming I match them, how would the schema have to look in order to "load" a sort of "template" for quantity breakdown for that unit?

 

[timeto]

My breakdowns table should change, right?