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.

 

[Darsarin]

I guess I should also say that if the street is already in our db I don't care about the low, high and EO data in the AT&T file. That data only matters if it's a new street we don't currently don't have in our db.

 

[jackd]

I guess I should also say that if the street is already in our db I don't care about the low, high and EO data in the AT&T file. That data only matters if it's a new street we don't currently don't have in our db.

Hi guys,

I've been out for a while, just got back.
In answer to rconverse, it seems to me that all 4 fields would be part of the primary key. However, you can not have a NULL component of a primary key, and since the EO is not always populated that could be a problem.

This statement confuses me a little - " if the street is already in our db I don't care about the low, high and EO data in the AT&T file". What if you have a street in your database, but the ATT has different house number range and an E/O entry, wouldn't you want to update your database?

I think you're telling us that all street entries in your database have a Hi Lo range. Is that correct?

 

[rconverse]

I see what you are saying. Did i mention that the file from AT&T will not any street listed more that once other than lets say..

Street A Dr
Street A Cir.

Feel like a dentist yet? It's like pulling teeth.

If these are the same street, then you are going to have issues.

Hi guys,

I've been out for a while, just got back.
In answer to rconverse, it seems to me that all 4 fields would be part of the primary key. However, you can not have a NULL component of a primary key, and since the EO is not always populated that could be a problem.

This statement confuses me a little - " if the street is already in our db I don't care about the low, high and EO data in the AT&T file". What if you have a street in your database, but the ATT has different house number range and an E/O entry, wouldn't you want to update your database?

I think you're telling us that all street entries in your database have a Hi Lo range. Is that correct?

Excellent point. I completely forgot about that.

 

[Darsarin]

This statement confuses me a little - " if the street is already in our db I don't care about the low, high and EO data in the AT&T file". What if you have a street in your database, but the ATT has different house number range and an E/O entry, wouldn't you want to update your database?

I think you're telling us that all street entries in your database have a Hi Lo range. Is that correct?

A. yes I don't care about low high updates from AT&T. I can get those from the map guy in a MUCH small list that I can update myself.


B. There are some of the older streets in the city that don't have low high data in the db. The data exists but there is no need to have it in the hard copy backup.


All I really care about is getting the new streets(with the corresponding low,high date) into our db without having go threw it manually street by street till I find a new one.

 

[Darsarin]

If these are the same street, then you are going to have issues.

Not the same street but have the same name ie:

Johnathan Dr.
Johnathan Cir.

or

10th Ave
10th St.

 

[jackd]

If these represent the same thing
Street A Dr
Street A Cir.
then there are other problems.

as for primary key, I think you could have StreetName, Lo and Hi as a composite key.
Even if there is Odd or Even associated with the street, the low number would be different.

I'm not sure what sort of activity occurs, but
Consider:
Apple St NW 200 880

new record comes in

Apple St NM 203 779 O


would you add the new record and adjust the existing record to
Apple St NW 200 880 E.

 

[rconverse]

If these represent the same thing
Street A Dr
Street A Cir.
then there are other problems.

as for primary key, I think you could have StreetName, Lo and Hi as a composite key.
Even if there is Odd or Even associated with the street, the low number would be different.

I'm not sure what sort of activity occurs, but
Consider:
Apple St NW 200 880

new record comes in

Apple St NM 203 779 O


would you add the new record and adjust the existing record to
Apple St NW 200 880 E.

Exactly what I was thinking.

 

[Darsarin]

If these represent the same thing
Street A Dr
Street A Cir.
then there are other problems.

as for primary key, I think you could have StreetName, Lo and Hi as a composite key.
Even if there is Odd or Even associated with the street, the low number would be different.

I'm not sure what sort of activity occurs, but
Consider:
Apple St NW 200 880

new record comes in

Apple St NM 203 779 O


would you add the new record and adjust the existing record to
Apple St NW 200 880 E.

Street A Dr. and Street A Cir. do not represent the same thing they are two separate streets.


In the Apple St NM(assuming you ment NW and not NM) instance I would have gotten that info from the map guy already or would get it at the same time this file comes out and fix it manually. So I would rather just have the append query skip it altogether.

 

[jackd]

Street A Dr. and Street A Cir. do not represent the same thing they are two separate streets.


In the Apple St NM(assuming you ment NW and not NM) instance I would have gotten that info from the map guy already or would get it at the same time this file comes out and fix it manually. So I would rather just have the append query skip it altogether.

Yes it was supposed to be NW.
Why skip the record if it adds detail to your database?

 

[Darsarin]

Yes it was supposed to be NW.
Why skip the record if it adds detail to your database?

Because we get address range data updates from the map guy. Hey is Faster and more accurate that AT&T since he pulls from AT&T, Util Company and other city departments.