Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How do I create a subsubdatasheet?
Posted on 2004-09-15
I am trying to create a table that will have a subsubdatasheet, and I'm having some trouble. Here is a simplified version of the structure:
One table has a user assigned to a specific role (chosen from a linked table of possible roles). From there, depending on which role they choose, I'd like to have different sub fields. If they choose Role 1, we may have to track data X and Y, but if they choose Role 2, we'll be interested in tracking data A and B.
Is it possible to do something like this? I'm not sure whether I should be using queries or subdatasheets. I hope this all made sense.
Thanks in advance!
Dustin
One table has a user assigned to a specific role (chosen from a linked table of possible roles). From there, depending on which role they choose, I'd like to have different sub fields. If they choose Role 1, we may have to track data X and Y, but if they choose Role 2, we'll be interested in tracking data A and B.
Is it possible to do something like this? I'm not sure whether I should be using queries or subdatasheets. I hope this all made sense.
Thanks in advance!
Dustin
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
5
10 Comments
Hi dustinbuss,
Could you please explain what you want in a different way,
it is not clear what you want.
jaffer
Could you please explain what you want in a different way,
it is not clear what you want.
jaffer
Let me try to show it a different way:
First, I have three tables:
Table 1 (Participant) - individuals assigned to roles:
| ID | Name | Role |
| 11 | Doug | 05 | <-- example
Table 2 (Roles) - a simple table with all the possible roles in it:
| ID | Role |
| 05 | Boss | <-- example
Rable 3 (Role Data) - more information needed, depending on the role:
| Role | Quota | Team |
| 05 | 20u | Blue | <-- example
Linking up the first two tables is easy. I can set it up so Table 2 is related to Table 1, so when I'm entering new people into the database, I can select a role for them from Table 2. The problem is, depending on which role I select for them I will need additional data (the data is not the same for each role), so somehow I need to create a Sub-Sub-Datasheet or something that will allow me to Enter a Person, Select Their Role, and then enter the Data pertaining to that Role.
Can anyone help with this?
Thanks,
Dustin
First, I have three tables:
Table 1 (Participant) - individuals assigned to roles:
| ID | Name | Role |
| 11 | Doug | 05 | <-- example
Table 2 (Roles) - a simple table with all the possible roles in it:
| ID | Role |
| 05 | Boss | <-- example
Rable 3 (Role Data) - more information needed, depending on the role:
| Role | Quota | Team |
| 05 | 20u | Blue | <-- example
Linking up the first two tables is easy. I can set it up so Table 2 is related to Table 1, so when I'm entering new people into the database, I can select a role for them from Table 2. The problem is, depending on which role I select for them I will need additional data (the data is not the same for each role), so somehow I need to create a Sub-Sub-Datasheet or something that will allow me to Enter a Person, Select Their Role, and then enter the Data pertaining to that Role.
Can anyone help with this?
Thanks,
Dustin
Sorry Dustin, I forgot your post.
Go to Table2, > Insert > SubDatasheet > Select Table3
this will make Table3 as a SubDatasheet of Table2
Go to Table1, > Insert > SubDatasheet > Select Table2
this will make Table2 as a SubDatasheet of Table1
ans since Table3 is already a SubDatasheet of Table2,
so in Table1 when you click on the + sign, Table2 will open asSubDatasheet, and when you click on the + sign of Table2, Table3 opens in SubDatasheet.
jaffer
Go to Table2, > Insert > SubDatasheet > Select Table3
this will make Table3 as a SubDatasheet of Table2
Go to Table1, > Insert > SubDatasheet > Select Table2
this will make Table2 as a SubDatasheet of Table1
ans since Table3 is already a SubDatasheet of Table2,
so in Table1 when you click on the + sign, Table2 will open asSubDatasheet, and when you click on the + sign of Table2, Table3 opens in SubDatasheet.
jaffer
I've been able to do this to connec tht tables, but the problem is that I was Table 3 to show different fields depending on what is selected in Table 2.
Perhaps I need it to look like this instead
Table 1
|
Table 2
|
Table 3 or 4 or 5
Does that make sense?
Perhaps I need it to look like this instead
Table 1
|
Table 2
|
Table 3 or 4 or 5
Does that make sense?
Yes you are right, except, No need for 4 and 5
because in Table 3 you will have all the fields that are related to Role number form Table2,
so in Table2, if Role=05
and in Table3 you have
| Role | Quota | Team |
| 05 | 20u | Blue |
| 01 | 10u | Red |
| 05 | 22u | green |
in Table2 when you click on the + sign, you should get
| 05 | 20u | Blue |
| 05 | 22u | green |
because in Table 3 you will have all the fields that are related to Role number form Table2,
so in Table2, if Role=05
and in Table3 you have
| Role | Quota | Team |
| 05 | 20u | Blue |
| 01 | 10u | Red |
| 05 | 22u | green |
in Table2 when you click on the + sign, you should get
| 05 | 20u | Blue |
| 05 | 22u | green |
I need to be able to add and change data, though in all the tables simply. That doesn't seem to allow me to do something like this:
| Name | Role |
+ | Role 1 | Role 2 | Role 3 |
+ | Data1 | Data 2 | Data 3 |
I'd like to be able to click on Role, select a role, then it give me a third row to fill in the specifics. Is it possible to make that very last table just blanks to enter into?
| Name | Role |
+ | Role 1 | Role 2 | Role 3 |
+ | Data1 | Data 2 | Data 3 |
I'd like to be able to click on Role, select a role, then it give me a third row to fill in the specifics. Is it possible to make that very last table just blanks to enter into?
What you will have is something like this:
| Name | Role |
+ | Role 1 | Role ID 1 |
+ | Role 1 | Quote 1 | Team 1 |
| Role 1 | Quote 2 | Team 2 |
dustinbuss
I think you asked the wrong question, sorry pal,
so why don't you give me an example of what you want to do, and let me help you in terms of telling how best to do it in Access.
please make this example contain more than 1 record, so that I can understand it better,
and if you have some data in a txt file or something, then you can post it here too.
jaffer
| Name | Role |
+ | Role 1 | Role ID 1 |
+ | Role 1 | Quote 1 | Team 1 |
| Role 1 | Quote 2 | Team 2 |
dustinbuss
I think you asked the wrong question, sorry pal,
so why don't you give me an example of what you want to do, and let me help you in terms of telling how best to do it in Access.
please make this example contain more than 1 record, so that I can understand it better,
and if you have some data in a txt file or something, then you can post it here too.
jaffer
Okay, so here's what I'm trying to do:
Add a person to database
Assign a couple roles to them
Then assign a couple sub roles to them
I want to later be able to reference who's in which roles, which subroles, etc.
So, let's say we have the following roles:
Assembly or Senate
In Assembly we have:
Group 1 or Group 2
In Senate we have:
Section A or Section B
I'm hoping to be able to enter a new person into the system, and assign them to these roles, all in one go. Do you think this can be done simply?
Thanks!
Add a person to database
Assign a couple roles to them
Then assign a couple sub roles to them
I want to later be able to reference who's in which roles, which subroles, etc.
So, let's say we have the following roles:
Assembly or Senate
In Assembly we have:
Group 1 or Group 2
In Senate we have:
Section A or Section B
I'm hoping to be able to enter a new person into the system, and assign them to these roles, all in one go. Do you think this can be done simply?
Thanks!
>Add a person to database
>Assign a couple roles to them
>Then assign a couple sub roles to them
>I want to later be able to reference who's in which roles, which subroles, etc.
Yes very much possible.
You can make 3 Tables like what you proposed, but they all should have ID (autonumber) which belongs to a person:
Table 1 (Participant) - assign individuals and give each an ID (autonumber):
| ID | Name |
| 11 | Doug | <-- example
Table 2 (Roles) - a simple table with all the possible roles in it, ID (number) is linked to Table 1:
| ID | Role |
| 11 | Boss | <-- example
Table 3 (Role Data) - more information needed, depending on the role:
| Role | Quota | Team |
| Boss | 20u | Blue | <-- example
| Boss | 10u | Red |
So at any Point is you want a Report about who are the the people with the Role Boss, you should be able to know them all,
and if you wanted to know the people who part of Team Red, you should be able to know them all.
Make Form frmParticipant from Table tblParticipant, make it a sigle Form,
Make Form frmRoles from Table tblRoles, make it a continuou Form,
Make Form frmRoleData from Table tblRoleData, make it a continuou Form,
Open Form frmParticipant in Design view,
Click on the Subform/Subreport button, Drag a Rectangel in the Detail section,
then select frmRoles from the list,
the Master/child relation should be the field ID.
Click on the Subform/Subreport button, Drag a Rectangel in the Footer section of Form frmRoles,
then select frmRoleData from the list,
the Master/child relation should be the field Role.
Save the main Form frmParticipant.
If the Roles are known, then I propose you make the Role field as a Combobox, so that you don't have to remember the Roles and can select it from the pull down menu, same thing for the other items too.
Now when you go back and forth to any persons record (in Form frmParticipant), the 2 subforms will automatically change based on what you entered for them, and you can always edit or add more info.
Since you mensioned the word Datasheet, you can set the view for both the subforms to Datasheet (this will not affect the functionality).
If this is what you want, and you need further assistance from me, please let me know.
I will only be able to see you comments after I wake up, in the morning.
jaffer
>Assign a couple roles to them
>Then assign a couple sub roles to them
>I want to later be able to reference who's in which roles, which subroles, etc.
Yes very much possible.
You can make 3 Tables like what you proposed, but they all should have ID (autonumber) which belongs to a person:
Table 1 (Participant) - assign individuals and give each an ID (autonumber):
| ID | Name |
| 11 | Doug | <-- example
Table 2 (Roles) - a simple table with all the possible roles in it, ID (number) is linked to Table 1:
| ID | Role |
| 11 | Boss | <-- example
Table 3 (Role Data) - more information needed, depending on the role:
| Role | Quota | Team |
| Boss | 20u | Blue | <-- example
| Boss | 10u | Red |
So at any Point is you want a Report about who are the the people with the Role Boss, you should be able to know them all,
and if you wanted to know the people who part of Team Red, you should be able to know them all.
Make Form frmParticipant from Table tblParticipant, make it a sigle Form,
Make Form frmRoles from Table tblRoles, make it a continuou Form,
Make Form frmRoleData from Table tblRoleData, make it a continuou Form,
Open Form frmParticipant in Design view,
Click on the Subform/Subreport button, Drag a Rectangel in the Detail section,
then select frmRoles from the list,
the Master/child relation should be the field ID.
Click on the Subform/Subreport button, Drag a Rectangel in the Footer section of Form frmRoles,
then select frmRoleData from the list,
the Master/child relation should be the field Role.
Save the main Form frmParticipant.
If the Roles are known, then I propose you make the Role field as a Combobox, so that you don't have to remember the Roles and can select it from the pull down menu, same thing for the other items too.
Now when you go back and forth to any persons record (in Form frmParticipant), the 2 subforms will automatically change based on what you entered for them, and you can always edit or add more info.
Since you mensioned the word Datasheet, you can set the view for both the subforms to Datasheet (this will not affect the functionality).
If this is what you want, and you need further assistance from me, please let me know.
I will only be able to see you comments after I wake up, in the morning.
jaffer
Featured Post
Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 17 hours left to enroll
635 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.