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]

Are the field types in Streets also number for Low, high and text for the rest?

Dis you see the links for Access2007 training? I found these somewhat by accident. I don't know if you intend to learn 2007 but if so, then these might be useful. There's about 25 -30 slides per lesson.

I'm going to see if there's a way to find out what the validation rules are.

 

[Darsarin]

Are the field types in Streets also number for Low, high and text for the rest?

Dis you see the links for Access2007 training? I found these somewhat by accident. I don't know if you intend to learn 2007 but if so, then these might be useful. There's about 25 -30 slides per lesson.

I'm going to see if there's a way to find out what the validation rules are.

Streets fields are all text.

Yes I did see them thanks for those.

 

[jackd]

Streets fields are all text.

Yes I did see them thanks for those.

Can you go itno design of Streets table.
Change the field type for Low and High to Number or whatever it is in msag.

You may get a messgae that some data may be lost, but that should be OK.

Then run the Append query again.

 

[Darsarin]

Can you go itno design of Streets table.
Change the field type for Low and High to Number or whatever it is in msag.

You may get a messgae that some data may be lost, but that should be OK.

Then run the Append query again.

Made all the fields in the streets table match what is in the msag table. Ran it again and got the same error.

 

[jackd]

Made all the fields in the streets table match what is in the msag table. Ran it again and got the same error.

When you go to msag table and click on design, there is a set of properties in the lower part of the screen.
One of the properties is Validation Rule

Do you see any text or message there??

 

[Darsarin]

When you go to msag table and click on design, there is a set of properties in the lower part of the screen.
One of the properties is Validation Rule

Do you see any text or message there??

Nope blank in all fields

 

[jackd]

Nope blank in all fields

Are there other tables in the database?

Do you know if there are relationships defined within the database?

Not sure of Acc 2007, but there is likely a Menu item called Tools, if you click it it may show a Relationships entry, if so click it and see what's up. This will be on the main access screen.

 

[Darsarin]

Are there other tables in the database?

Do you know if there are relationships defined within the database?

Not sure of Acc 2007, but there is likely a Menu item called Tools, if you click it it may show a Relationships entry, if so click it and see what's up. This will be on the main access screen.

No other tables. Ran a Relationship report and it was blank.

 

[Darsarin]

/sigh just got a phone call and have to be at work 2 hrs earlier than normal so I have to hit the rack. Thanks for all your help today Jackd.

 

[jackd]

No other tables. Ran a Relationship report and it was blank.

I do not know what validation rule is being violated. It there is no explicit validation rule, and there are not relationships involving msag, then it may be something peculiar to Acc2007.

Just got your message.

I'm traveling tomorrow, but should be back in the evening.

I'd like to see what the issue is.

Keep the faith..