473,320 Members | 1,965 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Linking more than two tables

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
Apr 2 '12 #1
10 2550
nico5038
3,080 Expert 2GB
Why using the [+] and not transform this into a main form for the main table and a subform for each dependent table ?

Nic;o)
Apr 2 '12 #2
zmbd
5,501 Expert Mod 4TB
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))



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...



If this doesn't help, please give a few more details about your table structure and what version of MS-Access you're using.
Attached Images
File Type: jpg relationships.jpg (22.6 KB, 624 views)
File Type: jpg tables.jpg (105.1 KB, 556 views)
Apr 3 '12 #3
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.
Apr 3 '12 #4
zmbd
5,501 Expert Mod 4TB
@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
Apr 4 '12 #5
NeoPa
32,556 Expert Mod 16PB
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.
Apr 4 '12 #6
Mihail
759 512MB
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/insigh...ble-structures
For better understanding, after you follow the above link, an idea about how should be designed your tables:

Expand|Select|Wrap|Line Numbers
  1. tblMembers:
  2.     Member_ID (Primary Key, Autonumber)
  3.     Member_Name (Text) - Do not use the word "Name"; it is an Access reserved word
  4.     Other fields

Expand|Select|Wrap|Line Numbers
  1. tblPayment:
  2.     Payment_ID (Primary Key, Autonumber)
  3.     Member_ID (Lookup field on table tblMembers)
  4.     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 !
Apr 4 '12 #7
zmbd
5,501 Expert Mod 4TB
Mihail:

(...)
Expand|Select|Wrap|Line Numbers
  1. tblPayment: 
  2.     Payment_ID (Primary Key, Autonumber) 
  3.     Member_ID (Lookup field on table tblMembers) (<<Z)
  4.     Payment_Date (Date) 
  5.  
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/answer...ds-tables-evil

-z
Apr 4 '12 #8
Mihail
759 512MB
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.
Apr 4 '12 #9
yeah thats close- I'll try that out see how it goes.

Thanks all for helps, links and suggestions...lots of ideas to test.
Apr 4 '12 #10
NeoPa
32,556 Expert Mod 16PB
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.
Apr 5 '12 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

10
by: Job Lot | last post by:
Is there any way to programmatically link access tables using vb.net
4
by: rdraider | last post by:
I am looking for some assistance with an update query that needs to link 3 tables: This query ran and reported over 230,000 records affected but did not change the field I wanted changed, not...
2
by: Phil S | last post by:
I used the Migration Workbench for Oracle 9.2 Client to migrate the tables in an Access 2K back-end database to Oracle. (The Migration Workbench is intended to automate the process of migrating...
3
by: Rob Davis | last post by:
I am familiar with VBA and the manual method of attaching/linking external data tables (File, Get External Data etc). I am also familiar with opening tables which exist in the current Access file,...
3
by: Ellen Manning | last post by:
Using A2K. I need syntax to delete all objects from a database other than tables, like with a FOR...LOOP construct. I researched the newsgroup and found suggestions to look thru Access help on...
11
by: dskillingstad | last post by:
I've been struggling with this problem for some time and have tried multiple solutions with no luck. Let me start with, I'm a novice at Access and I'm not looking for someones help to design my...
1
by: Gt394 | last post by:
tbl_Quote PK QuoteNo tbl_QuoteDetails - Junction table between tbl_Quote and tbl_Product tbl_Product PK ProductID tbl_Thickness PK ThicknessID tbl_PanelType PK PanelID...
10
by: Jennifer Carr | last post by:
I have an Access 2003 application that is split into two .mdb files for the interfaces and data. This app is distributed on a CD to be used on computers that have no internet access. When someone...
3
slightlybefuddled
by: slightlybefuddled | last post by:
(Exporting ImageReady slices as CSS rather than tables) apparently means it'll work just fine in Firefox, but do wacky stuff in IE? Can anyone help me figure out why on earth the slices are not...
20
by: OldBirdman | last post by:
I have 2 computers: #1 Desktop - Windows XP & Office 2000 #2 Laptop - Windows Vista & Office 2003 Laptop is NEW, and the hard-drive is partitioned C:\ and D:\ from factory, with Vista and...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.