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]

Ran this, had to put a space between is and null to get it to run. gave be a field titled "Expr1000" and it had 0 IN IT.

OK, the proper syntax Is Null , my mistake.

The count of 0 means there are no records with Low or High with no values.

So you should be able to run the procedure to create the Primary key.


Also here's a link to some video tutorials for Access 2007

http://www.ehow.com/video_4801727_create-sql-query-microsoft-access.html

you may have to search around for what you're interested in.

 

[Darsarin]

Oh what about the streets with multiple entries like this? Would that be the source?

 

[Darsarin]

Hey what if instead of trying to import the new table we do a Delete query and delete all the streets in the new table that are already in the msag table. Would that work?

 

[jackd]

Oh what about the streets with multiple entries like this? Would that be the source?

Are you able to run the createKey procedure?

These records are all different as long as [Street Name], Low and High are not repeated.

Here's another query you could run to see if there are duplicates.

SELECT msag.[street name], msag.low, msag.high
FROM msag
GROUP BY msag.[street name], msag.low, msag.high
HAVING (((Count(*))>1));

Any results here are for records that have more than 1 occurrence
of the [Street Name] +Low + High

 

[Darsarin]

DID IT! Found a street that was a true Duplicate and deleted one of them. Then ran the script to make the composite key!

 

[jackd]

DID IT! Found a street that was a true Duplicate and deleted one of them. Then ran the script to make the composite key!

So now you need to run the append query that

appends records to your msag table, using records in your Streets table,
where the streets records will not duplicate existing msag records.

Since you have a primary key defined to prevent duplicates, Access will tell you if there are key violations where you run your append query.

INSERT INTO msag ( [street name], low, high )
SELECT Streets.[street name], Streets.low, Streets.high
FROM Streets;

That should do it.

 

[Darsarin]

Ran it and got this.

Clicked yes and it did not add anything.

 

[jackd]

Ran it and got this.

Clicked yes and it did not add anything.

It is saying there are validation rules and these rules were not met.

Did you create the msag table?
Can you find out what rules exist for this table?
Does Streets have 3814 records?

What data types are your fields in msag?

When you go to table Design for msag, are the fields all text or integer etc?

If you run the proc I sent that listed the keys in the immediate window, does it show the composite key on msag?

Do you have any documentation for msag?

 

[jackd]

Here are several links that lead to 12 powerpoint presentations for learning Access 2007.

http://oc.course.com/np/Office2007/data/vista/Access.01.ppt
http://oc.course.com/np/Office2007/data/vista/Access.02.ppt
http://oc.course.com/np/Office2007/data/vista/Access.03.ppt
http://oc.course.com/np/Office2007/data/vista/Access.04.ppt
http://oc.course.com/np/Office2007/data/vista/Access.05.ppt
http://oc.course.com/np/Office2007/data/vista/Access.06.ppt
http://oc.course.com/np/Office2007/data/vista/Access.07.ppt
http://oc.course.com/np/Office2007/data/vista/Access.08.ppt
http://oc.course.com/np/Office2007/data/vista/Access.09.ppt
http://oc.course.com/np/Office2007/data/vista/Access.10.ppt
http://oc.course.com/np/Office2007/data/vista/Access.11.ppt
http://oc.course.com/np/Office2007/data/vista/Access.12.ppt

 

[Darsarin]

It is saying there are validation rules and these rules were not met.

Did you create the msag table?
Can you find out what rules exist for this table?
Does Streets have 3814 records?

What data types are your fields in msag?

When you go to table Design for msag, are the fields all text or integer etc?

If you run the proc I sent that listed the keys in the immediate window, does it show the composite key on msag?

Do you have any documentation for msag?

Nope I did not create it.


No clue on any rules.


Streets does have 3814 records


Fields in msag: Low and High are Number fields, all the rest are text.

Comp key on msag: Yes it does.

No Documentation