creativefusion

asked on

# Loop and Update Fields in Table

All,

I am in the process of developing a small database which will allow users to plan truck movements.

Within the database, there are currently three tables.

MT_SKU_MASTER

MT_CONTAINER_TYPES

MT_CONTAINER_CATEGORY

Within the MT_SKU_MASTER table, there are three fields that contain container quantities in them. The data in these fields is mandatory as it will be used to be perform calculations later.

My problem is, not every item inside this table contains the correct amount fo data. In some cases, it is all there, in other cases only partly and in worst cases, not there at all. What makes the problem worse, the issue is unfixable despite my constant nagging.

On average, the table contains a total of 17,000 records and has a key against the [ITM_REF_NO] field.

I am wanting someone to help me work out a method loop through the table and update the container quantities based on the following conditions.

1. Data exists for FCL_20 but not for FCL_40 or FCL_40H

2. Data exists for FCL_40 but not for FCL_20 or FCL_40H

3. Data exists for FCL_40H but not for FCL_40 or FCL_20

4. No data exists at all.

So in any case, the solution must be dynamic enough to handle any of these scenarios.

To handle scenario 4, I already have a function that calculates the qty for each container type, however, I have no idea on how to update the table with the results. It can be reviewed inside the test db.

For the other scenarios, calculation definitions are enclosed as follows:

For item 326, the following would apply.

Since there is a FCL_40_QTY of 500, we can determine how many cases we can fit per cubic metre of space.

Calculation = 500 (Quantity) / 60 (Cubic Capacity) therefore 8.333 VPs per Cube

So, to determine how many cases can fit inside a FCL_20, we multiply the 8.333 by the available space. i.e. 8.333 x 30 = 250

Similarly, we can apply the same method to determine the FCL_40H qty. i.e 8.333 x 70 = 583.33 or 582 rounded down to the nearest integer.

Once each value has been established, the record inside the table would then be updated.

Could someone please help me with this as I don't know what to do?

Test.mdb

I am in the process of developing a small database which will allow users to plan truck movements.

Within the database, there are currently three tables.

MT_SKU_MASTER

MT_CONTAINER_TYPES

MT_CONTAINER_CATEGORY

Within the MT_SKU_MASTER table, there are three fields that contain container quantities in them. The data in these fields is mandatory as it will be used to be perform calculations later.

My problem is, not every item inside this table contains the correct amount fo data. In some cases, it is all there, in other cases only partly and in worst cases, not there at all. What makes the problem worse, the issue is unfixable despite my constant nagging.

On average, the table contains a total of 17,000 records and has a key against the [ITM_REF_NO] field.

I am wanting someone to help me work out a method loop through the table and update the container quantities based on the following conditions.

1. Data exists for FCL_20 but not for FCL_40 or FCL_40H

2. Data exists for FCL_40 but not for FCL_20 or FCL_40H

3. Data exists for FCL_40H but not for FCL_40 or FCL_20

4. No data exists at all.

So in any case, the solution must be dynamic enough to handle any of these scenarios.

To handle scenario 4, I already have a function that calculates the qty for each container type, however, I have no idea on how to update the table with the results. It can be reviewed inside the test db.

For the other scenarios, calculation definitions are enclosed as follows:

For item 326, the following would apply.

Since there is a FCL_40_QTY of 500, we can determine how many cases we can fit per cubic metre of space.

Calculation = 500 (Quantity) / 60 (Cubic Capacity) therefore 8.333 VPs per Cube

So, to determine how many cases can fit inside a FCL_20, we multiply the 8.333 by the available space. i.e. 8.333 x 30 = 250

Similarly, we can apply the same method to determine the FCL_40H qty. i.e 8.333 x 70 = 583.33 or 582 rounded down to the nearest integer.

Once each value has been established, the record inside the table would then be updated.

Could someone please help me with this as I don't know what to do?

Test.mdb

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Hi Shaun,

sorry, I missed the comment in your original question about the function in the database.

I can create a query to update the 3 columns, 1 at a time, based on the information contained within this query.

I just have a couple of observations/questions.

