i need help how to link two tables in a database (1 Viewer)

[musmoniem]

hello everyone ,, i had a database and i want to link more than table with each other and want to make a form with multi tabs .. each tab open a sub form with the data in each table .. i made a relationship but got stuck .. any help i attached the database ... hope arnelgp help me

 

[The_Doc_Man]

OK, let's talk concepts here. Strictly speaking, a form has one and only one record source. That source could be a table OR a query, and if it is a query then it could include a JOIN. However, anywhere on that form, you have only one binding - the form's .RecordSource property. Having at least two tab controls gives you extra viewing space on the same form BUT both tabs are part of the same form and have the same .RecordSource property - that of the form that contains the two tab controls. (Having only one tab control is kind of pointless.)

I think what you MIGHT want - and this is a guess on my part - is that you want a table for which you can see related tables to act on them separately. The way Access does this is that your form can have a SUB-FORM (which is a topic you can look up.) A sub-form can have its own separate .RecordSource different from the main form and can operate semi-independently from its parent. I used "semi" because usually the separate form is dependent on the main form in some way. Which actually sounds like what you tried to describe. Then you would have a parent/child form situation to correspond to an independent/dependent or parent/child table situation.

Before I go any further with this, do my comments appear to relate to what you wanted to do? I have not looked at your DB because of personal preferences to avoid opening someone else's databases on my system.

 

[musmoniem]

OK, let's talk concepts here. Strictly speaking, a form has one and only one record source. That source could be a table OR a query, and if it is a query then it could include a JOIN. However, anywhere on that form, you have only one binding - the form's .RecordSource property. Having at least two tab controls gives you extra viewing space on the same form BUT both tabs are part of the same form and have the same .RecordSource property - that of the form that contains the two tab controls. (Having only one tab control is kind of pointless.)

I think what you MIGHT want - and this is a guess on my part - is that you want a table for which you can see related tables to act on them separately. The way Access does this is that your form can have a SUB-FORM (which is a topic you can look up.) A sub-form can have its own separate .RecordSource different from the main form and can operate semi-independently from its parent. I used "semi" because usually the separate form is dependent on the main form in some way. Which actually sounds like what you tried to describe. Then you would have a parent/child form situation to correspond to an independent/dependent or parent/child table situation.

Before I go any further with this, do my comments appear to relate to what you wanted to do? I have not looked at your DB because of personal preferences to avoid opening someone else's databases on my system.

The attachment is safe don’t worry about it ..
i had a parent table contain the personal info of contact .. and another 11 child table contain another data for some contacts
I want to show in a form with multi tab the whole data about the certain contact

 

[Isaac]

Look into using database Joins.

 

[The_Doc_Man]

I will say that having 11 child tables is certainly possible but seems excessive. It would surely complicate matters if you had to link with all 11 tables on the same form. It makes me wonder if the child tables are not properly normalized if all the data represents contact information.

 

[musmoniem]

I will say that having 11 child tables is certainly possible but seems excessive. It would surely complicate matters if you had to link with all 11 tables on the same form. It makes me wonder if the child tables are not properly normalized if all the data represents contact information.

No every table represents an info like table for travels , table for promotions , table for health conditions, and so on.. each contact may have more than vacation in the year , also some contacts may have more than one promotion in the year others may not have any promotions at all ... so i used a separate table and want to join all of them in one multi tab form each tab represents the data of the contact in each table .

 

[plog]

Let's just talk tables for a minute. I am looking at your Relationship Tool and this is what I see:

1. Using a non-primary key as a foreign key. Every table is linked to Contacts via its [ID Number] field, that is wrong. Since [ID] is the primary key of Contacts that is the value/field that should connect to every foreign table. However, if [ID Number] is unique to every Contact you can use that, in which case you get rid of [ID] entirely and assign [ID Number] as Contacts primary key.

2. [ID] in every table. You have 13 fields in your database called [ID], that's going to be confusng when writing queries. I suggest prefixing every ID with the ID it is the ID of (e.g. ID_Calls, ID_Sanctions, ID_Promotions, etc.).

3. Non-alphanumeric characters in names and beginining tables with numbers. Using non-alphanumeric and underscore characters in names makes coding and querying more difficult. The same goes for having a number as the first character of a table. You should rename your fields/tables to eliminate that ([1 Main Employees]--MainEmployees, [ZIP/Postal Code]-->Zip)


Then, for your form issue I would have 1 form with a tab object that has 12 tabs each holding a subform. The main form would be based on Contacts, then you build a subform for every other table and use a tab object and place each subform on a different tab.

 

[mike60smart]

Hi
You are also using Lookup Fields in Tables which is not recommended.