Limit Combobox list

[Micron]

So the file you uploaded was quite bare and I my posts can only reflect what I see. Even so, I still don't get it after your last post.
Start Date, Estd Finish Date, this looks like info specific to a project so I don't get why it would be in a stages table. My understanding is that if a project can go through stages, you should have a table with those stages listed - nothing else (aside from whatever may be related to the stage).
Cost Information - this looks like it should be in it's own table, linked back to the project ID
which are specific to only one Project in the Projects table - if it's specific to the project, it belongs in the project table, not the stages table
So one Project will have many stages which are completely specific to that Project. Unless you are keeping historical data, such as how many hours booked by whom at each specific stage, won't you just be updating the stage as it changes? If that's your intent, get ready for MUCH bigger challenges as you seek to maintain a history component of your database.
If the Stages table has no reference to the ProjectID, how will the stages be correctly related to their specific Project?
I've shown the relationship in the image. The assumption is that you will update the stage as it changes (per previous comment).

In my last post (assuming no make and model table)
table about cars: Make, Model, Colour - this is what I see as equivalent to the project.
table about colour options: red, green, blue. This I see as equivalent to stage - the project can be red, green or blue (or perhaps unapproved, approved, closed)
So, Ford, Fusion, 3 (colour ID for blue). When the project status changes (or car is painted)
Ford, Fusion, 2 or
Projects.ProjectID (1); Projects.ProjectStageID (1) [unapproved] at some point becomes
Projects.ProjectID (1); Projects.ProjectStageID (2) [approved]


If we're not in agreement, then either I am not tuned in to the business model you're working in, or you might want to research database normalization to see if anything tweaks afterwards.

[gw2013]

Hi Micron,

Apologies, I think it's a combination of too-simple an example and my terminology.

'Unless you are keeping historical data, such as how many hours booked by whom at each specific stage'

Yup, this is what I'm after.

Maybe the word 'stage' is the problem. Let's say it's Projects and Tasks. So I want to create a list of 'Tasks' for each project and record the hours spent per task by each employee. So at the end of a project, I can see how many hours were spent on each task.

So when it would come to recording the time spent on a task: the complete list of Projects would be available for any employee to select from. They first select the Project which then displays the list of tasks for that project which they select from and they then enter their hours for that task, for that day.

It's approaching 1am here so I'm gonna go but if you have the patience to reply, I'll check back in the morning ... Thank you for all your help.

[Micron]

Ever see that commercial for Bits & Bites (a snack mix in a bag) where the cartoon guy sticks his hand in the box, pulls out a handfull and remarks that everytime he does this "it's a whole new ball game"? That's what we have here.
Maybe a new thread is in order - something along the lines of "Need help creating a Project-Task Timekeeping database?
Here's something I threw together as a starter for table design - based on my probably flawed understanding of what you're trying to accomplish!

ProjTaskDB.zip

[gw2013]

Thanks.


My original question was how users can input their hours on particular Tasks which are related to a Project.
(but by describing Tasks as 'Stages', I completely bamboozled you so apologies for that).


So, now that we're talking Tasks, my original question (changed to 'Tasks' below) still stands as described in post #7:




So the idea is this will be a form filled out at the end of a day.


1. User opens form,
2. Selects their name,
3. In the subform(?), they need to Select the project, then select an individual task on that project enter their hours.
Each entry will default to today's date.
4. Ideally, as they fill out their hours, there would be a field at the bottom updating to show total hours entered for the day.


So I guess it's like a timesheet of sorts?




My original sample database was pretty much a simpler version of the spec you've provided.
So my question is still as outlined as above.

[Micron]

Thanks.
My original question was how users can input their hours on particular Tasks which are related to a Project.
(but by describing Tasks as 'Stages', I completely bamboozled you so apologies for that).
So, now that we're talking Tasks, my original question (changed to 'Tasks' below) still stands as described in post #7:

So the idea is this will be a form filled out at the end of a day.
1. User opens form,
2. Selects their name,
3. In the subform(?), they need to Select the project, then select an individual task on that project enter their hours.
No, in posts 2 and 8 (I believe) we said to put the Name and Project combos in the header. Now Task also - unless you want a datasheet of all tasks for selected project for a day.
Each entry will default to today's date.
4. Ideally, as they fill out their hours, there would be a field at the bottom updating to show total hours entered for the day.
I don't think a totals control will work in a subform datasheet even if the subform has a footer because the datasheet row count is volatile and the subform control height is fixed. As well, the footer is not part of the data set, so I would not expect it to show. In the main form footer, you could try a control with =DSum("NoofHours","ProjectHours","ProjectID= " & [Me].[cmbProject]) as its source. Dsum should not pose a noticeable lag unless you end up with mega records.

So I guess it's like a timesheet of sorts?
I would agree.

My original sample database was pretty much a simpler version of the spec you've provided.
So my question is still as outlined as above.

However, from what I saw of your db, your form may open with #Name in the subform (if subform is based on a query that gets criteria from main form controls that are blank and there are no Parent/Child relationships). If that occurs, you can live with it until combo selections are made, or hide the subform on main form opening and make it visible after the last header combo's After_Update. I was playing around with it, but since I was on a different track with respect to tasks/stages, my set of tables and relationships are screwed up now. Sorry if you didn't find the spreadsheet useful.

[gw2013]

Hi Again!

The spreadsheet was great, gave me some ideas and clarified things a lot.

I was just making the point in my last post that my main query was how to get the hours correctly assigned to the tasks via a form.

Thanks for the advice above.

I'll plough on and hopefully get it working...

[Micron]

Depends on how your form/subform is related and what the records are based on. Take a look at this before delving in too far
https://support.office.com/en-us/art...rs=en-US&ad=US