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]

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.
If anyone could be of help it would be great.

What is the error message you are getting?

A trick that is often used to prevent duplicates:
On your msag table, make [Street Name] the primary key. That means that you can only have one record in the table for 10TH AV SW .. etc.

If you try to add duplicate records, Access will tell you some records could not be inserted because of key violations. That's OK in this case, because you don't want duplicates.

Beware of field names with spaces in them. If you do use names with spaces, you must put the name inside square brackets [Street Name] - else Access rejects it .
You realize you are only adding the Street Name, no other fields -- is that what you want?

What fields did you import in to the second table (Streets)?

Does the Streets table actually have New Street Name as a field.

 

[Darsarin]

Hi Jackd,

I am getting this error: "Syntax Error in insert into statement"

Make Street Name primary Key, I can do that. Now in the db table there already some streets that have multiple entries. This is because some streets jet in and out of our jurisdiction and each time a street does this it gets a separate entry with a address range(the low & High columns) or the response to that street changes because it is so long.

Examples:

or

Now I can add the extra entries manually as they have to be verified one at a time anyway.


"You realize you are only adding the Street Name, no other fields -- is that what you want?"

Well I want all four columns of the excel sheet to import after I figure out how to do this.



"What fields did you import in to the second table (Streets)?"

I imported all 4 columns into the second table.




"Does the Streets table actually have New Street Name as a field?"

No it does not. I made the fields names match when I added the 2nd table.
The 2nd table only has 4 fields though. "Street Name, Low,High and EO"

 

[rconverse]

If a duplicate entry means a record has to have the same street name, High and low then all three need to be made into primary keys.

Are you writing this query? Why not use the built in query builder? I can't tell which table is which, but as jackd pointed out, you need brackets around any table or field name with a space. So for example...

table1.fieldA

'or

[table 1].[field a]

You're also only appending the one field (street) into the other table. I'm assuming that is not what you want to do.

 

[Darsarin]

ADVERTISEMENT

Are you writing this query? Why not use the built in query builder? I can't tell which table is which

You're also only appending the one field (street) into the other table. I'm assuming that is not what you want to do.

Unfortunately I am writing this query. I basically don't know what I am doing. I picked up that SQL query from my 1st post from someone else who was trying to help me.

I will use anything If it will get the job done.

I'm only appending one field right now just because I'm trying to figure this whole thing out(baby steps if you will).

 

[jackd]

Unfortunately I am writing this query. I basically don't know what I am doing. I picked up that SQL query from my 1st post from someone else who was trying to help me.

I will use anything If it will get the job done.

I'm only appending one field right now just because I'm trying to figure this whole thing out(baby steps if you will).

Darsarin,

I think we should go back to "what is the purpose of the database?" As rconverse said if you're going to have multiple records for Street names, then you will have to adjust the table - at least the primary key. The primary key is a unique identifier for each record in a given table.
If we understood what your application is for, we can probably give some advice.
I think we've been trying to answer specific questions out of context, and other factors have limited the usefulness of the advice given.

It sounds like this is your first database with Access. No problem, people here can help -- as long as they know what you are trying to build. It will also make the learning easier for you.

 

[Darsarin]

ADVERTISEMENT

Darsarin,

I think we should go back to "what is the purpose of the database?" As rconverse said if you're going to have multiple records for Street names, then you will have to adjust the table - at least the primary key. The primary key is a unique identifier for each record in a given table.
If we understood what your application is for, we can probably give some advice.
I think we've been trying to answer specific questions out of context, and other factors have limited the usefulness of the advice given.

It sounds like this is your first database with Access. No problem, people here can help -- as long as they know what you are trying to build. It will also make the learning easier for you.

Ok sounds like a good idea. As you can see in the 1st pic of my 1st post it's a simple 1 table db with a simple lay out. It has about 3800 records with all the streets we respond to.

We get the street name and adress range(low & high) info from AT&T. The db was made buy a guy who worked here 5 years ago(he no longer does). It has not been updated in about 3 years(the printed version has but more latter on that). They did the initial import and them went threw and added all the info for Lead, backup, backup2, service and Aerial(fields we added).

They then made 27 querys so they could print the info out alphabetically(the extra one for the numbered streets). This way when a few updates are done to lets say the streets starting with "S" they could just reprint those streets. This print-out is keep in a tabbed binder as a paper backup if the computers fail or we have to evacuate the building.

They paper backup has been updated by hand as new streets have been added to our area or any other changes needed to been made but the db it's self has not been. It's so far behind it would take forever to go threw and see what needs to be added or updated manually.

Thats why I am trying to import the latest excel file from AT&T and exclude any existing streets during the import. I would like to import all 4 columns from the excel file(seen in the 2nd pic of my 1st post)if I have to I can enter the Low, High and EO info manually if I get the new streets in.

Help any?

 

[rconverse]

Impoting should be fairly simple. Go to the file mene | Get External Data. Browse through the dialogue box until you find the Excel file you want to import. Follow the wizard and imprt the spreadsheet. For now that will be TableB. TableA will serve as the table already in the database that you want to append these new records to.

Now you have both tables in your DB, highlight the table that you just imported, TableB. Click on Insert (located on top menu) and choose query.

The query builder will appear. Select TableB from the dialog box that appears. Close the dialog box. Above the query builder change the query type from select to Append. You will be prompted to choose what table you'd like to append to (TableA). Next, highlight your four fields in TableB and drag them down to the query results section. The fields that you want to append to will automatically fill in because the field names are the same. Access is actually smart enough to recognize consistent fields in an append query. So when you drag down your fields from TableB, Access is smart enough to match those fields up to your TableA fields.

Here is a link to Access action queries. It covers more than just append, but about two minutes in or so, he tackles append queries. He doesn't show you how to get to the query builder though. To do that follow the steps I listed above (highlight table, insert, query).

http://www.youtube.com/watch?v=09FiAwFTVxI

HTH

Roger

 

[rconverse]

Oh and tackling duplicates. I'm still not totally clear on what constitutes a duplicate. For any combination of fields that can't be the same for a duplicate, then you need to make those fields a primary key.

It doesn't appear that street alone constitutes a duplicate. If a record has to have the same street, high, low and EO to make it a duplicate record, then you need to make all four fields primary keys in your TableA. If street and high are the only fields that constitute a duplicate record, then those two fields need to be primary keys in your TableA.

 

[jackd]

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.