HELP! Forms - Subforms - Combo Boxes

[jlgray0127]

Hello all!
I am having a dilema! I have two tables tied to two forms. There is a relationship.
Table one is called ProgramsNew. It lists specific programs my team is working on. Tabel 2 is Program Acitivites Selection. it contains different GW (GateWays) and activities that may pertain to the project.
Based off of the two tables, a new program is setup on a main form, (ProgramsNew). The subform is referenced to a checklist and status table (Programs Activites Select) for a project. The values of this table never change.
When an individual enters a new program in the main form, they go to the subform, and pick from the list, the different activities that relevant to the project for each Gateway. So in the list, you have GW1 and it lists 12 tasks that can possibly be selected for this program. The colums would be GateWay, which lists the multiple gateways of a project. GW1 - GW8 and there are several tasks listed for each GW.
After the Tasks are selected, the program and it's relevent tasks are tracked through two tables that hold the program data. One lists the program and the Dates of each Gateway. The other table list the project and the Tasks and then a Status Criteria of each task that needs to be selected as we report the phase of the project during each gateway. The records would be GW1 and then list the tasks for that gateway and if they are relevent, and then it lists the status of that task... Each GW and Task, has 3 predifined phases it can be in.



A sample of what one task and the status is listed below.

Project GW Task Status Score State
Project Alpha GW1 Contact Suppliers <50% of suppliers contacted 0
Project Alpha GW1 Contact Suppliers >50% but <75% of Suppliers Contacted 3
Project Alpha GW1 Contact Suppliers >75% of Suppliers Contacted 9


The subform, I would want the above data to look like this:

Project GW Task Status (combo box to select status) Score (updates from selecting status) State (updates to "Current State" or something when the Status is selected.
Project Alpha GW1 Contact Suppliers <50% of suppliers contacted 0

This is where I'm getting confused.

I need to be able to maintain the Project and it's status. What I want, is for a Main Form to have the project information in it from the Table Programs, which I have done.
I need a subform (Sample Data Above) referencing the second table, called GW Activities Status
What I want to happen here, is for the GW to list the Task, and then a combo box to select the Status and by selecting the status, to store the Score. In the afterupdate event, I would have the field State changed to some kind of indicator for my report to identify it as the current status of the task so I can run a report to gather the scores.

I know this was winded, but its the best way I could think to explain it to get started finding the solution.

[rpeare]

Let's simplify this a bit.

You have a project
Each project has many tasks
Each task is going to have a status (complete, pending, etc)

You want a project score based on the number of tasks assigned to that project that are 'complete'?

So for instance

Project A has 10 tasks
3 of the tasks are complete
4 are pending
3 haven't been started

You want your project to reflect 30% complete?

[jlgray0127]

Kind of... I get a score for it, yes. The way I was asked to set up the values is,
Status 1 = 0
Status 2 = 3
Status 3 = 9

So, for each activity, I choose a status and that determines my score for the task. This scoring system is designed to eliminate mediocrity and push for continuous improvement.
For instance, if GW 1 has 5 tasks, my total points available, is 45. So, if I have a 2 status 2 selections, and 3 status 9, this would result in a score of 73%

[rpeare]

Are all projects scored the same way? I don't see a problem with scoring this if the maximum score is 9x the number of tasks and the scoring is as simple as you have shown.

So what is the question how do you get this calculation if you have a table for your projects and a table for your tasks for each project?

[jlgray0127]

Yes. They will all be scored the same way. My question, is how do I get the combo box to only display the 3 status that are tied directly to the task.
What I want to see, when I open the main form, is a sub for that list GW1, the Activity, and then a combo box that has the 3 different specific status assigned to task.
There are 3 unique status for each activity, for each GW. So... basiscally, there are approx 73 tasks total for all the GW. This results in 219 unique status'. The status refers specifically to the task, and what phase it's in, on it's way to completeness.

Similar to this:

Project Alpha GW1 Contact Suppliers <50% of suppliers contacted 0
Project Alpha GW1 Contact Suppliers >50% but <75% of Suppliers Contacted 3
Project Alpha GW1 Contact Suppliers >75% of Suppliers Contacted 9

I want my subform to to look like this:

PROJECT GW ACTIVITY STATUS (COMBO) SCORE

Project Alpha GW1 Contact Supplier [combo box for status] [resulted score 0,3,9 for result of combo box]

Hope this helps! thank you!!!!

[rpeare]

how about supplying an example of your database, each task should only have one status, that status should be updated as you go along unless the status on any given date is of importance (i.e. you need to know how long a task languished in phase 1).

[jlgray0127]

I have a list that is associated with the tasks. I want the form to show the task, and use the combo box to find the 3 status's associated with that task, so I can select the single status for the task.

[rpeare]

if you're asking how you get a list of status items create a table with your status information

tblStatusList
Stat_ID  StatScore  StatText
1        0          Status 1
2        3          Status 2
3        9          Status 3

Then just make a combo box that references this table. You can do that either through the combo box dialog or build it yourself

[jlgray0127]

Here is a snap shot... as you can hopefully see, GW1 is list multiple times. The activity is listed 3 times for each activity. Then there is a unique status for each Activity. I want the GW and Activity on one line, with a combo box, which looks at the Status field relevant to the GW1 and Activity. This way, it would only display the 3 status that pertain to GW1 Attend GW1 Review.

[rpeare]

Are you attempting to use this for data entry? This looks like either a table, a query or a form in datasheet view and I can not tell which. But I suspect you are attempting data entry.

Is the STATUS field a text string associated with the activity? so for instance every time you have the activity DEVELOP PFEP PLAN the same three status descriptions are shown?

If the STATUS Is related to the SCORE then the table holding your scoring is probably includes an activity ID that is linked to your activity table but really what you want is a combo box that lists possibilities and what it looks like you've done is just connect the relevant tables in a select query which is giving you all possible results which clearly isn't what you want.

[jlgray0127]

This is a table in data view. I think I have my structuring jacked up. I have this in two tables as well, where I have the GW and Activity in Table 1 and then I have this table... I am thinking, I need to have Table one in a form, and then reference table two, to the combo box, correct? I have done this before, but it's been years.... lol

[rpeare]

you shouldn't be doing data entry on queries, period.

You should be doing this on form/subforms if you're trying to enforce referential integrity (or if you have a good grasp of vba and db concepts using an unbound form)