Relating one table to several others



  • 1. Object comparison program for tables, queries, forms, modules etc
    Anybody know of any totally FREE Access object comparators which will compare the database objects in two databases and show the differences? There are several commercial products that do this. their free trial versions tend to be limited to only a few of the objects. Bob
  • 2. Formatting help
    I was wondering if there is a place to download other styles for forms? Can I create my own? Where are they stored in W98? Thanks, Don.........
  • 3. Controls on Subreports
    How do you refer to a control on a Subreport? I have 3 subreports. I want to add and subtract according on the mainform (Reort Footer) but I don't know how to refer to the controls on the subreport. By the way the controls are calculated controls. Thanks DS
  • 4. Syntax of control on tab controls
    I have a form "frmturnover" on which there is asubform "subfrmturnover" which has three tabcontrols say Page1, Page2 and Page3. There is control "control2" on Page2 that when its is entered I want to check if it is the same value as a control "control1" on Page1 and then show a pop up box if they don't match. I can work out the code for the message box but I'm getting a little lost on the syntax for referring to the controls on the pages of the tab control. I also want to check the value of Control2 on page2 with the vale of control3 on a page1 of a subform "subfrmmain" on another form "frmmain" can anyone point me in the right direction? TIA Tony Williams

Relating one table to several others

Postby Grip » Sun, 04 Oct 2009 05:10:17 GMT

I'm building a database and want to have a couple tables (Notes,
Files) that relate to multiple other tables.  I would like to use a
UUID for this.

Is the Replication ID the right tool for this?  If not how do I create
an ID field that is unique across the database?

Here's what I am trying to do...

I have a table for Students.  I have a table for Classes.  I have a
table for Registrations.  I want to keep track of Notes for each
of those entities.  The Notes are the same for all three: Notes field,
Timestamp, UserID.  Instead of adding three Notes tables (Class Notes,
Student Notes, Registration Notes), I want a single Notes table.  That
table will
have a field ParentID that relates it to Classes or Students or

How do I relate that Notes table to each of the three other parent
tables without a key conflict?

I need to define a key for each of the
three parents that will be unique across them.  Otherwise the Note for
Jane Smith (auto increment ID 17) will show up for Class Physics (also
auto increment ID 17).

Make sense?

I posted this on m.p.a. but they kept answering a question I didn't
ask and didn't need the answer to.


Re: Relating one table to several others

Postby Roger » Sun, 04 Oct 2009 05:18:51 GMT

   notesId  autokey
   studentId (fk to tblStudent)
   classId (fk to tblClass)
   registrationId  (fk to tblRegistration)

you'd have only one of studentId, classId, registrationId, implements
in the application

and it allows many notes, per ID

Re: Relating one table to several others

Postby Grip » Sun, 04 Oct 2009 06:00:58 GMT

> otesId utokey
>> tudentId (fk to tblStudent)>
> lassId (fk to tblClass>
> egistrationId fk to tblRegistrati>n)
> >tes
> rea>edBy
> ateC>eated
> you'd have only one of studentId, classId, registrationId, imp>ements
> in the appl>ca>ion
> and it allows many notes, per ID

My normalization instincts tell me to avoid having two fields that
will always be null, but it may be good enough for government work.

No way to create a UUID?

Re: Relating one table to several others

Postby paii, Ron » Sun, 04 Oct 2009 06:41:15 GMT

NoteID                AutoNumber
NoteType            Class, Student, Registration
NoteLinkID         Long, Allow duplicates
Message              Memo

Link your detail tables to NoteLinkID and use NoteType to select only notes
for Class, Student or Registration; of course you will not be able to
enforce referral integrity on NoteLinkID with this table layout.

Otherwise add a M-M table for each detail table

NoteID                 AutoNumber
Message               Memo




Re: Relating one table to several others

Postby Roger » Sun, 04 Oct 2009 22:06:12 GMT

> NoteType lass, Student, Re>istration
> NoteLinkID Long, Al>ow duplicates
> Messa>e > emo
> Link your detail tables to NoteLinkID and use NoteType >o select only notes
> for Class, Student or Registration; of course you>will not be able to
> enforce referral integrity on NoteLinkID wit> t>is table layout.
> Otherwise add a M-M table f>r >ach detail>table
> tblNote
> NoteI> AutoN>mb>r
> Message > gt;Memo
> Cla>sID
> NoteI>
> tb>St>dentNote
> StudentID
>> NoteID
> tb>RegistrationNote
> Registrat>on>D
> NoteID- Hide quoted text -
> - Show quoted text -

referential integrity is the reason for my design
as to 'null' fields, many fields in many tables are null...

Similar Threads:

1.Automatic load of 1-2 data fields from one table to several others

I have a data base that requires a large amount of information (we will call 
census data) which has over 75 columns in a single table.  The information 
has to be updated on an annual basis - so the census table is a one-to-many 
relationship to the client table, with the primary/foreign respectively being 
the client ID.  There are also some tables I would like to generate 
automatically from the initial entry of the new client form and the first 
census table(s)

I would like to separate the types of census data.  What VBA code can I use 
to have each separate census table pick up the related client ID from the 
client table automatically without user selection in each table.  I would 
also like to have some background code that will automatically put the first 
visit date and client id in a separate table, and create an autonumber as 
secondary client code for a different reporting system.

2.One MDB updating several others

3.How do I link several forms to one id in one table

I use the data base to record details of students I see.  I currently have 1 
table with one main form and several other forms that are accessed from 
command buttons on the main form.  The forms accessed by the command buttons 
have a series of option buttons which help me record issues the child has.  
How do make the information recorded on each of the forms relate to the child 
and information on the main form.
Hope that is clear.  Thanks  

4.Copying related records from several tables

My database contains several related entities: Versions are made up of 
Assemblies which are made up of Components.

I want to select a Version on a form, then make a similar new Version (only 
the URN will change) I then want to copy each Assembly related to the old 
Version, relating the new Assembly records to the new Version. Similarly, I 
want to copy each Component record relating to each old Assembly record and 
relate it to the new Assembly record.

I know I can't do it with temporary tables, append and update queries, 
because I've tried. Does anyone have some sample code I could amend and 
attach to a command button on the form?

5.Splitting one table into several smaller ones

"PC" < XXXX@XXXXX.COM > wrote in news:0a4a01c37967$d15a8a30$ XXXX@XXXXX.COM :

> So, how is it that I can selectively tell the db to use (x)
> sub-tables for any given record? I've tried using an 
> INSERT statement but I would need to ask this 4 times in 
> this instance. What would be a better way?

I am not sure that you have quite got the hang of data normalisation. The 
point of "sub tables" is that they contain details of particular things: in 
a table of car-hirings, for example, the subtables would contain details of 
customers, cars, cheque clearances and so on. Your queries go looking for 
information depending on what you want to know, not on what might be there. 

I always worry about lines like, 

> I have a large table that needs to be split into several 
> smaller ones.

because normalisation is not really a question of splitting but one of 
collecting: you collect together all the car stuff and put it in a table of 
Cars, ditto for Customers and Payments etc. etc. 

If you are able to post more information about what you are trying to 
achieve, it may be possible to give you more precise advice. 

Best Wishes

Tim F

6. Breaking up one LARGE table into several smaller ones

7. Merging related records when there are multiple one-to-one relationships between two tables

8. Six one to one related tables: question

Return to MS ACCESS


Who is online

Users browsing this forum: No registered users and 0 guest