Initialize a linking table from existing data?

[Greta]

I am updating an existing mailing list that was essentially a spreadsheet in
Access. I want to add 2 tables: one to describe the kind of constituent
(board member, business, nonprofit, interests, etc.) and another table to
contain which mailings each constituent will receive. Because both of these
relationships are many-to-many, I have added linking tables between them.

I would like to initialize one of the linking tables with information that
exists in the main table, but I haven't figured out how. The main table is
called tblMasterFile. Pertinent fields in this table are ID (an autonumbered
primary key) and MasterFile (the previous primary key that is in the form
BUS0001. It is a 'smart' field in the sense that BUS tells me it's a
business & the 0001 is a differentiator.) The second table is called
tblDescriptors&Interests. It contains two fields: DescriptorID and
Descriptor where DescriptorID is an autonumbered primary key and Descriptor
is, say, "Business" or "education". The linking table is called
tblLinkMasterDescriptors. It has two fields: ID which I'd like to come from
the ID field in tblMasterFile, and DescriptorID which should match
DescriptorID in the tblDescriptorsInterests.

It seems like I should be able do some kind of an action query that says 'If
MasterFIle looks like BUS*, then place the corresponding ID from
tblMasterFile in the field ID in the tblLinkMaster&Descriptors and the number
2 in the DescriptorID field in tblLinkMasterDescriptors.

Any other suggestions would be helpful (e.g., should I get rid of ampersands
in table names?)...

Thanks!

 

[John W. Vinson]

I am updating an existing mailing list that was essentially a spreadsheet in
Access. I want to add 2 tables: one to describe the kind of constituent
(board member, business, nonprofit, interests, etc.) and another table to
contain which mailings each constituent will receive. Because both of these
relationships are many-to-many, I have added linking tables between them.

I would like to initialize one of the linking tables with information that
exists in the main table, but I haven't figured out how. The main table is
called tblMasterFile. Pertinent fields in this table are ID (an autonumbered
primary key) and MasterFile (the previous primary key that is in the form
BUS0001. It is a 'smart' field in the sense that BUS tells me it's a
business & the 0001 is a differentiator.) The second table is called
tblDescriptors&Interests. It contains two fields: DescriptorID and
Descriptor where DescriptorID is an autonumbered primary key and Descriptor
is, say, "Business" or "education". The linking table is called
tblLinkMasterDescriptors. It has two fields: ID which I'd like to come from
the ID field in tblMasterFile, and DescriptorID which should match
DescriptorID in the tblDescriptorsInterests.

It seems like I should be able do some kind of an action query that says 'If
MasterFIle looks like BUS*, then place the corresponding ID from
tblMasterFile in the field ID in the tblLinkMaster&Descriptors and the number
2 in the DescriptorID field in tblLinkMasterDescriptors.

Any other suggestions would be helpful (e.g., should I get rid of ampersands
in table names?)...

Absolutely. & is a string concatenation operator and has special functions in
captions and labels, and it's a VERY bad choice in a fieldname. I'd stick to
names starting with a letter and containing only letters, numbers and (if you
wish) underscores - no blanks or any other punctuation.

To fill the linking table you would use an Append query such as

INSERT INTO tblLinkMaster_Descriptors(ID, DescriptorID)
SELECT tblMasterfile.ID, (2)
FROM tblMasterFile
WHERE tblMasterFile.MasterFile LIKE "BUS*";

You'ld do the same with the other categories.

 

[Greta]

Thanks John.

It took me a couple of minutes to find the SQL view for a new query, but I
got there.

I entered the commands as written (modified table names to remove &'s
first). I received an error message containing a "type conversion failure",
122 records not copied due to key violations, 0 copied due to lock violations
& 0 copied due to validation rule violations. I selected to proceed anyway
(the linking table was empty). No data was copied & it appears that the
relationships were also deleted. I'm assuming I had some referential rules
that got in the way. Should I have expected the relationships to get deleted
when I proceeded despite the error messages? It's no big deal as it will be
simple to redo them, I'm just curious. I'll be back at this sometime
tomorrow. THANKS!

 

[John W. Vinson]

Thanks John.

It took me a couple of minutes to find the SQL view for a new query, but I
got there.

I entered the commands as written (modified table names to remove &'s
first). I received an error message containing a "type conversion failure",
122 records not copied due to key violations, 0 copied due to lock violations
& 0 copied due to validation rule violations. I selected to proceed anyway
(the linking table was empty). No data was copied & it appears that the
relationships were also deleted. I'm assuming I had some referential rules
that got in the way. Should I have expected the relationships to get deleted
when I proceeded despite the error messages? It's no big deal as it will be
simple to redo them, I'm just curious. I'll be back at this sometime
tomorrow. THANKS!

Perhaps you could post the actual SQL you used, and the relationships,
fieldnames and datatypes of your current table. The key violations suggests a
problem with the relationships or with the primary key. An Append query (even
a failing one) should not affect your relationships! What evidence do you have
that *THE RELATIONSHIP* was deleted? If you open the Relationships window and
show all relationships, is it gone??

 

[Greta]

A fresh look at things can work wonders! I opened the Relationships window
this morning after your last post & it hit me that the reason I thought the
relationships had been broken was because tables had disappeared from the
window. When I re-added the tables to the window, the relationships were
back! Then it hit me -- the tables disappeared because I had renamed them by
removing the "&" from the names. duh. Once I reestablished the
relationships & removed the 'enforce referential integrity' rule, the code
worked like a charm! My linking table is now initialized. I kept the append
query, but put big clues in the key spots to keep me from accidentally
misusing it. I even think I understand why things worked and didn't work
along the way!

Thanks!

 

[John W. Vinson]

A fresh look at things can work wonders! I opened the Relationships window
this morning after your last post & it hit me that the reason I thought the
relationships had been broken was because tables had disappeared from the
window. When I re-added the tables to the window, the relationships were
back! Then it hit me -- the tables disappeared because I had renamed them by
removing the "&" from the names. duh. Once I reestablished the
relationships & removed the 'enforce referential integrity' rule, the code
worked like a charm! My linking table is now initialized. I kept the append
query, but put big clues in the key spots to keep me from accidentally
misusing it. I even think I understand why things worked and didn't work
along the way!

I'd really suggest using Show All Relationships, or running this code:

Sub ShowAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim fld As Field
Set db = CurrentDb
For Each rel In db.Relations
Debug.Print "Relation "; rel.Name, rel.Table, rel.ForeignTable,
Hex(rel.Attributes)
For Each fld In rel.Fields
Debug.Print fld.Name; " linked to "; fld.ForeignName
Next fld
Next rel
End Sub

Renaming a table will indeed remove it from the relationships window but
should not affect its relationships. You may have duplicate relationships; if
so, Show All will display the tables involved twice, once with a 1 appended to
the tablename. You can select and delete the relationship line if that's the
case.

 

[Greta]

Thanks for the hint. I either didn't know or forgot that Show All...
existed. I was clean, though, only one set of tables. At least I know
there's nothing around that way to muck things up as I proceed!