Table design

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I seem to have problems making different tables relate to one another.
Monthly, I import trial balance information from an Excel spreadsheet into a
new MS Access table. I let MS Access assign it a Primary Key. We'll call
this the "TB" table.

I have this other table I designed that I wish to keep ongoing comments on
each account in several memo fields. We'll call this the "Comments" table.
My goal is to import a new trial balance monthly and be able to disconnect
the ongoing comments information (in Memo fields) from last month trial
balance table, creating a new Comments table and reconnect it with the newly
imported TB table. The Comment table has its on Access designated primary
key. I know not to establish a relationship with the two primary keys.
Instead I use a common note number.

I'v tried doing a "make-table" query separating out the comments with their
respective note numbers and then trying to relate the note number field of
the new TB table with the note number field of the Comments table. However,
the query displays no records.

I know this is the most elementary relational database question involving
relating tables. However, I have always been unsuccessful relating tables
through either the table relations mechanism or through queries. If
successful, I usually end up with a recordset that cannot be updated. The
books make it look like child's play by dragging one field over to a similar
one in another table. But I haven't had success doing this. Also, I don't
know VB and therefore can't use this type solution.

Would someone with great patience, please give me a short lesson in table
relations and if possible tell me how I can accomplish the problem stated
above. Thanks for any help in advance.

Ken Little
kenneth dot little at wachovia dot com
 
I seem to have problems making different tables relate to one another.
Monthly, I import trial balance information from an Excel spreadsheet into a
new MS Access table. I let MS Access assign it a Primary Key. We'll call
this the "TB" table.

I have this other table I designed that I wish to keep ongoing comments on
each account in several memo fields. We'll call this the "Comments" table.
My goal is to import a new trial balance monthly and be able to disconnect
the ongoing comments information (in Memo fields) from last month trial
balance table, creating a new Comments table and reconnect it with the newly
imported TB table. The Comment table has its on Access designated primary
key. I know not to establish a relationship with the two primary keys.
Instead I use a common note number.
Click to expand...

You would not create a new Comments table every time you have a new
record in your TB table. You'ld have *one* Comments table; it would
have its own Primary Key (CommentID perhaps), and a separate "foreign
key" linked to the TB table's Primary Key. If that field (which I'll
assume is named TB_ID) is an Autonumber, this field would be a Long
Integer; you could call it TB_ID also, if you wish.

If a single trial balance has 318 comments, that's fine - there would
be 318 records in the Comments table, each with a different value of
its primary key and a different value of its comment text, but all
with the same TB_ID value.

You would NOT put a note number field in the TB table, because then
you would be allowing one and only one comment, ever, for each TB
record. I gather that you want to allow multiple comments. The foreign
key field goes in the "many" side table.

To enter comments and have them automatically linked to the
appropriate TB record (without having to type in the TB_ID), you can
create a Form based on TB with a Subform based on Comments, using
TB_ID as the Master Link Field and Child Link Field.

If you wish to enter the data in a table datasheet... get over that
wish. Table datasheets are of VERY limited utility. Forms allow much
more flexibility and control.


John W. Vinson[MVP]
 
Back
Top