Adding new records with 3 relationships

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

It all starts with the data ... and I don't have a very clear picture of
your data yet.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

wrote in message
...
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

I believe your main problem is with understanding primary keys. Very seldom
does a table need more than one field for a primary key.
A primary key defines a record as a unique entity. For example, your
example of a City table with fldLocationID and fldCityID as the primary key
is very suspicious.
A City is a city regardless of location. The location may give you
information about the city, but the location is not the defining attribute of
a city. Even a city's name is not a defining attribute. The location might
help distinguish between Rome, Italy, and Rome, Texas, but just Rome is
ambigious. This is one reason why using a textual description of a thing is
not reliable as a primary key. Another is the name of a thing can change.
The city of Bombay was recently renamed Mumbai. That would cause havoc in a
relational database were there any child records to the city table. If,
instead, you use an Autonumber primary key, you would only have to change
the city name. Any related records in other tables would be unaffected.

Now, that doesn't mean some things that are not part of the primary key
can't be required to be unique. For example, a Social Security Number
should only be held by one person, so if you create a unique index on Social
Security Number, you will ensure a duplicate value is not entered and it
makes it faster to search on that field.

The case where you have 3 fields in the primary key is almost certainly
incorrect.

The short of it is that you need to reevalute what your primary keys should
be and redesign your database structure before you go any further.
--
Dave Hargis, Microsoft Access MVP


" wrote:

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

On Jul 10, 2:51*pm, Klatuu wrote:
I believe your main problem is with understanding primary keys. *Very seldom
does a table need more than one field for a primary key.
A primary key defines a record as a unique entity. *For example, your
example of a City table with fldLocationID and fldCityID as the primary key
is very suspicious.
A City is a city regardless of location. *The location may give you
information about the city, but the location is not the defining attribute of
a city. *Even a city's name is not a defining attribute. *The location might
help distinguish between Rome, Italy, and Rome, Texas, but just Rome is
ambigious. This is one reason why using a textual description of a thing is
not reliable as a primary key. *Another is the name of a thing can change. *
The city of Bombay was recently renamed Mumbai. *That would cause havoc in a
relational database were there any child records to the city table. *If,
instead, *you use an Autonumber primary key, you would only have to change
the city name. *Any related records in other tables would be unaffected..

Now, that doesn't mean some things that are not part of the primary key
can't be required to be unique. *For example, *a Social Security Number
should only be held by one person, so if you create a unique index on Social
Security Number, you will ensure a duplicate value is not entered and it
makes it faster to search on that field.

The case where you have 3 fields in the primary key is almost certainly
incorrect.

The short of it is that you need to reevalute what *your primary keys should
be and redesign your database structure before you go any further.
--
Dave Hargis, Microsoft Access MVP



" wrote:
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- Hide quoted text -

- Show quoted text -

Is there something in your reply I missed?
What I got back was just what I posted.
--
Dave Hargis, Microsoft Access MVP


" wrote:

On Jul 10, 2:51 pm, Klatuu wrote:
I believe your main problem is with understanding primary keys. Very seldom
does a table need more than one field for a primary key.
A primary key defines a record as a unique entity. For example, your
example of a City table with fldLocationID and fldCityID as the primary key
is very suspicious.
A City is a city regardless of location. The location may give you
information about the city, but the location is not the defining attribute of
a city. Even a city's name is not a defining attribute. The location might
help distinguish between Rome, Italy, and Rome, Texas, but just Rome is
ambigious. This is one reason why using a textual description of a thing is
not reliable as a primary key. Another is the name of a thing can change.
The city of Bombay was recently renamed Mumbai. That would cause havoc in a
relational database were there any child records to the city table. If,
instead, you use an Autonumber primary key, you would only have to change
the city name. Any related records in other tables would be unaffected..

Now, that doesn't mean some things that are not part of the primary key
can't be required to be unique. For example, a Social Security Number
should only be held by one person, so if you create a unique index on Social
Security Number, you will ensure a duplicate value is not entered and it
makes it faster to search on that field.

The case where you have 3 fields in the primary key is almost certainly
incorrect.

The short of it is that you need to reevalute what your primary keys should
be and redesign your database structure before you go any further.
--
Dave Hargis, Microsoft Access MVP



" wrote:
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- Hide quoted text -

- Show quoted text -

