append query? can this be done?

[teiben]

I have 2 tbls User & Scores, I need to periodically import into these from excel. I suspect I need to import into a table then create an append query...I have autonumber in the user table PK and Scores w/number, will this work? or would this be an update query?

 

[Scooterbug]

I import excel spreadsheets on a daily basis. Here is what I do.

I use the TransferSpreadsheet option in VBA. I dump the data into a temp table where I can correct any field(s) that need to be (In my case, I have some order numbers that have letters at the begining that need to be stripped off). Once I am done processing the data, I use an append query to dump the data into the respective table. Once that is done, I clear out the temp table.

 

[teiben]

can I import into 2 different tables? do you have any screen shots?

 

[Scooterbug]

You mean take the data from the spreadsheet and append the data to two different tables? Yes.

I dont know if screenshots of my code will help you in this situation. It's pretty specific to what I need it to do. For more info on how to use the TransferSpreadsheet method, you can open up the VBA editor and use the Help feature.

As for processing the data....it's hard to give advice when nothing is known about the data structure and what the desired outcome should be. If you want more advice, gonna need to know a bit more about what you are trying to accomplish.

Edit: Brain seems to be on Vaction...I re-read your first post...

When you import the data from the spreadsheet, what differiates between Users and Scores? What I would do, to make things easier, would to put the User data in one spreadsheet and the Score data into another. This way, when you import, you basically have the data already separated. Unless you need to do some kind of manipulation on certain fields, you can just import the data straight into the desired tables without needing a temp table.