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]

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.

If the map guy is more accurate and faster, and gets data from ATT, utils etc, Why do you also get data from ATT? I'm just trying to sort out the sourcing of data. Wouldn't the map guys data (from ATT) be more up to date than your data from ATT?

Seems like potentially a duplication of work?

Does the map guy give you data in an electronic form?

 

[Darsarin]

If the map guy is more accurate and faster, and gets data from ATT, utils etc, Why do you also get data from ATT? I'm just trying to sort out the sourcing of data. Wouldn't the map guys data (from ATT) be more up to date than your data from ATT?

Seems like potentially a duplication of work?

Does the map guy give you data in an electronic form?

Once I do this large update we I can keep it up to date with the smaller updates he gives out.

 

[jackd]

Once I do this large update we I can keep it up to date with the smaller updates he gives out.

OK,

so I think you can make

Street Name, Lo and Hi a composite primary key to your Table.

You may want to start by making a Backup copy of your Table first.
You can do this by going to the main screen of Access, Table tab.

Highlight your msag Table, then go to menu click Copy, then click Paste

It will ask for a name for the new table -- call it something like BackupOfMsag

Then, make [Street Name], Low and High the primary key.

To do this, select table msag, go to design view
holding down the ctrl key
select Street Name, then Low, then High by clicking the mouse when you're in the leftmost column next to Street Name, then Low then High.
Then go to the menu and click on the Key Symbol

That should get you the primary key.
Next is the query using your msag and streets tables.
You should use the query builder to create the query.

I think it will look something like this.

INSERT INTO msag ([Street Name], low, high)
SELECT streets.[Street Name],streets.Low, streets.High FROM streets;

This may give you key violations, but those would be duplicates which you don't want.

Have to go. Will be online later.

 

[Darsarin]

make [Street Name], Low and High the primary key.

Tried this and got:

Index or primary key cannot contain a Null value.

Guessing this is due to some of the low,High fields being empty.


I also tried to just make the Street name field Primary and got this:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Going to bed got work in the morning.

 

[jackd]

Tried this and got:



Guessing this is due to some of the low,High fields being empty.


I also tried to just make the Street name field Primary and got this:



Going to bed got work in the morning.

Yes the message indicates that one of the fields is empty.

The primary key must include [Street Name], low and High.

You can find which it is by doing a Select query

Select * from msag where
Low isNull Or High IsNull.

 

[Darsarin]

Yes the message indicates that one of the fields is empty.

The primary key must include [Street Name], low and High.

You can find which it is by doing a Select query

Select * from msag where
Low isNull Or High IsNull.

I'll go threw and populate the empty ones. Will it matter what I numbers I populate them with?

0 for low & high or do I need to make sure the high value is larger then the low value for the final import?

 

[jackd]

I'll go threw and populate the empty ones. Will it matter what I numbers I populate them with?

0 for low & high or do I need to make sure the high value is larger then the low value for the final import?

It doesn't matter to me or to Access, but this is to support 911 emergency so I suspect the numbers should be accurate / as good as you can get.

Can you get High Low numbers for the records in question from the map guy?
How many records have "no High or Low"?

 

[Darsarin]

It doesn't matter to me or to Access, but this is to support 911 emergency so I suspect the numbers should be accurate / as good as you can get.

Can you get High Low numbers for the records in question from the map guy?
How many records have "no High or Low"?

I'll just populate both fields with 0's and go back later and plug in the correct info so Ican get the import done.

 

[jackd]

I'll just populate both fields with 0's and go back later and plug in the correct info so Ican get the import done.

Sounds reasonable since I don't think street numbers anywhere start at 0.

How many such records?

 

[Darsarin]

Done, not many of them. Still getting:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

I tried Street name, low and High all individually and got the error on all fields.