Help Understaning Relationships

[Nathan87]

Hello. I'm trying to learn Access and can't seem to get my head wrapped around a concept that should be fairly straight forward (I think).

I am trying to make a simple database to start with 2 tables that are related with a one to many relationship. An Items table and a Pictures tables. The pictures table can contain multiple pictures for each item.

I was hoping to have the two table linked together so that on a form with with the Item details I could display the number of pictures. I started out by making 2 items in the Item table and then making 4 records in the Picture table. 3 pictures for Item 1 and 1 picture for Item 2. The problem is when the tables were linked and shown on a form, the form displayed Item 1 3 times (once for each picture). After digging some more in my Access book I realized when you have a 2 table linked together on a form you will get one record for each child and the rest of the information will be filled in for the parent. In other words by design I would have 3 records on the form for Item 1.

I'm not sure why you would want it like that. If I'm trying to show the details of each Item on a form, I don't want the item to show up 3 times because there is 3 pictures linked to that item.

It seems like I'm missing something obvious here.

In case, it helps at all I'm attached the database.

Thanks.

 

[jdraw]

see this link for info on relationships
http://www.techrepublic.com/article...relationships-between-database-tables/5034792


see this for MsAccess Form-Subform set up
https://www.youtube.com/watch?v=YZKN_-P6wck

 

[Nathan87]

Thanks for the links.

I think I found a solution to the problem I was having although I still don't really understand it. Previously, I had a query (I think..) for the source of the form. I had used that so that I could add some calculated fields instead of just the fields from the table. When I switched the source of the form to the actual table then I only have 1 record for each main record on the form.

I'm not sure if this makes sense, but it seems to me like if you have a table linked to another table with a one to many relationship then you can't have a query for the source of a form in which you want one record for each of the One records (I mean on the one side of the one to many relationship).

For the example in the previous link, my take is that if you want a form to display information about each book in the book table, then you can't have the source of the form as a query since the book table is linked to the author's table in a one to many relationship. Otherwise you would get some books showing up twice if they have 2 authors.

Is that how it works?

Thanks.

 

[cookiegary01]

Hi Nathan,
To show only the records from TableA on a Form, you can use the TableA as a source for your Form. The relationship is neglected in this case, because TableB will not be part of the source.

To show records from both TableA and TableB on a form or a Report, you use a query containing TableA and TableB.
I hope that this helps

 

[The_Doc_Man]

There are three (well, four) types of relationships to discuss.

1 to 1 - One record in table A matches one record in table B. Occurs sometimes when you have a bunch of data you use a lot and another bunch you use less often and don't want to always drag around the extended data.

1 to many / many to 1 - Your case of primary record and a bunch of child records, and also a case where many records have data fields that need to be translated, so you use a many/1 relationship for, say, code translation.

many to many - probably closest to the real world of business, and you need an intermediate table to enumerate the links.

no relationship - sometimes, tables just aren't related to each other no matter how much you want them to be.

Now what do you do with those relationships?

In the 1/many case, which is what you described, you have many choices but the most common in Access are the various flavors of JOIN query (INNER, LEFT, RIGHT), the SUMMATION + JOIN case, and the parent/child FORM case.

In the appropriate INNER JOIN query, you have in essence done a lookup of the PARENT's data for the child records. Because you will have one record for each CHILD and the data for the PARENT will be repeated.

You can turn that into a SUMMATION where you show parent data plus a SUMMATION (COUNT, SUM, AVERAGE, etc.) of child data.

The form case is actually probably what you wanted, but you have to remember the viewpoint here. A child table will potentially have many records matching one parent. To keep that from getting out of hand, the parent FORM ties only to the PARENT record. Then build a CHILD FORM or SUBFORM. Tie it ONLY to the CHILD RECORD. BUT... Access lets you tie the parent and child FORMS to each other through a common field, which is usually the CHILD record's FOREIGN KEY to the PARENT record's PRIME KEY. Then the Access form drivers handle the linkage for you.

The pictures you wanted would go into the child or subform. The parent's data stays OUT of the subform and stays on the parent form only.

Remember, a relationship in a table reflects a relationship between the two entities being tracked. If there is no relationship in reality, you can't force one in Access without it being artificial in nature and therefore likely to lead to confusion - hence my comments about unrelated tables.

 

[Nathan87]

Thanks a lot for all the great information. That helps.

I have one more question though. You mentioned that it's possible to show a summation of child data on a form with parent data. Is it possible to do that using a query or is the best way to use a formula on the form?

I wanted to put a count of the number of child pictures for each item on the item details form. I have gotten this working by using:
=DCount("[Picture]![PictureID]","[Picture]","[Picture]![ItemID] = Forms![ItemDetail2]![ItemID]")
as a source for a text box. If it's possible to link the child summation data with the parent data in a query or the main form source it seems like it would make things cleaner. Also, there seems to be a slight delay in the # of pictures showing up on the form and I'm thinking maybe if it was in the main form source directly it would eliminate the lag.

Thanks again.