Linking more than two tables

[Daedalus]

I'm trying to use relationships to link more than two tables.

Using referencial intrgerity I can connect two tables so that by clicking a small + I can open one inside the other for the various entries.

But I would like to add more so that when I click the + it opens tow or more lines below with the corrosping values entries in the other tables.

All tables have a unique number and Name field as options for Primary Key. In my successful linking of two I've used name as primary and entered number in the linked tables first time I open it for each entry.

Is what I'm trying even possible. The two table I've linked sofar have the same number of entries of number and name fields, but the ones I'm hoping to link will only have content of certain fields in their tables are filled in.

I'm basically hoping to make it so that I can see the values lsited in other tables for the same entry inside the first table...

help...if possible

[nico5038]

Why using the [+] and not transform this into a main form for the main table and a subform for each dependent table ?

Nic;o)

[zmbd]

Do you want a cascade of related records

Daedalus:
I'm trying to use relationships to link more than two tables.

But I would like to add more so that when I click the + it opens tow or more lines below with the correspond values entries in the other tables.

Not sure that I've followed this...
You would like to have a cascade of related records in the table?

Now, this may not be the best way to handle the situation you're after. Normally, I don't let users even see the raw tables and as suggested by another the use of a main form and subform is prefered.

With that said, are you after something like this?
tbl_one

(+)tbl_one Record one related on some fk to tbl_two

(+)the related information from tbl_two with one or more rows from tbl_two related to the current record in tbl_one

(+)one or more records from tbl_three, related back to tbl_two as it pertains to the child record from tbl_two inherited from tbl_one

So you get something like I have here with four tables:
tbl_manf - (pk manf_id)
tbl_prd - (pk prd_id);(fk manf_id (1:m))
tbl_asst - (pk asst_id);(fk prd_id (1:m))
tbl_hst - (pk hst_id);(fk asst_id (1:m))

[IMGnothumb]http://bytes.com/attachments/attachment/6273d1333464021/relationships.j pg[/IMGnothumb]

So what happens if I open the tbl_manf, click on the (+) next to a record I get a sub-group showing the related records in tbl_prd, if I click on the (+) on a record in the tbl_prod subgroup then I get yet another subgroup showing the related records in tbl_asst, etc...

[IMGnothumb]http://bytes.com/attachments/attachment/6274d1333464021/tables.jpg[/IMGnothumb]

If this doesn't help, please give a few more details about your table structure and what version of MS-Access you're using.

[Daedalus]

Yeah Guess I didn't explan properly - my bad.

Using Access 2007

The DB is for me to help me manage membership listings and such for a social club I'm the treasurer of.

Tables Members. Has values #, name (Primary Key), and various contact info details like mail, adress, phone, birthday, join date, leave date, status, type of membership, payment method etc.

Table Payment. Has values #, Name(Primary Key) and one field for each month.

Third table will be one that will hold the info on what type of management role, what group of membership and the like. Will also have #, Name(primary Key)

4th Table will be for lsiting wether a reminder has been sent for late payment and wether a fine has been issued. Will also have #, Name(Primary Key)

MY main working window in the DB is the Table Members since I usually need to mnipulate or inspect multiple entries in quick succession, so a form that displays a singel value is good for when I need to look up something for a specific reason, but for my usual work I've found the table view gives best results.

I've created queries for membership distribution, reports for payment lists and membership lists for printing. And forms for entering new members (incl search via number, a big success for me to get working) or single member payments.

