Solved

Loop and Update Fields in Table

Posted on 2011-02-15
9
982 Views
Last Modified: 2012-05-11
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
Comment
Question by:creativefusion
  • 5
  • 4
9 Comments
 
LVL 10

Assisted Solution

by:PSSUser
PSSUser earned 500 total points
Comment Utility
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

by:creativefusion
Comment Utility
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

by:PSSUser
Comment Utility
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

by:creativefusion
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Accepted Solution

by:
PSSUser earned 500 total points
Comment Utility
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

by:creativefusion
Comment Utility
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

by:creativefusion
Comment Utility
Great help. Highly recommended. Thanks again
0
 
LVL 10

Expert Comment

by:PSSUser
Comment Utility
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
0
 

Author Comment

by:creativefusion
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now