Noting the record number to update later

[Boomshank]

Hello,

I have a problem that i need some help solving.

I have a table called tblCourierCosts, the table consists of the following fields:

Date
Run
Boxes
CourierCost

What i need to do is for each Run, add the number of boxes and then perform a calculation to work out the cost and place the cost for the whole run in the CourierCost field of the first record of the run.

I am struggling with keeping a note of the record number (so i can write the costs back to the cost field of the 1st record)

Can someone help please

 

[marlan]

Hi,
I'm trying to understand the issue: how do you update your data, throug a bound form? execute an Insert SQL?
in any case, you can have the result of your VBA call be part of the same insertions as the number of boxes.

 

[jdraw]

It might be more useful and helpful if you showed more of your database structure-- tables and relationships. Sounds not normalized to me, but there's so little info. How do you identify courier? What about cost per box-- does this value change with time/volume etc? Do you do your calculation separately and manually?

Storing total cost in record 1 seems very customized. You might consider some sort of Summary Table that stores totals by run and or courier, or some other meaningful combination.

Good luck.

 

[Boomshank]

The data is imported from a text file, which already contains the courier. The courier cost is static and will not be changing.

 

[marlan]

Hi, I hope it is still relevant,

I would import the text file into a temp table, and insert a manipulation of the temp table to the permanent table:

INSERT INTO [I]permanentTable[/I] (Date, Run, Boxes, CourierCost)
SELECT Date, Run, Boxes, [I]calculationFunction[/I](Boxes)
FROM [I]tempTable[/I]

calculationFunction doesn't have to be VBA code, it could be somthing you created using the expresion builder.

good luck!