Solved

How do I create a subsubdatasheet?

Posted on 2004-09-15
10
246 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
Comment Utility
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
Comment Utility
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
Comment Utility
anyone?
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now