Primary Key in each Table will be Name (and I can easily manually enter the # to make the tables match like I need to do when linking two tables). All fields will be either text, number or adress - with a few that are lookup - nothing fancy.

What I was hoping was to enable me to have..well best words is prob "dropdown" below a person in Table Members via the [+] so that I get tables Payments. Mangement. and the Fine/reminder Table entries for that person shown in 3 lines below...

So:

Main Table Members
Payments
Management
Fines/reminders.

1 Table wherein I can with a click see values from 3 directly related tables.

That way I can rapidly close one person and move on to updat the next one.

Not sure waht I wish for is possible, if not I can have a go at the form/subform method but that would really be a fallback option. Espeically since I'm very weak in the code department.

Any help is greatly appriciated, even if jsut informing me that what I'm after is hopelessly complicated and I should abandon it for Form/subform...that way I can put the idea to rest.

[zmbd]

@Daedalus:
(...)
What I was hoping was to enable me to have..well best words is prob "dropdown" below a person in Table Members via the [+] so that I get tables Payments. Mangement. and the Fine/reminder Table entries for that person shown in 3 lines below...
(...)
So:

Main Table Members
Payments
Management
Fines/reminders.

1 Table wherein I can with a click see values from 3 directly related tables.

I think I understand what you're after... if you wouldn't mind, take another look at the second jpg in my first reply. (I added the picture some time after I made the posting - You may need to hover the mouse to see the entire picture. Still learning how to present things on this board :) )

Does it show the kind of relationship you're after?

Also, it appears, at first glance, that the database normalization could be optimized more; however, it's hard to tell.

In anycase, if I'm on the right track give me a "heads-up"


-z

[NeoPa]

Nice work Z :-)

I took the liberty of converting your [IMG] tags to [IMGNOTHUMB] ones in post #3, in order to make it easier to work from. Also, good to see you quoting intelligently and not simply repeating everything from a previous post.

[Mihail]

Absolutely sure is a normalization problem here. A big one.
Daedalus ! Read very carefully this thread (first post at least):

http://bytes.com/topic/access/insights/585228-database-normalization-table-structures

For better understanding, after you follow the above link, an idea about how should be designed your tables:

tblMembers:
    Member_ID (Primary Key, Autonumber)
    Member_Name (Text) - [B]Do not use[/B] the word "Name"; it is an Access reserved word
    Other fields

tblPayment:
    Payment_ID (Primary Key, Autonumber)
    Member_ID (Lookup field on table tblMembers)
    Payment_Date (Date)

Note that is not necessary to have a field for each month

And so on for the 3th, 4th, ... , Nth tables

It is allowed but is a very poor approach to define a Primary Key on a text field.

As an answer to your original question:
As far as I know is not possible to have more than one "+" sign to open sub-tables.
So, the best approach (if not the single) has been suggested by nico5038 in post #2.

Good luck !

[zmbd]

evil lookup fields in data tables

Mihail:

(...)

tblPayment: 
    Payment_ID (Primary Key, Autonumber) 
    Member_ID (Lookup field on table tblMembers) (<<Z)
    Payment_Date (Date)

Mihail offers to use a lookup field for the [Member_ID], it is my personal preference not to do so in datatables. When I first discovered the lookup field and started using these things in datatables, I ran into alot of frustration when trying to build queries and subsequent reports - this Gremlin infected several of my datbase projects, until, I read the following and saw the light:

http://access.mvps.org/access/lookupfields.htm

Now, I use lookups only in my queries and in forms etc... so as to display the "human-readable" information but I never use them in the underlying datatables. Besides, other database that I know of do not support lookup fields in the datatables so upsizing, should you need to do so, could be a real p.i.t.* !

A different take on this is at:

http://bytes.com/topic/access/answers/930392-lookup-fields-tables-evil

-z

[Mihail]

It is your choice.
I have tried to help you but I can teach you only what I know.

About your link... hm.
I don't agree what I read in, but I am not very experienced in database.
So, I expecting with a large interest the experts opinion.

[Daedalus]

yeah thats close- I'll try that out see how it goes.

Thanks all for helps, links and suggestions...l ots of ideas to test.

[NeoPa]

Mihail:
So, I expecting with a large interest the experts opinion.

Although it may not have been clear to you because of your limited English, I was actually saying that I agree with the MVPs advice about lookup-fields. I think they should be used as little as possible, and that means only when it is absolutely necessary. Personally, I cannot think where or when that might be, so I've never used them since I realised for myself what a PITA they can be.

I hope that's a clearer statement than the one from the linked thread :-)

PS. zmbd may be new here, but they are clearly quite expert at the concepts of databases generally. I would take anything they say as good advice unless you have very good reasons to do otherwise.