List value query - 2 layers deep

[blargh88]

Hi All,

I am fairly new to Access but have some experience with basic querying, forms, reports, etc. I'll try to explain the problem with the little DB knowledge that i have, so here goes:

I have 3 tables involved: Tasks, Activities, Contacts

In the Activities table, there is a List Value field called "POC" that is linked to the Contacts table "ID" field so one or more people can be selected (the IDs are numbers that translates to names in the List Value field). The Tasks table also links "Task POC" to the "ID" field on the Contacts table. The activities table also has a field called "Parent Task" that is a combo box querying "Title" from the Tasks table. And on the Tasks table, there is a field called "Task ID" which is a required value for each task. All of this works fine.

Now for the query... I can successfully query for Activity table information based on each separate POC linked to the Contacts table and sort with this:
SELECT Activities.[POC].Value, Activities.Title, Activities.[Parent Task]
FROM Activities INNER JOIN [Contacts] ON Activities.[POC].Value = [Contacts].ID


ORDER BY Activities.[POC].Value;

This is great, works fine and gives me most of the information i want. However I also need to have the Parent Task's Task ID listed so I tried adding in the Task table and listing Task ID as such:
SELECT Activities.[POC].Value, Tasks.[Task ID], Activities.Title, Activities.[Parent Tasker]
FROM (Activities INNER JOIN [Contacts] ON Activities.[POC].Value = [Contacts].ID) INNER JOIN Tasks ON (Tasks.[POC].Value = [Contacts].ID) AND (Activities.[Parent Tasker] = Tasks.Title)
ORDER BY Activities.[POC].Value;

Unfortunately, this doesn't work and the query comes up empty. Then i tried making it simpler and removing the extra link between Contacts.ID and Tasks.POC.Value since that has nothing to do with this query...
SELECT Activities.[POC].Value, Tasks.[Task ID], Activities.Title, Activities.[Parent Tasker]
FROM (Activities INNER JOIN [Contacts] ON Activities.[POC].Value = [Contacts].ID) INNER JOIN Tasks ON Activities.[Parent Tasker] = Tasks.Title
ORDER BY Activities.[POC].Value;




This still doesn't work and now i'm out of ideas with the little experience that i have.

Firstly, is it possible to do this? And if so, could you please give some tips and point out what i'm doing wrong here? I haven't seen anything like this with my limited experience yet, but I'm hoping this is an easy query for you guys.

Any and all help is appreciated! Thank you!

-Albert

[orange]

See if this applies to you
http://access.mvps.org/access/lookupfields.htm

You may want to describe to us exactly what Tasks, Activities, Contacts represent if your environment.
Further, you could tell us some of the rules associated with these things.
eg One Task can have Many Activities....

[blargh88]

Yes, unfortunately it does apply to my situation. However I could not find another way of allowing the user to select multiple distinct values that would all go into one field...but if there is i would be happy to hear it!

As for the environment:
Contacts is a list of contacts with their information (name, number, etc).
Activities holds all of the information related to each (due date, requestor, POC, etc). There can only be ONE parent task per activity and this activity sometimes needs to read information from, but never update, the tasks table.
Tasks holds all of the information related to each (due date, requestor, POC, etc). There can be multiple activities per task, but each of these activities do not influence the information for the tasks.

Thanks!

[orange]

Putting all values into one field is NOT a good thing. Please explain why you want to do this.
Fundamental in RDBMS design is 1 field 1 fact.

I have highlighted the facts within your response that are important to tables and relationships.
The other material does not say much about what each means.

Contacts is a list of contacts..? What is a Contact? "POC"?
Perhaps you can step back a little and tell us what your intended application is about? What is the purpose of this database?


Contacts is a list of contacts with their information (name, number, etc).
Activities holds all of the information related to each (due date, requestor, POC, etc). There can only be ONE parent task per activity and this activity sometimes needs to read information from, but never update, the tasks table.
Tasks holds all of the information related to each (due date, requestor, POC, etc). There can be multiple activities per task, but each of these activities do not influence the information for the tasks.

[blargh88]

I created a form for the user to fill in information that would all go to the appropriate fields. However when they got to selecting people (for POC, for people designated to work on it, etc), they wanted to be able to select one or more people (ad infinitum) at a time from drop box or a check list that would then be stored as part of the task. At the time, a quick google search showed that a lookup field for list values would do exactly that. At first i wanted to do 1 field 1 fact like you mentioned, but figuring out a way to dynamically add fields based on the user filling in forms seemed less efficient considering how many people they could list (e.g. if the user put in 20 people working on one task, the table would need to have 20 fields one for each separate person who is working on the task...).

A contact is a person. I was just assuming Outlook terminology, apologies.