On Jul 10, 4:46*pm, Klatuu wrote:
Is there something in your reply I missed?
What I got back was just what I posted.
--
Dave Hargis, Microsoft Access MVP



" wrote:
On Jul 10, 2:51 pm, Klatuu wrote:
I believe your main problem is with understanding primary keys. *Very seldom
does a table need more than one field for a primary key.
A primary key defines a record as a unique entity. *For example, your
example of a City table with fldLocationID and fldCityID as the primary key
is very suspicious.
A City is a city regardless of location. *The location may give you
information about the city, but the location is not the defining attribute of
a city. *Even a city's name is not a defining attribute. *The location might
help distinguish between Rome, Italy, and Rome, Texas, but just Rome is
ambigious. This is one reason why using a textual description of a thing is
not reliable as a primary key. *Another is the name of a thing can change. *
The city of Bombay was recently renamed Mumbai. *That would cause havoc in a
relational database were there any child records to the city table. *If,
instead, *you use an Autonumber primary key, you would only have to change
the city name. *Any related records in other tables would be unaffected..

Now, that doesn't mean some things that are not part of the primary key
can't be required to be unique. *For example, *a Social Security Number
should only be held by one person, so if you create a unique index on Social
Security Number, you will ensure a duplicate value is not entered and it
makes it faster to search on that field.

The case where you have 3 fields in the primary key is almost certainly
incorrect.

The short of it is that you need to reevalute what *your primary keys should
be and redesign your database structure before you go any further.
--
Dave Hargis, Microsoft Access MVP

" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I had trouble sending the next messaqe and I did not know that nothing
was sent. What I have done in the meantime is write a procedure to do
this. I did not start this program and someone else created the table
and forms. So I went back to what he had done when he adds a
CityBuilding record and I got it to work. I will put the code below.
The only problem I have now is I use a CreateQueryDef and the second
time it runs the query is still there and it about. So how can I get
around that problem?

Arep

Function SaveBldg()
Dim MyDb As DAO.Database
Dim MyRS, MyBldg As DAO.Recordset
Dim MyQ, MyInsert As QueryDef
Dim sBldg, sql, sLoc, sCity, sDesc As String

' Open DB,Table and Query
Set MyDb = CurrentDb()
Set MyQ = MyDb.QueryDefs("qryOpenReq_Bldg1")
Set MyRS = MyQ.OpenRecordset()
Set MyBldg = MyDb.OpenRecordset("tblCityBuilding")
Set MyInsert = MyDb.CreateQueryDef("InsertBldgQuery")

With MyRS
.MoveFirst
Do Until .EOF

If ![Dup] = 0 Then
sLoc = CStr(![fldLocationID])
sCity = CStr(![fldCityID])
sBldg = CStr(NextBldg(![fldLocationID], ![fldCityID]))
sql = "INSERT INTO tblCityBuilding ( "
sql = sql + "fldLocationID, "
sql = sql + "fldCityID, "
sql = sql + "fldBuildingID, "
sql = sql + "fldBuildingDesc "
sql = sql + ") VALUES ("
sql = sql + sLoc
sql = sql + ", " + sCity
sql = sql + ", " + sBldg
sql = sql + ", '" + ![Address 1] + "' ) "
MyInsert.sql = sql
MyInsert.Execute
End If

.MoveNext
Loop
End With

' Close Everything
MyRS.Close
Set MyRS = Nothing
MyQ.Close
Set MyQ = Nothing
MyDb.Close
Set MyDb = Nothing

End Function

Private Function NextBldg(LocationID As Long, CityID As Long) As Long
'// this code gets the next building number
Dim crit As String
Dim vResult As Variant

'// make the lookup portion of the DMax fuction (criteria)
crit = "fldLocationID=" & LocationID & " AND "
crit = crit & "fldCityID=" & CityID

'// grab the last building ID
vResult = DMax("fldBuildingID", "tblCityBuilding", crit)
'// now, if we have buildings...
If Not IsNull(vResult) Then
'// make the building ID = max plus 1
NextBldg = vResult + 1
Else
'// otherwise it is the first building for this city
NextBldg = 1
End If

End Function

I would not bother to create a querydef. You are building the SQL
dynamically, so you could use the Currentdb.Execute method. It is faster,
anyway.

Add the terminating semicolon to this line:
sql = sql + ", '" + ![Address 1] + "' ); "
Then
Currentdb.Execute sql, dbFailOnError

