Solved Tables and multivalued fields (1 Viewer)

[Teri Bridges]

So here I am with another table Question. I removed all the Look-up fields from my table. I had one field that was a multi-value field (roles). A client may be assigned multiple roles. I now understand that is bad, bad, bad.

I am creating my form and need to assign the roles to the client. Is a sub-form the best approach for this?

I already have 6 sub-forms so I was hoping to avoid another. My approach is to put the sub-forms on page tabs (tab control). Any suggestions?

 

[Teri Bridges]

So here I am with another table Question. I removed all the Look-up fields from my table. I had one field that was a multi-value field (roles). A client may be assigned multiple roles. I now understand that is bad, bad, bad.

I am creating my form and need to assign the roles to the client. Is a sub-form the best approach for this?

I already have 6 sub-forms so I was hoping to avoid another. My approach is to put the sub-forms on page tabs (tab control). Any suggestions?

I should also add that I have removed calculated fields from my tables. If I understand correctly, I should place those on forms and reports. There is no need to store that data in a table.

 

[The_Doc_Man]

What you have described is not uncommon. You have a low number of posts but suggested in another post that you had taken some classes so you have had SOME exposure to table design. If you got anything on database normalization from those classes, you know that there are independent tables and dependent tables (often referred to using a "parent/child" model).

In the case where a person can hold more than one role, you are about to enter into a many/many relationship, because you have a table of persons and a table of possible roles. You want something called a "junction" table. In its simplest form, you start with a person table with a person ID and a role table with a role ID. You add a junction table, which has two fields at minimum - the PersonID and RoleID. (Other fields are possible but in the simplest case are not necessary. You can ask separately later.)

Basing SELECT queries off the junction table, you can JOIN PersonRole to the Person table on PersonID and see a list of roles held by the person. OR you can JOIN PersonRole to the Role table on RoleID and see a list of persons in a particular role. Note that you can do a three-way join to get data from the two single-topic tables to populate a query for reports.

The things to look up here if you are not sure are these key concepts: Junction table and JOIN query. Do some reading if these are not familiar to you. Also, database normalization is a good topic if you are not sure. As a side note, since this IS a database forum, looking up normalization is enough. However, in a web search you must include the term database in your search because other disciplines also use the term normalization and that could confuse you.

 

[plog]

Is a sub-form the best approach for this?

Yes.

My approach is to put the sub-forms on page tabs (tab control). Any suggestions?

That would be my approach as well.

There is no need to store that data in a table.

Correct as well.

Lastly, I'd like a look at your table structure. It sounds like you are headed in the right direction (with this thread and the other you started), but I'd like to make sure you have set up your tables correct. Can you complete the Relationship Tool in Access, expand all the tables so we can see all field names, take a screenshot and then post it here? That way we can help make sure you have the correct structure.

 

[Teri Bridges]

Yes.



That would be my approach as well.



Correct as well.

Lastly, I'd like a look at your table structure. It sounds like you are headed in the right direction (with this thread and the other you started), but I'd like to make sure you have set up your tables correct. Can you complete the Relationship Tool in Access, expand all the tables so we can see all field names, take a screenshot and then post it here? That way we can help make sure you have the correct structure.

Is this what you are asking for? Here are the relationships I have so far.

 

[MarkK]

I have removed calculated fields from my tables

I like to calculate results in a query.

 

[Teri Bridges]

Yes.



That would be my approach as well.



Correct as well.

Lastly, I'd like a look at your table structure. It sounds like you are headed in the right direction (with this thread and the other you started), but I'd like to make sure you have set up your tables correct. Can you complete the Relationship Tool in Access, expand all the tables so we can see all field names, take a screenshot and then post it here? That way we can help make sure you have the correct structure.

Sorry I think I replied to the wrong Post. Here are my table relationships.

 

[Teri Bridges]

What you have described is not uncommon. You have a low number of posts but suggested in another post that you had taken some classes so you have had SOME exposure to table design. If you got anything on database normalization from those classes, you know that there are independent tables and dependent tables (often referred to using a "parent/child" model).

In the case where a person can hold more than one role, you are about to enter into a many/many relationship, because you have a table of persons and a table of possible roles. You want something called a "junction" table. In its simplest form, you start with a person table with a person ID and a role table with a role ID. You add a junction table, which has two fields at minimum - the PersonID and RoleID. (Other fields are possible but in the simplest case are not necessary. You can ask separately later.)

Basing SELECT queries off the junction table, you can JOIN PersonRole to the Person table on PersonID and see a list of roles held by the person. OR you can JOIN PersonRole to the Role table on RoleID and see a list of persons in a particular role. Note that you can do a three-way join to get data from the two single-topic tables to populate a query for reports.

The things to look up here if you are not sure are these key concepts: Junction table and JOIN query. Do some reading if these are not familiar to you. Also, database normalization is a good topic if you are not sure. As a side note, since this IS a database forum, looking up normalization is enough. However, in a web search you must include the term database in your search because other disciplines also use the term normalization and that could confuse you.

