Defining a table with many subtables HOW?

[MadsNilsson]

Hi there. Hope you can help. What I want to achieve - is setting up a databases with two (maybe more) sub-tables related to a main table/form, in the work with a televison casting. The end result I want would be like this:

Main table: Family: Johnsons
Sub table: members:
Family member 1: Roger
Family member 2: John
Family member 3: Anna
Sub table: assets
Asset 1: Plasma TV
Asset 2: Dish washer

But when I try to define a one-to-many relationship with more than one table connected, access just allow me to have one sub-table.

It would be really nice to have a form (do I need to use a query?) showing the Family information, having to open each of the sub tables: "members" and "assets" and then add data to it and close. Can you help? Thank you in advance, experts

 

[dhookom]

IMO, subdatasheets are evil users of resources. I don't generally allow users to view table datasheets.

The typical solution is to use a main form with a couple subforms.

Duane
Hook'D on Access
MS Access MVP

 

[MadsNilsson]

Hi You,
Thanx for a fast reply. I am a bit of a novice on this. Could you explain it more in detail - step by step - for me? Thank you in advance for your help

 

[dhookom]

What do you need explained? Do you understand how to create forms? How about subforms? Do you know about single view and continuous forms?

Duane
Hook'D on Access
MS Access MVP

 

[MadsNilsson]

Yes. I know how to set up forms and subforms. But I am not sure about the relationship part.

If I just make one table with all the information in it, and then split it into subforms - it will be an overwhelming list of fields and information. And in this table I would have to enter:
Family member 1
Family member 1s hobbies
Family ember 1s assets
And so on.

It presume it would be a bit easier to have a main form, named "Family" - linked to sub-forms called "Members" and "Assets". But when I dont know how to set up the relation-ships. If they are needed at all?

I tried to set up referencial relationships from

Family [Familyid] to Familymember [Familyid]
and
Family [Familyid] to Assets [Familyid]

But then it is all locked. I cant enter data?

Thank you for taking your time

M

 

[dhookom]

I don't think you get it. There would be at least three tables.
[tt][blue]
tblFamilies
===========
FamilyID primary key
---more fields for the family ---

tblFamilyMembers (one record per member per family)
================
FamMemID primary key
FamilyID foreign key to tblFamilies.FamilyID
FirstName
LastName

tblAssets (one record per asset per family)
==========
AssetID primary key
FamilyID foreign key to tblFamilies.FamilyID
AssetName
[/blue][/tt]

The link master/child properties are all based on the FamilyID field.

Duane
Hook'D on Access
MS Access MVP

 

[MadsNilsson]

Hi again. Thank you. I have tried your solution. What kind of relationships do I have to set up? I tried this from the beginning - but could not enter any data in main table ("Famliy"). Am I doing anything wrong? I set it up with foreign keys in the two sub-tables, forcing cascade update.

 

[MadsNilsson]

Hi. I think i am able to make this work now. I did what you said - and added two buttons on the main form, named "Family members" and "Family assets" - opening, and filtering the members and the assets for the family.

Next I want to merge this into one scheme - so that I can e-mail this scheme to the cast. Is it possible to do this from the main form? I only get the option of e-mailing each table. Could this be done through a query?

 

[dhookom]

I don't think I suggested anything about buttons that open anything. Subforms are hosted on a main form and are tyically always visible. You can use a tab control on your family form and place a subform on its own page of the tab control.

Duane
Hook'D on Access
MS Access MVP

 

[MadsNilsson]

With your help, I managed to set it all up. The only thing I miss now, is to merge it all into one "table" (query - maybe?) so I can e-mail the families all the fields and get them to fill it in and return it to me. Any suggestions? As I got three tables?

 

[dhookom]

I am not optimistic that you could create a single query with these three tables since Assets are not related to Family Members.

If I were to create this, I would probably push records to an Excel file to email to families. The when the files came back in, there could be some code to import the Excel "records".

Duane
Hook'D on Access
MS Access MVP