Relations, Forms and Subforms

[knowshowrosegrows]

I need someone to lead me to the most simplistic idiot guide to creating
relationships. I have done the training on the microsoft site and read the
help fields and I am not getting it.
Thanks

 

[kingston via AccessMonster.com]

Do you not get how to do it or do you not get why you do it or both? What
training did you go through? I found that the Northwind database was very
helpful when I first started using Access (you can install it with Access).
Go to the Relationships window and study it. Look at how the fields have
been divided amongst the different tables. Relationships go hand in hand
conceptually with normalization so you may want to look that topic up too.

Basically, normalization is the process of dividing data fields into tables
so that data isn't repeatedly stored and maintained. For example, if you
wanted to keep track of sales to customers, you wouldn't want to write down
the customer's address and phone number every time he made a purchase. This
would mean that if the customer moved, you might have to change the address
hundreds of times depending on how often he made purchases. So you'd have a
table listing purchases and a table listing customer information. Then,
you'd relate the two sets of information with a key such as CustomerID. This
key exists in both tables and allows you to make a relationship between the
two sets of information. With this relationship you can easily maintain
customer information and quickly find information like all the purchases one
customer made.

You can create relationships in Access in the Relationships window. Open the
database window and there should be an icon in the main toolbar that looks
like three tables connected by two lines. Click on the icon and add the
tables you want to relate. Click and hold a field in one table and drag and
drop it onto the appropriate field in the related table. Start with what's
called the primary key (in the example, it is the CustomerID in the Customers
table). You'll be prompted on whether or not to Enforce Referential
Integrity. That essentially means whether changes in the primary table
should be reflected in the related table automatically. If you're dealing
only with IDs, this really isn't an issue. You can do basically the same
thing when building a query and adding tables but the relationships will not
be reflected outside of the query.

I know this isn't the best explanation but hopefully this helps.

 

[knowshowrosegrows]

Thank you for your great reply. It clarified some stuff, but... I am
working on a form with at least one subform.

I have a table of vendors (and vendor ID). The vendors have multiple sites
(and site ID). i have those two tables and they are filled with data. Each
site has an undetermined number of apartments ( I don't know how many until
my inspectors go out), and lastly, each apartment has an undetermined number
of bedrooms and the number of beds in each bedroom has to be noted.

So I need to collect an autonumber for each apartment at a site. And I need
to collect an autonumber for each bedroom at an apartment.

I need each apartment the inspector notes in the form to get an automatic
unique number, but if it's an autonumber, it won't relate to any of the
fields in the other tables because it is not the same data type. --
Thanks

 

[kingston via AccessMonster.com]

OK, to do this in subforms, you really don't need to create relationships in
the Relationships window; creating subforms via the Access wizard will
automatically link the data for you properly. Let's start with a 3-tiered
system and you can extend it further.

First, start with the data table at the bottom of the hierarchy. In this
example, I'm going to use tblBedrooms. In this table, make sure you have a
foreign key (FK) field called ApartmentID; this establishes the relationship
between a particular BedroomID and a specific apartment. Create a simple
tabular form for tblBedrooms. Open it and make sure it works. You should
have no problems here.

Second, create a form for tblApartments. Make sure this table has a FK field
called SiteID (and naturally its own ApartmentID); this is used to tie a
specific apartment to a site in step three. Create a simple tabular form for
tblApartments. In this form's design mode, drag and drop the form you
created for tblBedrooms. You'll be asked to specify the parent/child field.
This is ApartmentID since you want Access to synchronize the bedroom records
with the current apartment. Save the form and open it. Use it and make sure
you understand what's going on. When you go to a specific apartment, you'll
see the bedrooms linked to that apartment. If you need to add a bedroom,
just do so in the subform. Leave that apartment and go back to it. You'll
see that the bedroom and the apartment have been effortlessly linked (via
ApartmentID).

