Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Loop and Update Fields in Table

Posted on 2011-02-15
Medium Priority
989 Views
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
0
Question by:creativefusion
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 5
• 4

LVL 10

Assisted Solution

PSSUser earned 2000 total points
ID: 34904710
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:
Update_FCL20_From_FCL40

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"));

Update_FCL40H_From_FCL40
UPDATE MT_SKU_MASTER, MT_CONTAINER_TYPES AS 40GP, MT_CONTAINER_TYPES AS 40HC SET MT_SKU_MASTER.FCL_40H_QTY = Int(([MT_SKU_MASTER].[FCL_40_QTY]/[40GP].[CubicCargoCapacity])*[40HC].[CubicCargoCapacity])
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:
Update_FCL40_From_FCL20
Update_FCL40_From_FCL40H
After the above 2 all FCL40 fields, aside from where all 3 fields are blank will be populated.
Update_FCL20_From_FCL40
Update_FCL40H_From_FCL40
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,
Chris
0

Author Comment

ID: 34910401
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?

Regards,
Shaun

0

LVL 10

Expert Comment

ID: 34914080
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
0

Author Comment

ID: 34914151
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
0

LVL 10

Accepted Solution

PSSUser earned 2000 total points
ID: 34914490
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,
Chris
0

Author Comment

ID: 34914571
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
0

Author Closing Comment

ID: 34914582
Great help. Highly recommended. Thanks again
0

LVL 10

Expert Comment

ID: 34914618
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))
;

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
0

Author Comment

ID: 34914756
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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
###### Suggested Courses
Course of the Month6 days, 16 hours left to enroll

#### 705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.