Automatically creating a Link table from a unique id in VBA/SQL

[blobb]

Hi. I posted this question a week or so ago but never got a response, so I
thought that I would attempt again with a different approach.

What I am attempting to do is create a link table [Contact Types] that has
three fields in it: ContactType1, ContactID, and ContactID2. I want to move
the unique identifier: Contacts.ContactID into ContactID and the related
records ContactID into ContactID2. The stumbling block that I am facing is
that Subject 001 may be connected to Subject 250 and 270 in [Contacts]. I
have created another unique identifier Contacts.UniqueID that concatenates
the ContactID from the original source (e.g., Subject 001) and a counter
(used for other purposes). I am attempting to find all records that are
related to the original source (e.g., 001) and then move both 001 into
[Contact Types].ContactID and the related records ContactID (250 & 270) into
[Contact Types]ContactID2. The SQL subquery below works by itself but when
I move it into VBA (as shown below) the UniqueID field is always empty.
Could someone help?


LSQL = LSQL & "(INSERT INTO [Contact Types](ContactType1, ContactID,
ContactID2) "
LSQL = LSQL & "VALUES ("
LSQL = LSQL & "Offspring" & "," & IndexID & "," & NewbornID
LSQL = LSQL & "IN "
LSQL = LSQL & "(SELECT ContactID AS NewbornID "
LSQL = LSQL & "FROM Contacts "
LSQL = LSQL & "WHERE LEFT(" & UniqueID & ",3) = " & IndexID & "))"

 

[pietlinden]

Hi.  I posted this question a week or so ago but never got a response, so I
thought that I would attempt again with a different approach.  

What I am attempting to do is create a link table [Contact Types] that has
three fields in it: ContactType1, ContactID, and ContactID2.  I want tomove
the unique identifier: Contacts.ContactID into ContactID and the related
records ContactID into ContactID2.  The stumbling block that I am facing is
that Subject 001 may be connected to Subject 250 and 270 in [Contacts].  I
have created another unique identifier Contacts.UniqueID that concatenates
the ContactID from the original source (e.g., Subject 001) and a counter
(used for other purposes).  I am attempting to find all records that are
related to the original source (e.g., 001) and then move both 001 into
[Contact Types].ContactID and the related records ContactID (250 & 270) into
[Contact Types]ContactID2.  

The standard way of doing this is to create a child table
(ContactType, ParentContactID, ContactFirst, ContactLast...)

Then you join back to the parent based on ParentContactID.

 

[blobb]

I guess that I don't understand what you mean. I was attempting to keep my
database normalized by just creating a linking table. Are you suggesting
that I have two different tables with the names etc in them?

Thanks!

Hi. I posted this question a week or so ago but never got a response, so I
thought that I would attempt again with a different approach.

What I am attempting to do is create a link table [Contact Types] that has
three fields in it: ContactType1, ContactID, and ContactID2. I want to move
the unique identifier: Contacts.ContactID into ContactID and the related
records ContactID into ContactID2. The stumbling block that I am facing is
that Subject 001 may be connected to Subject 250 and 270 in [Contacts]. I
have created another unique identifier Contacts.UniqueID that concatenates
the ContactID from the original source (e.g., Subject 001) and a counter
(used for other purposes). I am attempting to find all records that are
related to the original source (e.g., 001) and then move both 001 into
[Contact Types].ContactID and the related records ContactID (250 & 270) into
[Contact Types]ContactID2.

The standard way of doing this is to create a child table
(ContactType, ParentContactID, ContactFirst, ContactLast...)

Then you join back to the parent based on ParentContactID.

 

[blobb]

Can use SQL subqueries in VBA? Basically, the WHERE subquery clause is
applying the right constrants but the SELECT subquery statement is not moving
the ContactID into the alias NewbornID. Can anyone clarify why this would
be? Thanks!

LSQL = "(INSERT INTO [Contact Types](ContactType1, ContactID,
ContactID2) "
LSQL = LSQL & "VALUES ("
LSQL = LSQL & """Offspring""" & "," & IndexID & "," & NewbornID & ") "
LSQL = LSQL & "IN "
LSQL = LSQL & "(SELECT ContactID AS NewbornID "
LSQL = LSQL & "FROM Contacts "
LSQL = LSQL & "WHERE LEFT(" & UniqID & ",3) = " & IndexID & "))"

I guess that I don't understand what you mean. I was attempting to keep my
database normalized by just creating a linking table. Are you suggesting
that I have two different tables with the names etc in them?

Thanks!

Hi. I posted this question a week or so ago but never got a response, so I
thought that I would attempt again with a different approach.

What I am attempting to do is create a link table [Contact Types] that has
three fields in it: ContactType1, ContactID, and ContactID2. I want to move
the unique identifier: Contacts.ContactID into ContactID and the related
records ContactID into ContactID2. The stumbling block that I am facing is
that Subject 001 may be connected to Subject 250 and 270 in [Contacts]. I
have created another unique identifier Contacts.UniqueID that concatenates
the ContactID from the original source (e.g., Subject 001) and a counter
(used for other purposes). I am attempting to find all records that are
related to the original source (e.g., 001) and then move both 001 into
[Contact Types].ContactID and the related records ContactID (250 & 270) into
[Contact Types]ContactID2.

The standard way of doing this is to create a child table
(ContactType, ParentContactID, ContactFirst, ContactLast...)

Then you join back to the parent based on ParentContactID.