But, I would also use a name other the sql as it is an Access reserved word.
I typically use strSQL
--
Dave Hargis, Microsoft Access MVP


" wrote:

On Jul 10, 4:46 pm, Klatuu wrote:
Is there something in your reply I missed?
What I got back was just what I posted.
--
Dave Hargis, Microsoft Access MVP



" wrote:
On Jul 10, 2:51 pm, Klatuu wrote:
I believe your main problem is with understanding primary keys. Very seldom
does a table need more than one field for a primary key.
A primary key defines a record as a unique entity. For example, your
example of a City table with fldLocationID and fldCityID as the primary key
is very suspicious.
A City is a city regardless of location. The location may give you
information about the city, but the location is not the defining attribute of
a city. Even a city's name is not a defining attribute. The location might
help distinguish between Rome, Italy, and Rome, Texas, but just Rome is
ambigious. This is one reason why using a textual description of a thing is
not reliable as a primary key. Another is the name of a thing can change.
The city of Bombay was recently renamed Mumbai. That would cause havoc in a
relational database were there any child records to the city table. If,
instead, you use an Autonumber primary key, you would only have to change
the city name. Any related records in other tables would be unaffected..

Now, that doesn't mean some things that are not part of the primary key
can't be required to be unique. For example, a Social Security Number
should only be held by one person, so if you create a unique index on Social
Security Number, you will ensure a duplicate value is not entered and it
makes it faster to search on that field.

The case where you have 3 fields in the primary key is almost certainly
incorrect.

The short of it is that you need to reevalute what your primary keys should
be and redesign your database structure before you go any further.
--
Dave Hargis, Microsoft Access MVP

" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I had trouble sending the next messaqe and I did not know that nothing
was sent. What I have done in the meantime is write a procedure to do
this. I did not start this program and someone else created the table
and forms. So I went back to what he had done when he adds a
CityBuilding record and I got it to work. I will put the code below.
The only problem I have now is I use a CreateQueryDef and the second
time it runs the query is still there and it about. So how can I get
around that problem?

Arep

Function SaveBldg()
Dim MyDb As DAO.Database
Dim MyRS, MyBldg As DAO.Recordset
Dim MyQ, MyInsert As QueryDef
Dim sBldg, sql, sLoc, sCity, sDesc As String

' Open DB,Table and Query
Set MyDb = CurrentDb()
Set MyQ = MyDb.QueryDefs("qryOpenReq_Bldg1")
Set MyRS = MyQ.OpenRecordset()
Set MyBldg = MyDb.OpenRecordset("tblCityBuilding")
Set MyInsert = MyDb.CreateQueryDef("InsertBldgQuery")

With MyRS
.MoveFirst
Do Until .EOF

If ![Dup] = 0 Then
sLoc = CStr(![fldLocationID])
sCity = CStr(![fldCityID])
sBldg = CStr(NextBldg(![fldLocationID], ![fldCityID]))
sql = "INSERT INTO tblCityBuilding ( "
sql = sql + "fldLocationID, "
sql = sql + "fldCityID, "
sql = sql + "fldBuildingID, "
sql = sql + "fldBuildingDesc "
sql = sql + ") VALUES ("
sql = sql + sLoc
sql = sql + ", " + sCity
sql = sql + ", " + sBldg
sql = sql + ", '" + ![Address 1] + "' ) "
MyInsert.sql = sql
MyInsert.Execute
End If

.MoveNext
Loop
End With

' Close Everything
MyRS.Close
Set MyRS = Nothing
MyQ.Close
Set MyQ = Nothing
MyDb.Close
Set MyDb = Nothing

End Function

Private Function NextBldg(LocationID As Long, CityID As Long) As Long
'// this code gets the next building number
Dim crit As String
Dim vResult As Variant

'// make the lookup portion of the DMax fuction (criteria)
crit = "fldLocationID=" & LocationID & " AND "
crit = crit & "fldCityID=" & CityID

'// grab the last building ID
vResult = DMax("fldBuildingID", "tblCityBuilding", crit)
'// now, if we have buildings...
If Not IsNull(vResult) Then
'// make the building ID = max plus 1
NextBldg = vResult + 1
Else
'// otherwise it is the first building for this city
NextBldg = 1
End If

End Function