Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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
0
dustinbuss
Asked:
dustinbuss
  • 5
  • 5
1 Solution
 
jjafferrCommented:
Hi dustinbuss,

Could you please explain what you want in a different way,
it is not clear what you want.

jaffer
0
 
dustinbussAuthor Commented:
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
0
 
dustinbussAuthor Commented:
anyone?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
jjafferrCommented:
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
0
 
dustinbussAuthor Commented:
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?
0
 
jjafferrCommented:
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 |
0
 
dustinbussAuthor Commented:
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?  

0
 
jjafferrCommented:
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
0
 
dustinbussAuthor Commented:
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!
0
 
jjafferrCommented:
>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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now