1) The function checks both the weight and volume of the items compared with the container. However the functions I created to calculate the capacity based entirely on volume. This could result in an answer that is too heavy for the container - particularly if calculating using container 20GP/column FCL_20_QTY as your starting column, since it has a high weight to capacity ratio.

This means it would be best to instead calculate missing columns using the same equation defined in the module (i.e. as if none of the columns had been populated).

2) Using the equation for item 326 I make the FCL_40_QTY as 1691 not 500. This is because

Weight calculation:

23000/13.6 = 1691 (when rounded down)

Capacity calculation:

60/0.0171 = 3508 (when rounded down)

Have I made a mistake with the calculations, is the FCL_40_QTY incorrect in the sample data or is there another factor I'm missing?

If the FCL_40_QTY is incorrect do you want your updates to assume any populated figures are correct or should they be recalculated?

3) Calculating capacity purely on total volume may produce a higher figure than can actually fit. This may be the reason for the difference in point 2. Using just volume can give a good ball park in most cases. A better method, if the data was available, would be based on the 3 dimensions (Width, Height and Depth). For example if you have a box 1 * 1 * 1 and an object is 2 * 0.5 * 0.5 then based on volume you can fit 2 but based on actual size none will fit.

This 3rd point is a good reason for keeping the quantity defined if provided (assuming the source can be trusted), and may be worth considering with any future developments to your database, if you can capture dimensions.

For now if you can answer point 2 I will write the required queries for you. Just to be clear the questions I need answering for now are:

Have I got the calculation incorrect/missed something?

Should existing values be left unchanged even if they differ from the results of the calculation or would you like all values recalculated?

Kind Regards,

Chris

sorry, I missed the comment in your original question about the function in the database.

I can create a query to update the 3 columns, 1 at a time, based on the information contained within this query.

I just have a couple of observations/questions.

1) The function checks both the weight and volume of the items compared with the container. However the functions I created to calculate the capacity based entirely on volume. This could result in an answer that is too heavy for the container - particularly if calculating using container 20GP/column FCL_20_QTY as your starting column, since it has a high weight to capacity ratio.

This means it would be best to instead calculate missing columns using the same equation defined in the module (i.e. as if none of the columns had been populated).

2) Using the equation for item 326 I make the FCL_40_QTY as 1691 not 500. This is because

Weight calculation:

23000/13.6 = 1691 (when rounded down)

Capacity calculation:

60/0.0171 = 3508 (when rounded down)

Have I made a mistake with the calculations, is the FCL_40_QTY incorrect in the sample data or is there another factor I'm missing?

If the FCL_40_QTY is incorrect do you want your updates to assume any populated figures are correct or should they be recalculated?

3) Calculating capacity purely on total volume may produce a higher figure than can actually fit. This may be the reason for the difference in point 2. Using just volume can give a good ball park in most cases. A better method, if the data was available, would be based on the 3 dimensions (Width, Height and Depth). For example if you have a box 1 * 1 * 1 and an object is 2 * 0.5 * 0.5 then based on volume you can fit 2 but based on actual size none will fit.

This 3rd point is a good reason for keeping the quantity defined if provided (assuming the source can be trusted), and may be worth considering with any future developments to your database, if you can capture dimensions.

For now if you can answer point 2 I will write the required queries for you. Just to be clear the questions I need answering for now are:

Have I got the calculation incorrect/missed something?

Should existing values be left unchanged even if they differ from the results of the calculation or would you like all values recalculated?

Kind Regards,

Chris

ASKER

Hi Chris,

No problems and again thanks for helping me with this.

Please find enclosed my responses to your questions:

1. The function is to be used only for items where all fields are = 0. In other words, FCL_20 = 0, FCL_40 = 0, FCL_40H = 0. Item 5730 is a good example of this.

2. You are correct! I merely dropped the data in there as a sample however, and VERY importantly, my stakeholders specifically asked to use this method as oppossed to the function we developed. The reason being is that the recorded values are based on actual reciepts from suppliers, and specifiy exactly how many cartons can be loaded into a shipping container based on a number of factors such as product stackability, carton length, width, height, carton packaging specifications, mandatory uprights... the list goes on. So, it was agreed by my stakeholders that the best method would be to convert it into cube then multiply the cartons per cube out by the available cube for the other containers that do not have values.

