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]

Oh and tackling duplicates. I'm still not totally clear on what constitutes a duplicate.

Hey,

If you look in my second post there are 2 pics those are both pic of entries in the db or Table A if you will.

When AT&T puts a new excel sheet out they just add to the old sheet so it contains all the streets we already have in the db plus the new ones. I don't want to import streets I already have in the db or Table A.

 

[rconverse]

Darsarin,

Totally agree with rconverse.
I do have a couple of questions though related to your application.

Do these Street addresses hi and Lo constitute your zip + 4 or something? I'm trying to figure out why AT&T is the source for updates?

If ATT data is good, is it possible to replace your tables with the latest data from ATT? That's not a suggestion, just a question.

It looks like there are other fields in the table in the database. With the AT&T file, there are only four fields. I am not sure how the other fields in the database table are populated, but that would prohibit replacing the table with the new ATT file, I think.

Hey,

If you look in my second post there are 2 pics those are both pic of entries in the db or Table A if you will.

When AT&T puts a new excel sheet out they just add to the old sheet so it contains all the streets we already have in the db plus the new ones. I don't want to import streets I already have in the db or Table A.

I see the multiple records with the same street names. Are you saying you want only one record for every street? How do you decide which record to keep?

If you're saying you only want to add records with a combination of street name and high that are unique, you need to set those two fields to primary keys.

If you're saying you only want to add records with a combination of street name, high and low that are unique, you need to set those three fields to primary keys.

 

[Darsarin]

Darsarin,

Totally agree with rconverse.
I do have a couple of questions though related to your application.

Do these Street addresses hi and Lo constitute your zip + 4 or something? I'm trying to figure out why AT&T is the source for updates?

If ATT data is good, is it possible to replace your tables with the latest data from ATT? That's not a suggestion, just a question.

1st I guess I should have said this all has to do with E911 stuff. AT&T is the source because this is a dump of their ANI/ALI database. Automatic Number Identification (ANI) and Automatic Location Identification (ALI).

Phone companies in the US run these databases and 911 centers poll these DB's to get a 911 callers name and address when they call 911. Some centers like our own manage the ANI/ALI db in-house and AT&T sends us the updates to the DB.

I work for the Fire Dept. and get the AT&T info from the guy who does the mapping for the CAD(computer aided dispatch) system. The map data excludes names and phone #'s and just has street names and address ranges.

I'll talk about the data below.


The Low and High are addresses ranges.

If you look above you see 10th AV SW has a range of 2415 - 3906. This means the lowest address on this street is 2415 and the highest address on the street is 3906. So if someone calls and says I'm at 2300 10th AV SW I know that A. this person does not know where he/she is or B. This might be a new address we don't know about so you better get good location info from the caller or C. If the caller is using a cell phone the call may have been routed to the wrong 911 center(it happens but not often).

The EO field is for even or odd. Some streets are on the edge of our response area so one side of the street might be ours and another side might not be. In those cases the entried would look like this:

If you look at the 2nd Balch Rd. entry you will see a address range of 1360 - 1456 E. This means we cover the houses in that range that have even numbers only. The houses with an odd address lets say 1369 Balch Rd would not be ours because it is an odd number and would but in on the other side of the road. When we run into this we have to make a separate entry for that street ever time it happens. This is were OUR duplicate entries come from.



The file we get from AT&T has about 15 columns in it and has every phone # in our county in it. I delete all the columns I don't need and delete all the streets that we don't cover and it leaves me with this:

We add the columns Lead, Backup, Backup2, Service and Aerial and input the appropriate data for those fields. If I just dump our table and import AT&T's data to a new able I would have to enter all of our data all over again. If you look up 2 pics you will see our data ie: 1A, 1B, 3, 1B and L6

 

[rconverse]

Okay, so what if you get a file like this...

10th ST SW 1200 3000 E


But your DB has this:

10th ST SW 1000 3000 E

Do you want to remove the first record and replace it with the new one? I'm thinking that street and EO would be primary keys. Your thoughts, jackd?

 

[rconverse]

Are all of these legitimate records? If so, you'll want to make all four of your fields primary keys.

 

[Darsarin]

Okay, so what if you get a file like this...

10th ST SW 1200 3000 E


But your DB has this:

10th ST SW 1000 3000 E

Do you want to remove the first record and replace it with the new one? I'm thinking that street and EO would be primary keys. Your thoughts, jackd?

Address range changes happen very rarely and when they do normally happen in new subdivisions so we get that data from the map guy and not AT&T and update it manually.

 

[Darsarin]

Are all of these legitimate records? If so, you'll want to make all four of your fields primary keys.

Yes all those are legitimate.

I can make the 'Street Name" column in the db a primary key and allow duplicates but will that effect anything when we do the append query?

 

[Darsarin]

I guess it kind of weird. We took fresh data with no duplicates, made a db out of it and added our own duplicate entries. Received fresh data, want to import it into our current data but only new streets don't import any street we already have in our db, but might end up making our own duplicate entries of the new data we import if needed.

 

[rconverse]

Yes all those are legitimate.

I can make the 'Street Name" column in the db a primary key and allow duplicates but will that effect anything when we do the append query?

I think you need to set all four of the fields in your DB table (TableA) primary keys.

Let's say you have this record in the that table in the DB (TableA)

StreetA 100 2000

Then on the new file you have these records in your AT&T table (TableB)

StreetA 10 90 E
StreetA 2400 3000 O
StreetA 100 2000
StreetA 2560 2800 E

If you appended that data, three records would apppend. The only record that would not append would be the bolded one.

 

[Darsarin]

I think you need to set all four of the fields in your DB table (TableA) primary keys.

Let's say you have this record in the that table in the DB (TableA)

StreetA 100 2000

Then on the new file you have these records in your AT&T table (TableB)

StreetA 10 90 E
StreetA 2400 3000 O
StreetA 100 2000
StreetA 2560 2800 E

If you appended that data, three records would apppend. The only record that would not append would be the bolded one.

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.