OH, Those topics have not been in any of the courses I have bought. I now have my next research topic. Thank you. Sometimes I feel I am taking one step forward... LOL

 

[Teri Bridges]

Yes.



That would be my approach as well.



Correct as well.

Lastly, I'd like a look at your table structure. It sounds like you are headed in the right direction (with this thread and the other you started), but I'd like to make sure you have set up your tables correct. Can you complete the Relationship Tool in Access, expand all the tables so we can see all field names, take a screenshot and then post it here? That way we can help make sure you have the correct structure.

I could actually attach the db.

 

[plog]

Those tables look really good. The only question I have is--how do you have a form with many subforms on it? Your table structure doesn't really seem like that is necessary.

 

[The_Doc_Man]

OH, Those topics have not been in any of the courses I have bought. I now have my next research topic. Thank you. Sometimes I feel I am taking one step forward... LOL

 

[Teri Bridges]

Those tables look really good. The only question I have is--how do you have a form with many subforms on it? Your table structure doesn't really seem like that is necessary.

I do not have a Course ID in every Table. All the items in the subforms link back to the course. it goes something like this...
There is one course, the course may have many lessons, Lessons may have many topics, topics may have many events, and events have many review cycles.

I bound the lesson to the course, the topic to the lesson, the event to the topic, and the review cycle to the event.

I would be open to suggestions

 

[plog]

The way I would do it is a form that lists every course with a button beside them. That button goes to a form that shows just one course with a subform that shows all its lessons with a button next to each lesson. That button goes to a form that shows just one lesson with a subform that shows al its topics with a button next to each topic. That button goes to a form that shows just one topic...etc. etc.

 

[Teri Bridges]

The way I would do it is a form that lists every course with a button beside them. That button goes to a form that shows just one course with a subform that shows all its lessons with a button next to each lesson. That button goes to a form that shows just one lesson with a subform that shows al its topics with a button next to each topic. That button goes to a form that shows just one topic...etc. etc.

Oh my, I am not sure my skill set is there yet. I need to draw this out so I can see it in my mind and then I have to work on how to make it work. I initially tried something like this but I had a hard time getting the buttons to make the correct connection. For example, if I was on course 1 getting the lesson to open matching that course, if I had a change to log getting the button to open to the change log with the course field populated. It appears I have a lot of work to do. Thanks you for all your help. I value all the input.

 

[Gasman]

Use the WHERE parameter of the OpenForm command.

 

[Teri Bridges]

The way I would do it is a form that lists every course with a button beside them. That button goes to a form that shows just one course with a subform that shows all its lessons with a button next to each lesson. That button goes to a form that shows just one lesson with a subform that shows al its topics with a button next to each topic. That button goes to a form that shows just one topic...etc. etc.

Ok I am able to from the course list form go to the lesson list and the lesson form connects to the corrects to the correct lesson and course. The issue I face is on the lesson form I have an add lesson button, when I click that button it drops the course ID. So when I add a new lesson it is not bound to the course. I think I need to do something with got focus. I am not sure how to keep the new record bound to the course. I hope that made sense.

 

[Teri Bridges]

Oh my, I am not sure my skill set is there yet. I need to draw this out so I can see it in my mind and then I have to work on how to make it work. I initially tried something like this but I had a hard time getting the buttons to make the correct connection. For example, if I was on course 1 getting the lesson to open matching that course, if I had a change to log getting the button to open to the change log with the course field populated. It appears I have a lot of work to do. Thanks you for all your help. I value all the input.

I changed the forms to continuous forms, which is better than single form. I do not know how to get the course ID to auto-populate on new entry. I can manually enter it but I need it to auto populate.

 

[Teri Bridges]

Use the WHERE parameter of the OpenForm command.

Thank you that worked

 

[plog]

Close but nope. You need:

A form to list all courses with a button next to each course. When a course button is clicked it opens...
A form that has just one course with a subform for all its lessons with a button next to each lesson. When a lesson button is clicked it opens...
A form that has just one lesson with a subform for all its topics with a button next to each topic. When a topic button is clicked it opens...
etc. etc.

You've independently built forms for each category. Your lesson form needs to be a subform to a form that shows just one course. When you do that you can tie the course to the lesson like so:

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.microsoft.com

 

[Teri Bridges]

Close but nope. You need:

A form to list all courses with a button next to each course. When a course button is clicked it opens...
A form that has just one course with a subform for all its lessons with a button next to each lesson. When a lesson button is clicked it opens...
A form that has just one lesson with a subform for all its topics with a button next to each topic. When a topic button is clicked it opens...
etc. etc.

You've independently built forms for each category. Your lesson form needs to be a subform to a form that shows just one course. When you do that you can tie the course to the lesson like so:

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.microsoft.com

Okay Let me work on this. Again thanks for the guidance.