Concatenating imported data for primary key

[Persist]

1I want to regularly import data into an access table from a spreadsheet

The spreadsheet has data: Code1, Code2, Number1, Number2
Code1 = share code
Code2 = stock exchange code
Number1 = price

I want the resulting access table to have a primary key made up of the concatenation of the two codes: Code1 and Code2

I have a table tSeparate which has the same fields as the spreadsheet.
Code1, Code2, Number1, Number2

First I delete all the records in tSeparate
Then I import the data into tSeparate

I have a query qJoining which operates on tSeparate giving:
BothCodes: [Code1] & [Code2], Number1, Number2

I have a table tJoined which has the same fields as the above query


[BothCodes], Number1, Number2

I delete all the old records in tJoined
Then I run an append query which uses qJoining to give me a table with the primary key I want.

Is there an easier way to do this?

[June7]

Why is Excel involved?

Can you set a link to the Excel spreadsheet?

A linked spreadsheet can be queried just like a table.

[Persist]

Excel is involved as my share charting package produces a csv file containing the data I want: data.csv
I open the csv file in Excel and save it as data.xlsx (Access does not recognise the csv file)

I will try setting up an access table that links to this xlsx file, rather than importing this data into tSeparate

Then I can create the table with the concatenated primary key as I previously described:
. query qJoining and
. the append query

There is data on about 4000 shares so I appreciate having fewer copies of this. I did have the data duplicated in
. csv file
. xlsx file
. Access table tSeparate and
. Access table tJoined

Your suggestion of the linking means I do not have to store the data in tSeparate. Thanks

Could this process be simplified further

[offie]

So I found http://office.microsoft.com/en-us/ac...001232227.aspx
I didn't read all of it but it seems to be what you want,

Do you need Code1 and Code2 to be together? You can set them both as primary keys (Select both then press the key button), which I think would work in your situation.
Ex: Both 1-2 and 1-3 rows would be different because even though 1 is the same, the second field is different, so if they are both a primary key this will be allowed.

To combine them you would have to run a query, simple [Code1] & " " & [Code 2], the " " is to add a space between them. Queries update when tables update so no issue there.

I think your main question was about updating right? So if you link an access table to an excel sheet, when you change the excel sheet the table changes. If you import straight to access you can append (sew tables together) them.

[June7]

Further simplification would be not bothering with writing records into Access table. Excel links can be queried just like Access tables, just can't edit them from Access. Even further would be linking to the csv file if you could figure out why Access won't and resolve that issue.