Connecting periods from different tables - database design problem

[sockswithsandals]

This is really driving me crazy! I am not experienced Access user first of all.

I'm trying to build a database where would be project allocations per person per period (month) (could be many projects) and persons would also report their actual hours per project per week (so several reports per person per project) but I don't know how to connect these periods from different tables. Now when I run a query I'll get everything multiple times as hour report period 4 shows all allocations etc. How this should be done...? I would really appreciate any help...

[June7]

Instead of repeating the UserID, ProjectID, FY, Period in tblRepHours, save the SPID pk from tblStaffProj as fk. Otherwise, do a join between those two tables on 4 fields:

SELECT tblProj.ProjID, tblProj.ProjName, tblRepHours.USERID AS tblRepHours_USERID, tblRepHours.FY, tblRepHours.RHPeriod, tblRepHours.RepHours, tblStaff.USERID AS tblStaff_USERID, tblStaff.StaffF, tblStaff.StaffL, tblStaff.StaffT, tblStaffProj.APct, tblStaffProj.APeriod
FROM tblStaff RIGHT JOIN ((tblProj RIGHT JOIN tblStaffProj ON tblProj.ProjID = tblStaffProj.[ProjID]) RIGHT JOIN tblRepHours ON (tblStaffProj.APeriod = tblRepHours.RHPeriod) AND (tblStaffProj.FY = tblRepHours.FY) AND (tblStaffProj.ProjID = tblRepHours.ProjID) AND (tblStaffProj.UserID = tblRepHours.USERID)) ON tblStaff.USERID = tblStaffProj.UserID;

[sockswithsandals]

Thanks a lot! That got me a lot further. One thing though, now the query shows every weekly report as a separate line for project so it has one project 4 times per every period. Would it be possible to have reported hours totals per project per period in the results...?

Like if I have

SELECT tblRepHours.userid, tblRepHours.projid, tblRepHours.fy, tblRepHours.rhperiod, Sum(tblRepHours.rephours) AS SumOfrephours
FROM tblRepHours
GROUP BY tblRepHours.userid, tblRepHours.projid, tblRepHours.fy, tblRepHours.rhperiod;

then it shows totals from tblRepHours as I'd like it to show but how can I combine that with the allocations? Or should I make a new table which has reported hours totals in it...?

BR,

"A lot to learn from Access"

[June7]

Certainly is possible.

Suggest you build a report that will summarize data in group header/footer sections. This will allow display of the detail records as well as summary calculations. Use the query I provided (add the APct field to the query) as the RecordSource for report. Here it is again, slightly modified:

SELECT tblRepHours.*, tblStaffProj.APct, tblProj.ProjName, tblProj.ProjEvery, tblStaff.StaffF, tblStaff.StaffL, tblStaff.StaffT, tblStaff.StaffR
FROM tblStaff RIGHT JOIN ((tblProj RIGHT JOIN tblStaffProj ON tblProj.ProjID = tblStaffProj.[ProjID]) RIGHT JOIN tblRepHours ON (tblStaffProj.APeriod = tblRepHours.RHPeriod) AND (tblStaffProj.FY = tblRepHours.FY) AND (tblStaffProj.ProjID = tblRepHours.ProjID) AND (tblStaffProj.UserID = tblRepHours.USERID)) ON tblStaff.USERID = tblStaffProj.UserID;

Access Help has guidelines on building reports using Grouping & Sorting with aggregate calcs. Also, review:
http://office.microsoft.com/en-us/ac...010341571.aspx
http://office.microsoft.com/en-us/ac...aspx?section=5

[sockswithsandals]

Thanks a lot again! I think this can be marked as solved.