Import selected into existing records

  • Thread starter Thread starter Beth
  • Start date Start date
B

Beth

This is probably a simple question, but the answer eludes me, so I am
putting out there for the experts.

I have a table with about 20 fields, ClaimNumber as the key. We have the
claims in the database and we have made notes and other information on them,
but the claim expense field is not populated.
Once a month I am provided an Excel spreadsheet with the claim numbers and
their claim expense. That spreadsheet could also include some claims that
have previously not existed in the database, but need to be tracked starting
then.
How can I import the data from the spreadsheet into existing records, but
not lose the manually populated information?

I am open to any suggestions.

Thanks in advance.
Beth
 
Beth:
You could import the spreadsheet with the amounts into a separate Access
table. Then you could do an update query, joining the two tables on
ClaimNumber. Upate the Amount field in the first table with the amount in the
second table, setting the Criteria to IS NULL for the Amount field in the
first table. That way you will only update the amount for records that do not
have any amount.

You could then do an Append query for all the records in the claims table
that have a ClaimNumber that doesn't exist in the first table.
 
Hi,

I have similar problem and having pain from last few days. I have thousands
of data in several different excel file and all of them have 30 same fields.
Every time I import in access, I can import in new table. But I would like
to import all the excel files in to the EXISTING TABLE so I can have all the
data in one place. At the same time I need to import more excel file in to
the EXISTING TABLE in the future too.

Problem comes at the end of impoting step. It lets me to choose EXISTING
TABLE. When I click next after Import to Table "X" Import Spreadsheet Wizard
shows error message saying:
"An error occurred trying to import file 'C:\Documents and
Setting...........xls'. The file was not imported"

Thanks in advance
Ramesh
 
Hi Ramesh,

You can only import data directly from an Access sheet into an existing
table if (a) the number of columns and the data types in Excel exactly
match the number of fields and their data types in Access, and (b) the
column headers in Excel exactly match the Access field names. In
addition, of course, the data to be imported mustn't violate any of the
table's constraints (indexes, relationships, validation rules).

Remember that when you tell Access to import data from a worksheet it
attempts to import the worksheet's entire UsedRange. If parts of the
worksheet outside the range occupied by your table have been used for
other purposes, or formatted, the UsedRange may be bigger than the table
and the import will fail. To avoid this problem, you can define the
table area as a named range and import that.

If problems continue, use a linked table to access the worksheet data,
or import it to a temporary table. Then use an append query to move the
data into the table where you want it.
 
Back
Top