Loop and Update Fields in Table


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.


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?

Who is Participating?
PSSUserConnect With a Mentor Commented:
Hi Shaun,

thats great thanks. Good to know you are aware of the possible issues :)

From the list of factors beyond dimension wouldn't like to think how to take them all into account in a single query - glad you're not asking for that (yet!).

Anyway, the below should be the final piece. This will update all 3 columns where all 3 are zero, so can be run before or after the 4 queries mentioned before.

UPDATE MT_SKU_MASTER AS M, MT_CONTAINER_TYPES AS 20GP, MT_CONTAINER_TYPES AS 40GP, MT_CONTAINER_TYPES AS 40HC SET M.FCL_20_QTY = IIf(Int([20GP].PayloadCapacity/M.VNDR_PACK_WEIGHT)<Int([20GP].CubicCargoCapacity/M.VNDR_PACK_CUBE),Int([20GP].PayloadCapacity/M.VNDR_PACK_WEIGHT),Int([20GP].CubicCargoCapacity/M.VNDR_PACK_CUBE)), M.FCL_40_QTY = IIf(Int([40GP].PayloadCapacity/M.VNDR_PACK_WEIGHT)<Int([40GP].CubicCargoCapacity/M.VNDR_PACK_CUBE),Int([40GP].PayloadCapacity/M.VNDR_PACK_WEIGHT),Int([40GP].CubicCargoCapacity/M.VNDR_PACK_CUBE)), M.FCL_40H_QTY = IIf(Int([40HC].PayloadCapacity/M.VNDR_PACK_WEIGHT)<Int([40HC].CubicCargoCapacity/M.VNDR_PACK_CUBE),Int([40HC].PayloadCapacity/M.VNDR_PACK_WEIGHT),Int([40HC].CubicCargoCapacity/M.VNDR_PACK_CUBE))
WHERE (((M.FCL_20_QTY)=0) AND ((M.FCL_40_QTY)=0) AND ((M.FCL_40H_QTY)=0) AND (([20GP].ContainerTypeName)="20GP") AND (([40GP].ContainerTypeName)="40GP") AND (([40HC].ContainerTypeName)="40HC"));

Kind Regards,
PSSUserConnect With a Mentor Commented:
From the information above, the first 3 items can be done with a series of update queries.

I can't write a query for the 4th situation as there isn't any information on how to calculate one of the 3 fields without the other 2. If you can provide informtion on how to do this I will write a query.

For item 2 (since that is your example) the 2 queries you need are below:

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

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

As you can see these are just variations of each other.

For item 1 you just need to add an update query Update_FCL40_From_FCL20, as FCL_40H can then be populated using Update_FCL40H_From_FCL40.

For item 3 you just need to add an update query Update_FCL40_From_FCL40H and can then use Update_FCL20_From_FCL40.

As a complete solution for all 3 items I would run the queries in the order:
After the above 2 all FCL40 fields, aside from where all 3 fields are blank will be populated.
The above 2 will then ensure the remaining 2 fields are populated.

Of course instead of using FCL40 as you main column you could use eitehr FCL20 or FCL40H - if one column more often contains data, or is easier to calculate if all 3 are blank, I would use this as your main column.

Kind Regards,
creativefusionAuthor Commented:
Hi Chris,

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?


Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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,
creativefusionAuthor Commented:
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,
creativefusionAuthor Commented:
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,
creativefusionAuthor Commented:
Great help. Highly recommended. Thanks again

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:

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!!*/

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,
creativefusionAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.