Third, create a form for tblSites. In design mode for this form, drag and
drop the second form you created for apartments. Indicate that the
parent/child field should be SiteID. That's it. The apartment subform
should work within the sites form just like the bedrooms subform works for
the apartment form.

Continue doing this up to the top level of the hierarchy. I wouldn't do this
with too many levels simply because the form will be very complicated. Also,
this only works with one to many relationships. In other words, a site
cannot belong to more than one vendor but a vendor may own multiple sites.
hth

Thank you for your great reply. It clarified some stuff, but... I am
working on a form with at least one subform.

I have a table of vendors (and vendor ID). The vendors have multiple sites
(and site ID). i have those two tables and they are filled with data. Each
site has an undetermined number of apartments ( I don't know how many until
my inspectors go out), and lastly, each apartment has an undetermined number
of bedrooms and the number of beds in each bedroom has to be noted.

So I need to collect an autonumber for each apartment at a site. And I need
to collect an autonumber for each bedroom at an apartment.

I need each apartment the inspector notes in the form to get an automatic
unique number, but if it's an autonumber, it won't relate to any of the
fields in the other tables because it is not the same data type. --
Thanks

Do you not get how to do it or do you not get why you do it or both? What
training did you go through? I found that the Northwind database was very

[quoted text clipped - 35 lines]

 

[knowshowrosegrows]

You are the Best! Thankyou!
--
Thanks

OK, to do this in subforms, you really don't need to create relationships in
the Relationships window; creating subforms via the Access wizard will
automatically link the data for you properly. Let's start with a 3-tiered
system and you can extend it further.

First, start with the data table at the bottom of the hierarchy. In this
example, I'm going to use tblBedrooms. In this table, make sure you have a
foreign key (FK) field called ApartmentID; this establishes the relationship
between a particular BedroomID and a specific apartment. Create a simple
tabular form for tblBedrooms. Open it and make sure it works. You should
have no problems here.

Second, create a form for tblApartments. Make sure this table has a FK field
called SiteID (and naturally its own ApartmentID); this is used to tie a
specific apartment to a site in step three. Create a simple tabular form for
tblApartments. In this form's design mode, drag and drop the form you
created for tblBedrooms. You'll be asked to specify the parent/child field.
This is ApartmentID since you want Access to synchronize the bedroom records
with the current apartment. Save the form and open it. Use it and make sure
you understand what's going on. When you go to a specific apartment, you'll
see the bedrooms linked to that apartment. If you need to add a bedroom,
just do so in the subform. Leave that apartment and go back to it. You'll
see that the bedroom and the apartment have been effortlessly linked (via
ApartmentID).

Third, create a form for tblSites. In design mode for this form, drag and
drop the second form you created for apartments. Indicate that the
parent/child field should be SiteID. That's it. The apartment subform
should work within the sites form just like the bedrooms subform works for
the apartment form.

Continue doing this up to the top level of the hierarchy. I wouldn't do this
with too many levels simply because the form will be very complicated. Also,
this only works with one to many relationships. In other words, a site
cannot belong to more than one vendor but a vendor may own multiple sites.
hth

Thank you for your great reply. It clarified some stuff, but... I am
working on a form with at least one subform.

I have a table of vendors (and vendor ID). The vendors have multiple sites
(and site ID). i have those two tables and they are filled with data. Each
site has an undetermined number of apartments ( I don't know how many until
my inspectors go out), and lastly, each apartment has an undetermined number
of bedrooms and the number of beds in each bedroom has to be noted.

So I need to collect an autonumber for each apartment at a site. And I need
to collect an autonumber for each bedroom at an apartment.

I need each apartment the inspector notes in the form to get an automatic
unique number, but if it's an autonumber, it won't relate to any of the
fields in the other tables because it is not the same data type. --
Thanks

Do you not get how to do it or do you not get why you do it or both? What
training did you go through? I found that the Northwind database was very

[quoted text clipped - 35 lines]

help fields and I am not getting it.
Thanks