Schema that facilitates the calculation of task breakdowns dynamically

[Pat Hartman]

The x=ref table that provides the RowSource values for the data entry form includes the internal UOM. You simply join to the x-ref table whenever you need to translate the customer's UOM to your processing UOM.

 

[timeto]

Uh... is this what would make it work?

 

[timeto]

The x=ref table that provides the RowSource values for the data entry form includes the internal UOM. You simply join to the x-ref table whenever you need to translate the customer's UOM to your processing UOM.

Yeah, I mean, that's a combobox. But how do I actually make use of this for the breakdown?

 

[Pat Hartman]

I don't see any table that defines the UOM or the internal use UOM. Perhaps that is what units is supposed to be. It needs to have a row for every possible UOM value. It needs a flag that says this is an internal UOM. Then it needs a column for UseThisUOM. For convenience, even the rows marked with the internal UOM flag should have an ID in the UseThisUOM column so for use in calculations, ALL calculations use the UseThisUOM ID value. In the maintenance form, there will be a combo to select UseThisUOM and it will use a RowSource query that selects from the units table ONLY the rows with the internal UOM flag set to True.

 

[gemma-the-husky]

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.

If you calculate a surface area, is that simply length x width. If it's a curious shape, why would they expect you to demonstrate your calculation. If you evaluate it as 83 sq metres, why is that not sufficient?

If it's all too complex for you to model in access, why not just record the totals in access, and attach a workings sheet in excel?

 

[mike60smart]

This is a quick example of the subform for carrying out the calculations

 

[timeto]

This is a quick example of the subform for carrying out the calculations

Thank you very much, Mike. I really appreciate the effort you put into capturing the initial vibe of the solution. There is, however, a problem in the concept. If you wouldn't mind, take a look at the Excel file attached in post #13. In it, the columns used for breaking down the total Kg for the reinforcement steel task are entirely different.

I might not have been clear enough that I want to avoid hardcoding those columns for each breakdown for that very reason, I may have different columns in some tasks. Length, Width, etc., are the most common, but there are some that will differ and I won't be able to break down the quantity with those columns. Sorry about not being clear.

If you calculate a surface area, is that simply length x width. If it's a curious shape, why would they expect you to demonstrate your calculation. If you evaluate it as 83 sq metres, why is that not sufficient?

If it's all too complex for you to model in access, why not just record the totals in access, and attach a workings sheet in excel?

It's not sufficient to just show the number because there will be someone reviewing my work. This person will grab a measuring tape and actually measure each dimension stated in the document.

Why not in Excel? I've been doing it in Excel since the beginning of time, as everyone else, but there are many other parts to this that require the constraints of a relational database and I want to stop migrating data, I want to do my work in one place.

I don't see any table that defines the UOM or the internal use UOM. Perhaps that is what units is supposed to be. It needs to have a row for every possible UOM value. It needs a flag that says this is an internal UOM. Then it needs a column for UseThisUOM. For convenience, even the rows marked with the internal UOM flag should have an ID in the UseThisUOM column so for use in calculations, ALL calculations use the UseThisUOM ID value. In the maintenance form, there will be a combo to select UseThisUOM and it will use a RowSource query that selects from the units table ONLY the rows with the internal UOM flag set to True.

Please refer to post #22, I'm showing the matched unit in the tasks table.

 

[Pat Hartman]

Please refer to post #22, I'm showing the matched unit in the tasks table.

That isn't a cross reference. You need a UOM table similar to what I described. The data entry form uses the full list of UOM's both internal and external. The cross reference part substitutes an internal UOM when the selected UOM is not an internal UOM.

The task table has only ONE UOM value. The one that the customer used. The internal UOM is obtained by joining the UOM from task and picking up the id of the UOM that will be used for the internal calculations.

Another way (but the wrong way to do this), which is what I think you are trying to do is to have two UOM's on the task. One that the customer uses and one that you use internally. You are having trouble because you can't see how to do this on the fly. Sit back and think about what a cross reference is. The UOM table is a list of known values. When you add a new item to this list as you might occasionally, you, at that time, figure out what your internal UOM is for the customer's UOM. Then every time the customer uses 1xl, you look in the UOM table and see that translate to sqft and that is what you use internally.

 

[timeto]

