Importing/Merging data help

[Darsarin]

HI,

First let me say my access knowledge is limited so bare with me. I have a simple 1 table DB that I am trying to import a excel sheet into.

Here is a screen shot of the DB.

and here is a shot of the excel sheet.

Both are lists of streets. The excel sheet is an updated list with all the streets in the DB plus some new ones. I want to import the excel sheet but exclude during the import all the streets that are already present in the db.

I have imported the excel sheet into a 2nd table called Streets and have tried to run an append query with no luck.

Here is the query I tried to use.

INSERT INTO msag (Street Name)
SELECT NewStreet Name FROM streets
WHERE NewStreet Name not in (SELECT msag.Street Name FROM msag);

If anyone could be of help it would be great.

 

[jackd]

Found a little info:

Validation rule violations

There are several places to look to solve for this one:

*

There is something in the Validation Rule of one of the fields, and the data you are trying to add does not meet this rule. The Validation Rule of each field is in the lower pane of table design window.
*

There is something in the Validation Rule of the table, and the data you are trying to add does not meet this rule. The Validation Rule of the table is in the Properties box.
*

The field has the Required property set to Yes, but the data has no value for that field.
*

The field has the Allow Zero Length property set to No (as it should), but the data contains zero-length-strings instead of nulls.

If none of these apply, double-check the key violations above.
Still stuck?

If the problem data is not obvious, you might consider clicking Yes in the dialog shown at the beginning of this article. Access will create a table named <b>Paste Errors or Import Errors or similar</b>. Examining the specific records that failed should help to identify what went wrong.

After fixing the problems, you can then import the failed records, or restore a backup of the database and run the complete import again.



Did you get a table created that contains details of the errors?

 

[jackd]

Darsarin,
Got some advice from colleague that may help.

Using a little Trial and Error.
Add one new record manually to the msag table using values from an existing record.

Replace field by field with values from the Streets table.

You should be able to open the msag table, then just type values in for a new record.
If there really is some rule violation, you may get a more meaningful error message.

You should put some characters such as XXX in some text field so that you can remove the record later.

Once you can add a new record without error, you can try to add one using the data from the Streets table.

Record what you do and what results you get.

Well I'm away for the day, be back online later.

Good luck.

 

[Darsarin]

Sorry it took so long to get back been busy. The lead,backup and rest of the fields in the msag were set to required. After is changed them it worked like a charm.

I guess I should have checked that sooner but I will know next time. Thanks to Jackd and Rconverse for all their help.

I think I have learned more about Access doing this one thing then I thought I would ever know.

Jackd thanks for the links for those Access videos. I'll watch them all.