Help with Staff DB and Team Tracking pls

[seestevecode]

I'm trying to create a Staff Database to track hours spent in various activities each day. Each member of staff is in one team at a time, but moves between teams.

I've simplified my table set-up for demonstration

Staff
StaffID - PK for Staff table
StaffName

Team
TeamID - PK for Team table
TeamName

Activity
ActivityID - PK for Activity table
ActivityName

TeamStaff (TeamID, StaffID and StartDate form PK)
TeamID - FK from Team table
StaffID - FK from Staff table
StartDate - start date in team
EndDate - end date in team

StaffActivity (ActivityDate, StaffID and ActivityID form PK)
ActivityDate - date activity was recorded
StaffID - FK from Staff table
ActivityID - FK from Activity table
ActivityHours - number of hours doing activity

And some sample data:

Staff
FF : Fred Flintstone

Team
1 : Red
2 : Blue

Activity
KISS : Kissing Wilma
DINO : Walking Dino

TeamStaff
1 : FF : 1/1/2009 : 31/1/2009 - Fred was in the Red team in January
2 : FF : 1/2/2009 : 28/2/2009 - Fred was in the Blue team in February

StaffActivity
7/1/2009 : FF : KISS : 4 - 4 hours of kissing Wilma on 7 Jan (Red team)
22/1/2009 : FF : DINO : 2 - 2 hours of walking Dino on 22 Jan (Red team)
7/2/2009 : FF : KISS : 3 - 3 hours of kissing Wilma on 7 Feb (Blue team)
22/2/2009 : FF : DINO : 9 - 9 hours of walking Dino on 22 Feb (Blue team)

I now want to do a query to show the total number of hours on each activity between given dates (eg. 1 Jan and 15 Feb), taking into account the dates Fred was in each team.

I add all 5 tables to the Query view then add the following fields:

ActivityDate (from StaffActivity) : Where : >=#1/1/2009# And <=#15/2/2009# : Don't Show
TeamName (from Team) : Group By : Show
ActivityName (from Activity) : Group By : Show
ActivityHours (from StaffActivity) : Sum : Show

I expected to see this:

Red : Kissing Wilma : 4 - from 7 Jan
Red : Walking Dino : 2 - from 22 Jan
Blue : Kissing Wilma : 3 - from 7 Feb

(with no row for 22 Feb as it is outside the Where range for ActivityDate).

I actually saw this:

Blue : Walking Dino : 2
Blue : Kissing Wilma : 7
Red : Walking Dino : 2
Blue : Kissing Wilma : 7

I am confused .

I realise I haven't linked the ActivityDate to the StartDate and EndDate of the TeamStaff table, but am not sure how.

I hope this all makes sense. Thanks for any feedback.

 

[khawar]

Try following Sql

SELECT Team.TeamName, 
Activity.ActivityName, 
StaffActivity.ActivityHours, 
StaffActivity.ActivityDate
FROM Team INNER JOIN
 ((Staff INNER JOIN (Activity INNER JOIN
 StaffActivity ON Activity.ActivityId = StaffActivity.ActivityId) ON 
Staff.StaffId = StaffActivity.StaffId) 
INNER JOIN TeamStaff ON Staff.StaffId = TeamStaff.StaffId) 
ON Team.TeamId = TeamStaff.TeamId
WHERE 
(([activitydate] Between [startdate] And [enddate]) 
AND 
((StaffActivity.ActivityDate) Between #1/1/2009# And #2/15/2009#));

 

[seestevecode]

Thanks for your response.

Copying and pasting your SQL directly didn't work, but I managed to follow your reasoning and got it to work.

What I hadn't done in the Query Window was to add a SECOND instance of ActivityDate (not shown) and add the criteria for StartDate and EndDate in there.

Now I should (hopefully) be able to extrapolate this into my more complex database.

Thanks again.