All this DB needs to do is allow the user to input tasks, subtasks (activities), POCs, the people who will work on it, etc. They need to be able to do this through forms so they don't directly touch the DB information (I have forms set up with easy UI). They need to be able to run queries through Access and they will only be using Access (so i'm not worried about whether it's compatible with other DB types). Lastly it needs to generate reports, which I have set up by referring to queries.

I am very familiar with Excel, but like i mentioned earlier have little experience with Access - sorry in advance for any noobish mistakes or design decisions! And thanks in advance!

[orange]

No problem. Access and Excel are very different animals.

At first i wanted to do 1 field 1 fact like you mentioned, but figuring out a way to dynamically add fields based on the user filling in forms seemed less efficient considering how many people they could list (e.g. if the user put in 20 people working on one task, the table would need to have 20 fields one for each separate person who is working on the task...).

This is the heart of your problem. That may be how Excel people would set it up, but not so in Access.
You really need to set up your tables and relationships. That is the "things of interest" and how they relate to each other.

Initially I see, these tables

Task and SubTask(Activity) and Contact but where does Requestor fit?

Do people work on a Task, or do they really work on Activity?

If 20 people could work on an Activity, I would have another Table (Assignment or something) that identifies
the Activity and the Person, There could be 20 records -- one for each person on the activity.

[blargh88]

People can work on both Tasks and Activities, or just one but not the other. So for example, if a particular activity needed some programming expertise and hired a contractor they would list the contractor on the activity, but not on the task because the contractor is only helping with the limited scope of the activity. Essentially each task has a bunch of properties and each activity has a bunch of similar or analogous properties, but they can contain different information. Hence there is a separate table for Tasks and Activities.

Requestor is just another property that Tasks and Activities have, but not really much to do with the problem at hand.

Hmmmm okay. I see what you are saying and that makes sense. But before I go into all of the rework necessary to implement this...is it possible to do the query i originally asked given the situation? The ideal next steps would be to figure out how to do this query quickly and have a "working" database in place because of time constraints. Then I can take some time to rebuild the part that we're talking about after. It will be a long road to redo so much...but trial be fire sure helps to teach lessons.

If it is possible to actually run the original query, that would be awesome. Thanks!

[orange]

Why do you have

Essentially each task has a bunch of properties and each activity has a bunch of similar or analogous properties, but they can contain different information. Hence there is a separate table for Tasks and Activities.

If every Task has at least 1 Activity, then you can relate the Activity(ies) to the Task and sum up the details. No need for details at Task level, they can be calculated in a query when needed.

[blargh88]

I think i'm using bad wording, so please bear with me...

An activity can be assigned a parent task, but can also be standalone so I should have said earlier than an activity can have zero or one tasks related to it. A task can have multiple activities under it, but can also be standalone with no activities (for example because it is a persistent task with activities that only pop up once a week and get finished quickly, or because the task is on hold, or because the task was just created and no resources have been assigned to it yet so there are no activities, etc). There is a difference between these two mostly because of scale - tasks are large and can be comprised of many activities but also have a general goal or separate information that related activities would not contain; activities are smaller and do not necessarily have to have a parent task, but when they do, they can only have one. The sum of all activities that a parent task has is NOT greater than or equal to the task itself. That is why we need details at the Task level as well as the activity level.

[blargh88]

OK, just figured it out. The POCs are using numbers as the actual stored values and the display values are text. However the Parent Task was using text as the actual stored values...so once I changed the Parent task to use numbers as the stored values the query worked fine.

However this will definitely get confusing in the future especially if the DB grows in size and complexity. So I will be taking your advice and remodeling the DB. Thanks for the help!

[orange]

If you have it working, great. But, I think we're on the same wave length so to speak. Close anyway..
However, this may be a show stopper

activities are smaller and do not necessarily have to have a parent task, but when they do, they can only have one.

I think that concepts-wise, this is where we differ, and it may critical.
Every Activity, has one Parent Task ( even if that task is trivial). The reasoning behind this is when you called Activity a subTask. Think about it, can there be a subtask without a Task?

With RDBMS, you can have a Table Task with attributes specific to that Task - Title, Task Manager, Task StartDate....
but anything in a subTask/Activity that rolls up to the Task should be associated with the subTask/Activity and rolled up for reporting etc when needed.

From a planning perspective you may deal at the Task level with some sort of ProjectedStartDate, or ProjectedFinishDate, and use "guesstimates for planning", but use rollups from related subTask/Activities for "real" numbers.

[blargh88]

More good points! I think I will have a field for the level of difficulty per task and per activity. That way there will be trivial tasks instead of taskless activities.

I will look into doing that and probably get some formal training on DBs (you can probably tell this is a situation where i was the closest tech-person on hand, even if I am unfamiliar with the intricacies of DB management).

Again, thanks for all the help! This RDBMS is a new way of thinking for me, but it makes a lot of sense.

[orange]

Here are some free videos that may ease the learning effort.

You might wish to watch these free videos. There are other videos, these are just a sample.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

You can go back and watch these as often as you want.