3. I agree entirely. Funnily enough, I do have the dimensions for each item, but lets just go with what we have for now and refine it in future if required.

Please let me know if I have not answered your questions properly.

Thanks again for your help,

Shaun

No problems and again thanks for helping me with this.

Please find enclosed my responses to your questions:

1. The function is to be used only for items where all fields are = 0. In other words, FCL_20 = 0, FCL_40 = 0, FCL_40H = 0. Item 5730 is a good example of this.

2. You are correct! I merely dropped the data in there as a sample however, and VERY importantly, my stakeholders specifically asked to use this method as oppossed to the function we developed. The reason being is that the recorded values are based on actual reciepts from suppliers, and specifiy exactly how many cartons can be loaded into a shipping container based on a number of factors such as product stackability, carton length, width, height, carton packaging specifications, mandatory uprights... the list goes on. So, it was agreed by my stakeholders that the best method would be to convert it into cube then multiply the cartons per cube out by the available cube for the other containers that do not have values.

3. I agree entirely. Funnily enough, I do have the dimensions for each item, but lets just go with what we have for now and refine it in future if required.

Please let me know if I have not answered your questions properly.

Thanks again for your help,

Shaun

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi Chris,

Your a legend!!! Thanks so much for your help with this.

I'll work my way through incorporating this all into one procedure with my updates.

Great help.

Kind regards,

Shaun

Your a legend!!! Thanks so much for your help with this.

I'll work my way through incorporating this all into one procedure with my updates.

Great help.

Kind regards,

Shaun

ASKER

Great help. Highly recommended. Thanks again

Cheers.

Sorry, just noticed a mistake when I've come back to this page :(

The first pair of queries only check the source column isn't 0, not that the destination is.

The first query should be:

UPDATE MT_SKU_MASTER, MT_CONTAINER_TYPES AS 40GP, MT_CONTAINER_TYPES AS 20GP SET MT_SKU_MASTER.FCL_20_QTY = Int(([MT_SKU_MASTER].[FCL_40_QTY]/[40GP].[CubicCargoCapacity])*[20GP].[CubicCargoCapacity])

WHERE (((MT_SKU_MASTER.FCL_40_QTY)<>0) AND (([40GP].ContainerTypeName)="40GP") AND (([20GP].ContainerTypeName)="20GP")

/*Add condition below so we don't overwrite a valid FCL_20_QTY!!*/

AND ((MT_SKU_MASTER.FCL_20_QTY)=0))

;

Sorry about that!

If you need anything related to my response clarifying or checking (I can't see any othe mistakes at the moment!) please let me know.

Kind Regards,

Chris

Sorry, just noticed a mistake when I've come back to this page :(

The first pair of queries only check the source column isn't 0, not that the destination is.

The first query should be:

UPDATE MT_SKU_MASTER, MT_CONTAINER_TYPES AS 40GP, MT_CONTAINER_TYPES AS 20GP SET MT_SKU_MASTER.FCL_20_QTY = Int(([MT_SKU_MASTER].[FCL_

WHERE (((MT_SKU_MASTER.FCL_40_QT

/*Add condition below so we don't overwrite a valid FCL_20_QTY!!*/

AND ((MT_SKU_MASTER.FCL_20_QTY

;

Sorry about that!

If you need anything related to my response clarifying or checking (I can't see any othe mistakes at the moment!) please let me know.

Kind Regards,

Chris

ASKER

Hi Chris,

Thanks for clarifying this. I would have found it during integration but none the less I will let you know if there is any problems.

Thanks once again.

Shaun

Thanks for clarifying this. I would have found it during integration but none the less I will let you know if there is any problems.

Thanks once again.

Shaun

ASKER

Thanks for providing this solution.

Is it possible for you to translate the function inside module 1 that handles scenario 4?

In simple terms, the function uses a number of parameters listed below:

A: ContainerTypeID

B: ContainerCategoryID

C: VPWeight

D: VPCube

Is there a way we can incorporate this into a procedure as well?

Regards,

Shaun