A little guidance?

[foxygrandma]

I was assigned to fill in a column of costs in a 120,000 line table. Quite basically, I imported the data including the costs from excel into access (table 1), and used an update query to match the cost's position and update table 2 as necessary.

There were two types of data I was pulling from the table 1 and using to match position in the table, delivery numbers and shipment numbers. Every record provided only one number, either delivery or shipment. I matched the delivery numbers successfully. However, because there are several deliveries to a shipment, the records that provided only the shipment numbers are incorrect. It filled in the same cost for several different deliveries of the same shipment. This is to be expected. I solve this problem using the following method.

Another piece of information included in the record is its weight. My job is to distribute the cost of each shipment according to the percent of the total weight each delivery makes up. For example: Object one weighs 2 lbs, object two weighs 6 lbs, the cost is $10. Object one would get $2.50 of the cost, object two would get $7.50. Pretty simple.

I put together a quick Excel spreadsheet that I plug the weights and cost into and I get the cost per unit. Then I copy and paste those back into the table. This is a long manual process and will probably wear out my control, c, v keys, as well as my sanity.

Does all this make sense?

My question is, what can I possibly do to automate this process? Is VBA the answer? Is there an Access function of some sort? Do I link the Excel sheet? I need some guidance on what to do for this problem. Thank you for any help you can offer. And I also appreciate you sitting and reading through this novel of a post.

[n8kindt]

i definitely understand why you want to use access. i got started using access for the same purpose. but i will tell you this: it is very hard to follow along with the exact result you are trying to achieve. it is probably very simple (it usually is) so i would suggest you attach fragments of the tables you are working with--or at least a visual example of some sort. perhaps someone can follow along with your thread but it seems a bit involved without knowing your table structure. as far as guidance, i would say you probably want to create a simple function with vba that calculates the prices using a similar formula you used in excel and combine it with a one-to-many query. i would love to give you a more definite answer than that though, so please post back with a better example of your tables. probably the best way to do so would be to post your metadata. here is an example of how to post your metadata:

Table Name=tblStudent

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

regards,
nate

[foxygrandma]

Delivery#	Shipment#	 GrossWeight Cost Month
81910252	565017	437.2	829.77	February
81910838	565017	2046	        829.77	February
81910148	565017	3472.965	829.77	February
81910834	565017	899.193	829.77	February
81912056	565017	712.845	829.77	February
81910251	565017	609.12	829.77	February
81912054	565017	798.572	829.77	February
81911524	565017	2847.416	829.77	February

This ought to put in perspective what I'm doing. Basically this is a part of the table where I have already updated the cost to go along with the shipment#. As you can see the Shipment# is 565017, so wherever that number was found, I inserted that Cost. However this is incorrect. The cost is only 829.77 for the entire shipment. And it needs to be divided out among the eight deliveries. That's where the weight comes into play. The weights are all added up and then each individual weight is divided by the total to get the percentage of the cost it gets. So the total weight here is 11823.31. The first weight is 437.2. 437.2/11823.31 = 0.037 or 3.7% of the total. Multiply that by the total price which is 829.77 = 30.68. So 30.68 would go in the first row under cost, replacing 829.77. Does this all make more sense?

So I would have to go through and do that manually for the entire table unless there is some creative way of automating this process. I have never used VBA so I wouldn't know the first thing to do, but I might be able to figure it out if you tell me where to start.