Newbie data entry question.

[pjaj]

I am familiar with RDB in general, but not with the specifics of Access data entry.

I have created a simple database from an Excel spreadsheet that tracks my investments. The spreadsheet contained 3 columns, stock name, date and value.There was about a year's worth of data making some 4500 rows. There were about 20 different stock names that split out into one lookup table, 300 dates into another leaving the 4500 values in the main table. This all worked OK. The names change from time to time, and there are about 18-20 new sets of data each week day.

The problem is that I have a second spreadsheet with a similar set of data, mostly the same stock names but different dates and of course values. How can I import this data into the existing database automatically adding any new stock names and the new dates to the two lookup tables? There are about 1300 records to import, so manually is not the preferred option!

 

[ShaneMan]

Hey Pjaj,

Check into Append Queries and see if that doesn't get you started on the right track.

HTH,
Shane

 

[pjaj]

Thanks Shane, almost there.

That certainly looks the way to do it, but I'm getting a type conversion error on the very last step, the actual import. I'm still working on it.

Maybe it's because the table from which I'm trying to import is actually a link to the Excel spread sheet containing the new data, not a true Access table. Still it knows that there are 1173 records to import, if only it could do it!

Peter.

 

[neileg]

This may be because there's a mix of text and numeric data in a column. This often happens with links to Excel. Access determines the datatype on the basis of the first few records. And if the first few are text but record 24 is numeric, it fails.