Calculations and Access, how do I . . . ?

[Hannah1]

An Overview: I am designing a database which tracks competitors finishes at several national events. With each finish, competitors accumulate points which is based on their placement and the number of competitors in the class for each event. For each event / class, the points must be calculated and competitor's finish recorded. Then the points for each event are collected and summed in the end to determine the overall winner.

My question: the calculations are rather complex, I can get Excel to do them, and create a table which will generate the points / place. But how do I get the information to and from Access and Excel? And is it possible to automate the calculation, maybe using a macro, so that I can simply enter the class size and competitor finish in Access forms, have Excel run the calculations and get Access to access (sorry) the point information in the Excel spreadsheet? Or is there an all-together different and simpler way to do this?

 

[rtg]

The TransferSpreadsheet method may help.

This example is taken from the Help file

The following example imports the data from the specified range of the Lotus spreadsheet Newemps.wk3 into the Microsoft Access Employees table. It uses the first row of the spreadsheet as field names.

DoCmd.TransferSpreadsheet acImport, 3, "Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"

You could run this code as a function started from a form or on startup via autoexec. Once in the Access table the excel data can be manipulated before its displayed on the form.

Hope this helps