Thread: Do calculation in Access then Export to Excel

Hi all,

I need help setting up a calculation that will result in about 80,000+ records and growing. This query result will then have to be exported back to Excel in 2 separate worksheet outputs since I have Excel 2002 SP3 and it only goes up to 65,000 per worksheet.

The calculation will calculate the cost of a specific item to a particular destination. Right now there are about 120 locations and a little less than 700 items (hence roughly 80k unique combinations). Both of these variables will grow over time that is why I need a macro to run this twice a week or so to keep things up to date. I have each tab of items set up as an indivual table in Access (10 product groups) as well as the Freight cost to each location in another table.

Right now there is a drop-down already made to select the item location and item # in the 1st worksheet of Excel and the calculation works just fine with the index-match array that is in the "delivered cost" cell. I just need to do the calculations in Access instead of Excel as that is a requirement from my company, and then export the results back to Excel.

I need some kind of looping macro or calculation set-up in Access to do this and the calculation step per item is:

Delivery cost to location divided by the cartons/container to get the freight adder, then the freight adder is added to the cost/container to give us the landed cost result.

The following is an example:

DDP_Cost CTNs/Container Freight Cost/CTN Landed_Cost
$909 864 $1.05 $28.38 $29.44


I hope someone can help me as I have been stuck on this for almost 2 weeks as I am just a beginner at programming and intermediate at Access (not a clue on calculation part of Access). I guess the biggest problem is that the worksheet inputs in Excel have to stay in that format as it is live linked to the tables in Accesss right now. it will be a shared workbook with other users adding item # information as we add it to stock and I am clueless on performing the calculation in Access.

Much Thanks.