Query criteria syntax question

[Darsarin]

Hi,

First I want to apologize for not searching for the answer but I am not knowledgeable enough to do a proper search so forgive my forum etiquette fopah.

I have a simple Access DB with one table and 8 columns and just shy of 3800 records.

I have imported a coma delimited file into a second table with updated info formatted the same way. The 1st column is all text and consists of all street names in our city.

I am trying to use a query to import the new table into the existing table but exclude existing street names and only append the new street.

I am having trouble finding info on the net about the proper syntax and format of the criteria I should use to accomplish this.

Thanks in advance for any help.

 

[duluter]

Hi.

Let's try this:

First, create a union query that joins all of the street names into one result set:

SELECT t1.Streets as AllStreets FROM t1
UNION SELECT t2.Streets as AllStreets FROM t2;

Call this Qry1.


Now we're going to create an insert query to insert these streets into your t1 table, but only if the street isn't already there. Like this:

INSERT INTO t1 (Streets)
SELECT AllStreets
FROM Qry1
WHERE AllStreets not in (SELECT t1.Streets FROM t1);


You can combine this into one master query like this:

INSERT INTO t1 (Streets)
SELECT AllStreets
FROM (SELECT t1.Streets as AllStreets FROM t1
UNION SELECT t2.Streets as AllStreets FROM t2)
WHERE AllStreets not in (SELECT t1.Streets FROM t1);


I think that should work.


Duluter

 

[Darsarin]

Duluter,

Thanks for the reply I will give it a try and see what I blow up.



Darsarin

 

[Brianwarnock]

Just make the field containing the street names a unique index ie no dups in the design of the table, then when you run an append query no dups will be permitted. The system will tell you how many failed.

If you all ready have dups it wont allow the change and you will need to run a find dups query to help you remove them, or a make table query to a new table with the condition set.

Brian

 

[wiklendt]

...and always have a backup of your database that you can revert to if things go horribly wrong.

 

[Darsarin]

...and always have a backup of your database that you can revert to if things go horribly wrong.

I have several backups in different places.

I have many dupes already because some streets go in and out of the city limits so their is and entry for each time that happens with an address range.
_______
I.E.

Smith St| 100 - 200
Smith St| 250 - 300
Smith St| 375 - 800
__________________

One street has 8 entries. Those we check manually every update to see if any of the address ranges have changed since their are only about 20 of them.

 

[duluter]

Hmm, I'm not really sure what I was thinking earlier with the union query.

INSERT INTO t1 (Streets)
SELECT NewStreets FROM t2
WHERE NewStreets not in (SELECT t1.Streets FROM t1);

Does this work?


Duluter

 

[Brianwarnock]

I have several backups in different places.

I have many dupes already because some streets go in and out of the city limits so their is and entry for each time that happens with an address range.
_______
I.E.

Smith St| 100 - 200
Smith St| 250 - 300
Smith St| 375 - 800
__________________

One street has 8 entries. Those we check manually every update to see if any of the address ranges have changed since their are only about 20 of them.

How does this tie in with your demand that the new additions must have no dupes?

Brian

 

[Darsarin]

Hmm, I'm not really sure what I was thinking earlier with the union query.

INSERT INTO t1 (Streets)
SELECT NewStreets FROM t2
WHERE NewStreets not in (SELECT t1.Streets FROM t1);

Does this work?


Duluter

I'm Back...Sorry it took so long to reply have been busy with other things. I tried the above and got a "Syntax Error in insert into statement".

If it helps any here is a shot of the DB.

and a shot of the excel file I'm importing and trying to insert into the table.

msag is the name of the table in the DB(it's not much of a db just 1 table.)
I imported the excel sheet into a table called Streets.

I formatted the query like this

INSERT INTO msag (Street Name)
SELECT NewStreet Name FROM streets
WHERE NewStreet Name not in (SELECT msag.Street Name FROM msag);