Solved

How do I create a subsubdatasheet?

Posted on 2004-09-15
10
248 Views
Last Modified: 2006-11-17
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
Comment
Question by:dustinbuss
  • 5
  • 5
10 Comments
 
LVL 27

Expert Comment

by:jjafferr
ID: 12068813
Hi dustinbuss,

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

jaffer
0
 
LVL 1

Author Comment

by:dustinbuss
ID: 12071327
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
 
LVL 1

Author Comment

by:dustinbuss
ID: 12077333
anyone?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 27

Expert Comment

by:jjafferr
ID: 12077841
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
 
LVL 1

Author Comment

by:dustinbuss
ID: 12078374
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 12078420
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
 
LVL 1

Author Comment

by:dustinbuss
ID: 12078543
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 12078627
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
 
LVL 1

Author Comment

by:dustinbuss
ID: 12079027
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
 
LVL 27

Accepted Solution

by:
jjafferr earned 500 total points
ID: 12079571
>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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question