Do not import records already existing in the database

[Guest]

I need to import data from an excel sheet to a table in access. The excel
sheet may contain records that are already in the database. While importing,
the records that are already in the database must not be imported.
My database has two columns - Card number & Status
While importing, access must look for a match in the card number column, if
the match exists, then a second comparison must be done with the Status
column. If the status column also matches, then the record must not be
imported.
However, if the card number matches and the status does not match, then the
record must be imported.
Records wherein the card number does not match must also be imported.

Scenario (excel sheet)
Card number Status
1 Closed
2 Open

Table (Access)
Card number Status
1 Closed
2 Closed
From the above example, when importing only the record for card number 2
must be imported.
I am not good at coding.

Sashi

 

[tina]

in the Access table, set a unique index consisting of both fields. make sure
that each *individual* field's Index property is set to No or Yes
(Duplicates OK). only records that do not violate the index will be added to
the table.

hth

 

[Guest]

Hi Tina..
Thank you for your response. Tried it out..but does not seem to work.
I set the Index criteria for both the card # & Status field to "Yes
(Duplicates OK)".
And tested by importing 4 records as mentioned below:
Card # Status
01002 C
42002 O
42003 C
32002 C
The result was to import only the record for card 42002. The access table
contained all four card numbers, but since only 42002 had a different status,
only this record had to be imported. However, only record 01002 was imported
which should not have been the case. Please assist.

Sashi

 

[tina]

I set the Index criteria for both the card # & Status field to "Yes

(Duplicates OK)".

okay, but did you also set a unique index consisting of *both* fields, as i
instructed before? to do that, open the table in Design view and click the
Index button on the toolbar. in the Indexes box, in the first blank line in
the left column, type "TwoField" and in the right column add the card number
fieldname. in the Index Properties section at the bottom of the box, set the
Unique property to Yes. go back to the next blank line, and *skip* the left
column; in the right column add the Status fieldname.

hth

 

[Guest]

Hey Tina..
Tried this..but i receive an error mssg while saving the design changes to
the table(shown below)..Please help..
"The changes you requested on the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again"

Sashi

 

[tina]

well, then, you already have duplicate records in the table. suggest you
make a copy of the database, delete all the records from the table in the
copy, set the indexes as i suggested, then try your import(s) again.

hth