Subform Design Question

[bballhermit]

I am trying to design a form/subform combo as described here:

The main form has combo boxes selecting the contract, task order, project, and year as filters. In the subform, then, I want to have all employees that have hours assigned to that project in that year to be displayed.

The subform I want to look like this where the month names and totals are the number of hours that employee is assigned to that project that month.


Last Name | First Name | Oct | Nov | ... | Aug | Sep | Total
.
.
.

Could you help me design a query and set up the subform to do this or point me to a resource/tutorial that would walk me through it? I am very new to Access.

Thank you.

My Table Structures:

tblWorkingHours
MonthId (Key)
Year
WorkingHours (Business hours in month)
MonthNum (1-12)

tblAssignments
AssignmentID (Key)
EmployeeID
ProjectID
MonthID
Hours (Hours this employee is assigned to this project this month.)

tblEmployees
EmployeeID
LastName
FirstName
Rate

 

[MarkK]

It doesn't make sense to me to link to a date. I think I might have mentioned this to you in the past. Obviously you can do it, but to me it's like having a person table and linking to the name. The name inextricably belongs to the person. Why not put it in the person table?
I use this same argument when discussing the date of an assignment. The date is inextricably a dimension of the assignment.

 

[bballhermit]

Yes, you did mention that before, and I am definitely open to eliminating this link/table, however that's the only way I know how to store and access the amount of business hours in any given month.

 

[gemma-the-husky]

if the main form is a project - then have the main form show the project and include two controls for the date range

then have a query that selects employee hours for that project, between the dates.

show this as a subform.

you just need to requery the subform everytime you change data on the main form.

-------------

you could just as easily do this with an outer container form, and two subforms

I do quite a few thnigs like this, based around dates.

 

[MarkK]

For me, I think I've been clear in another thread about my thoughts on this design so I won't repeat that.
To write the query you describe I would open the query designer and specify that you want a crosstab query and go from there. I think the error messages for crosstabs are pretty good so the system will keep you posted on what you're doing wrong. I find crosstabs tricky but that just means that it takes me longer to get the output exactly the way I want it. Just keep kicking at it.
The problem with a crosstab is that the columns--how many there are and what they are called--is a function of your data. That makes it hard to base a form on a crosstab because the column named Oct, though it is the first control on your form, may not--after a few months of data updates--be the first column in the query.
Hope this helps,

 

[bballhermit]

Ok, thanks. I'll give that a shot.