Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
How do I create a subsubdatasheet?
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
Asked:
5-
5
1 Solution
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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Be seen. Boost your question’s priority for more expert views and faster solutions
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Could you please explain what you want in a different way,
it is not clear what you want.
jaffer