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]

Get a compile error User-defined type not defined

and high lights this: Dim db As DAO.Database

This indicates a missing reference.

On the Modules screen where you see the program code, on the menu at the top there is an Entry called Tools, click it,
that should drop down a list-- you're looking for References
in the list you'll see one called Microsoft DAO 3.6 Object Library
click the little box to the left (to set the checkmark),
Then to the right of the references there is an UP ARROW
click it or hold it down-- This will move the DAO reference higher up-- you want it as high as possible.

Once you've done that try to run the procedure again.

 

[jackd]

Any luck?

 

[Darsarin]

This popped into the intermediate window.

List Tables with Indexes and/or
Primary Key and Display Components


\\Admin4\hfd shared\MSAG\hfdesz.mdb

Table



msag Index = Street Name
Contains field: Street Name

 

[jackd]

This popped into the intermediate window.

Yes that's expected. The procedure lists the indexes for all tables in the immediate window.

So it looks like Street Name is an index but not a primary key.

Sub CreateKey()
Dim db as DAO.database
Dim Sql as string
sql ="CREATE INDEX PrimaryKey ON msag ([Street Name], Low, High) WITH PRIMARY"
set db = current.db
db.execute sql
end sub

You can copy this procedure and run it also, if you have made sure that all Low and High have values.

 

[jackd]

This is corrected procedure

Sub CreateKey()
Dim db as DAO.database
Dim Sql as string
sql ="CREATE INDEX PrimaryKey ON msag ([Street Name], Low, High) WITH PRIMARY"
set db = currentdb '<<<<<<< no . here
db.execute sql
End sub

This will create the composite (multi field) primary key

 

[Darsarin]

Run-time error 3022

says they same thing about duplicate values in the index, key etc..etc..

 

[jackd]

Run-time error 3022

says they same thing about duplicate values in the index, key etc..etc..

Did you put values in for all the records that had no Low or High?

You'll need a query to find out which records have Null values.

SELECT count(*) from msag
where Low isNULL or High isNULL

 

[Darsarin]

Yeah because before when I tried to make the Comp Key it gave me a can't have a null error. After I filled them all in is when it went back to the no dupes error.


Wounder if access is seeing Smith St and Smith Rd. as a duplicate entry?

 

[jackd]

Yeah because before when I tried to make the Comp Key it gave me a can't have a null error. After I filled them all in is when it went back to the no dupes error.


Wounder if access is seeing Smith St and Smith Rd. as a duplicate entry?

No they will be seen as different.



Have you created a query in Access?
See this link http://www.ehow.com/how_2125020_use-access-2007-query-wizard.html

 

[Darsarin]

SELECT count(*) from msag
where Low isNULL or High isNULL

Ran this, had to put a space between is and null to get it to run. gave be a field titled "Expr1000" and it had 0 IN IT.