Another way (but the wrong way to do this), which is what I think you are trying to do is to have two UOM's on the task. One that the customer uses and one that you use internally. You are having trouble because you can't see how to do this on the fly. Sit back and think about what a cross reference is. The UOM table is a list of known values. When you add a new item to this list as you might occasionally, you, at that time, figure out what your internal UOM is for the customer's UOM. Then every time the customer uses 1xl, you look in the UOM table and see that translate to sqft and that is what you use internally

I'm afraid I'm not understanding why keeping my customer's unit and its match in the same row is bad. If I understand you correctly, you say I need a table like this:
fk | match
1 | pc
1 | pz
1 | pce
2 | sqmt
2 | m2
2 | mt2
...

Is that what you mean?

 

[mike60smart]

I'm afraid I'm not understanding why keeping my customer's unit and its match in the same row is bad. If I understand you correctly, you say I need a table like this:
fk | match
1 | pc
1 | pz
1 | pce
2 | sqmt
2 | m2
2 | mt2
...

Is that what you mean?

This example allows you to select a specific Task and then you add the dimensions in the specific Subform.

It takes a bit longer to setup but your dimensions are all 100% accurate.

 

[Pat Hartman]

I'm afraid I'm not understanding why keeping my customer's unit and its match in the same row is bad. If I understand you correctly, you say I need a table like this:
fk | match
1 | pc
1 | pz
1 | pce
2 | sqmt
2 | m2
2 | mt2

Keeping the translated UOM in the task table violates normal forms. It would be a duplication of data.

The table I suggested has FOUR columns
PK (autonumber)
UOM (text value)
InternalYN (whether this is your UOM or not)
UseThisUOM (the FK to whatever internal UOM the text value relates to.

The xref table needs to be updated ONCE, when you get a new UOM from a customer. Then you lookup the UOM you want to use when you need to do a calculation.

 

[Pat Hartman]

If you store the looked up value when you create the task record, you run the risk of a data anomaly should you have to change the xref table.

 

[timeto]

This example allows you to select a specific Task and then you add the dimensions in the specific Subform.

It takes a bit longer to setup but your dimensions are all 100% accurate.

Thank you, Mike. I'm sorry, my intention is to not hard-code any column. The sample Excel file serves just as a little demonstration that I may have different columns for different tasks. I really can't determine the number of columns or the names for them, this needs to be done on the spot, according to the task. While I do have many tasks that can be broken down using Length, Width, Height, ... as headers, I always have tasks that can not be broken down with any of those headers.

I appreciate the effort you have put in demonstrating how it can be approached if I hard-code the columns. It also sparked a few ideas of how I could fill each estimate with ease. Thank you.

The table I suggested has FOUR columns
PK (autonumber)
UOM (text value)
InternalYN (whether this is your UOM or not)
UseThisUOM (the FK to whatever internal UOM the text value relates to.

I think I'm more of a visual person, I can't visualize your explanations within my workflow, do you have a basic example you can show me? This is what I could understand from the explanation, but I'm not sure it's correct:

I still can't understand why I need a boolean, to me, that sounds like extra steps. I could be wrong, of course, but I'm lost here.

 

[Pat Hartman]

You have interpreted my suggestion rather than following my directions. My original suggestion said to modify the units table assuming that is where you are storing UOM. Why not just create the table I suggested with the four columns? That IS the cross reference table between your own internal UOM and the potential customer UOMs and it replaces the unit table. That x-ref is static and has nothing to do with a task. It stands by itself. The unit_Id in the task table relates to the unit_id in the units table as it used to. Nothing about that changed.

The reason for the YN column is so that you can distinguish in the list of UOMs, which are for internal use and which are in the list because the customer uses a different name. The YN is used so that on the form that builds the x-ref, you can use a combo to limit the value of UseThisUOM to ONLY the UOMs that are flagged as INTERNAL. So the UseThisUOM contains a value from unit_id. This is a self referencing relationship. Think of it like a table of employees. Some of the employees are managers. You have a manager flag or more likely a field with one of several values listing the type of management position. Then you have a field named ManagerID. The ManagerID contains a value from the EmployeeID field because all managers are employees but not all employees are managers - and that is the "flag" part. Just like not all UOMs are used by your internal calculations, only some of them are.

The relationship of task to UOM is 1-1 and so the UOM is an attribute in the task table. BUT, the UOM shown in the task table is what came from the customer's order. The UOM on the task table is related to the unit table. That means that whenever you need to find the internal UOM, you can get it from the units table by using a join.