A
albert.repasky
I am importing data into the database, because the HR does not want to
imput data they already have in an Excel speadsheet. Since the DB
using ID's that point at other tables, I have to satisfy and find the
ID's for the names of things like Building. But since the
CityBuilding table has 3 id's for the primary key, I am having trouble
adding new names if the building name is not already there.
The first query goes through and find all the building name and sets a
switch "Dup" if it is already in the table or not. The second query
is supposed to add the name to the table if Dup is set to 0 and not
1. This works for other table, but not this because of the 3 ID's
that our the primary key. I have no idea how to tackle this.
The queries and indexes our below.
There is also a City table with fldLocationID and fldCityID as the
primary key.
I hope I included enough info for someone to help me. If I have not
please ask for what else you need.
Thanks ahead of time.
Arep
First Query runs
SELECT DISTINCT tblCity.fldLocationID, tblCity.fldCityID,
Import_OpenReqs.[Address 1], IIf([Address 1]=[fldBuildingDesc] And
[City]=[fldCityName],1,0) AS Dup
FROM (Import_OpenReqs LEFT JOIN tblCityBuilding ON Import_OpenReqs.
[Address 1] = tblCityBuilding.fldBuildingDesc) LEFT JOIN tblCity ON
Import_OpenReqs.City = tblCity.fldCityName;
Then 2nd query runs
INSERT INTO tblCityBuilding ( fldLocationID, fldCityID,
fldBuildingDesc )
SELECT qryOpenReq_Bldg1.fldLocationID, qryOpenReq_Bldg1.fldCityID,
qryOpenReq_Bldg1.[Address 1]
FROM qryOpenReq_Bldg1
WHERE (((qryOpenReq_Bldg1.Dup)<>1));
The primary key to the table is: fldLocationID,fldCityID,fldBuildingID
imput data they already have in an Excel speadsheet. Since the DB
using ID's that point at other tables, I have to satisfy and find the
ID's for the names of things like Building. But since the
CityBuilding table has 3 id's for the primary key, I am having trouble
adding new names if the building name is not already there.
The first query goes through and find all the building name and sets a
switch "Dup" if it is already in the table or not. The second query
is supposed to add the name to the table if Dup is set to 0 and not
1. This works for other table, but not this because of the 3 ID's
that our the primary key. I have no idea how to tackle this.
The queries and indexes our below.
There is also a City table with fldLocationID and fldCityID as the
primary key.
I hope I included enough info for someone to help me. If I have not
please ask for what else you need.
Thanks ahead of time.
Arep
First Query runs
SELECT DISTINCT tblCity.fldLocationID, tblCity.fldCityID,
Import_OpenReqs.[Address 1], IIf([Address 1]=[fldBuildingDesc] And
[City]=[fldCityName],1,0) AS Dup
FROM (Import_OpenReqs LEFT JOIN tblCityBuilding ON Import_OpenReqs.
[Address 1] = tblCityBuilding.fldBuildingDesc) LEFT JOIN tblCity ON
Import_OpenReqs.City = tblCity.fldCityName;
Then 2nd query runs
INSERT INTO tblCityBuilding ( fldLocationID, fldCityID,
fldBuildingDesc )
SELECT qryOpenReq_Bldg1.fldLocationID, qryOpenReq_Bldg1.fldCityID,
qryOpenReq_Bldg1.[Address 1]
FROM qryOpenReq_Bldg1
WHERE (((qryOpenReq_Bldg1.Dup)<>1));
The primary key to the table is: fldLocationID,fldCityID,fldBuildingID