2 Problems my 2003 file. and a query that won't group the records by month

[kizmissy]

my database was working fine until a new month was added in the attendance now each staff member has about 20 records when they should only have two. if i remove the second month it corrects its self but i can't do that permanently my problem lies within the all about hours query there should be two records per staff member as it should show all the details month (Oct and Nov) it worked fine when there was only one month or hours but now there are two instead of bringing up two records it brings up several. I realise my database is unconventional but I really have no idea how to do it any other way, I don't have any real understanding of SQL so its all pretty much been done through wizards.




also i have office 2010 but the office has 2003 so i saved it to 2002-2003 file format and it was working fine up until today when it has started to give and error message
Which Reads:


Your Microsoft Office Access database or project contains a missing or broken reference to the file 'ACEDAO.DLL' version 12.0

[June7]

This is what happens when a dataset is joined to multiple related tables that have multiple records for each record in the primary dataset.

The query is joining on StaffID. Each dependent query has up to two records that must join to each of the records in AttendanceT table. Because the jointypes are all INNER, records show only where the StaffID is in all the tables.

If you want to show all the various data summations in one query, need a dataset of all possible StaffID/Month/Year combinations, then join the dependent queries on that dataset. Use compound join on staffID/month/year. Include year so the db will be functional for multiple years.

[kizmissy]

sorry i'm pretty new to access, do you mean i need to make a new table of months and years and join me query to that?

if i am to make a new table what should my fields be because when i tried i did month a year separately as text but will that work? i would try as time/date but i cant get it to just let me input the month and year

[June7]

The StaffID/year/month dataset can be a query built from the Attendance table.

SELECT DISTINCT StaffID, Year([attendancedate]) & Format([attendancedate],"mm") AS YrMo FROM AttendanceT;

Also calculate the YrMo in each of the dependent queries.

The AllAboutHoursQ query would then be:
SELECT StaffYrMo.StaffID, StaffYrMo.YrMo, WorkedPaidHoursMonthlyQ.WorkedPaidHours, VoluntaryHoursMonthlyQ.[Voluntary Hours], MonthlyHoursQ.Hours AS TotalHours, [AttendanceNumbers04/12-04/13Q].Total AS TotalDays, PayQ.Pay
FROM [AttendanceNumbers04/12-04/13Q] RIGHT JOIN (PayQ RIGHT JOIN (WorkedPaidHoursMonthlyQ RIGHT JOIN (MonthlyHoursQ RIGHT JOIN (VoluntaryHoursMonthlyQ RIGHT JOIN StaffYrMo ON (VoluntaryHoursMonthlyQ.StaffID = StaffYrMo.StaffID) AND (VoluntaryHoursMonthlyQ.YrMo = StaffYrMo.YrMo)) ON (MonthlyHoursQ.StaffID = StaffYrMo.StaffID) AND (MonthlyHoursQ.YrMo = StaffYrMo.YrMo)) ON (WorkedPaidHoursMonthlyQ.StaffID = StaffYrMo.StaffID) AND (WorkedPaidHoursMonthlyQ.YrMo = StaffYrMo.YrMo)) ON (PayQ.StaffID = StaffYrMo.StaffID) AND (PayQ.YrMo = StaffYrMo.YrMo)) ON ([AttendanceNumbers04/12-04/13Q].StaffID = StaffYrMo.StaffID) AND ([AttendanceNumbers04/12-04/13Q].YrMo = StaffYrMo.YrMo);



When do you get the 'broken reference' error? I am not getting it.

[kizmissy]

when i calculate the YrMo on WorkedPaidMonthlyHourQ it messes up all the info how can i stop that from happening and which other Querys do i need to put it into?

[kizmissy]

oh and about the broken reference i looked the error up and deleted the cause

[June7]

So many queries of queries to finally get to the AllAboutHoursQ, can be difficult to follow.

Not finding query named WorkedPaidMonthlyHourQ. There is WorkedPaidHoursMonthlyQ.

I should have said 'calculate YrMo in the appropriate queries associated with AllAboutHoursQ'.
WeeklyHoursWithMonthQ
MonthlyHoursQ
AttendanceNumbers04/12-04/13Q

I just took a look at WorkedPaidHoursWeeklyWithMonthQ and I am surprised it even runs with the cross joins in there. Change VoluntaryHoursWeeklyQ to include the YrMo field then this field will be available to subsequent queries. You have a breakdown by week but again if you want to allow multiple years, need year component. I am not sure what you want this query to show but I don't think it is correct as is with joins only on StaffID.

[kizmissy]

The information i need is:
Days worked in a week, Days worked in a Month
Percent of Absent, Sick, Late, Present (Annual Leave and Unpaid Holiday to be added)
Paid Hours, Voluntary Hours, Total hours (Both Monthly and Weekly records)

If I was too delete my query's how would you collate this info?

[June7]

Should not be saving staff name in AttendanceT table, just save the StaffID and retrieve the name by joining tables in query.

Create a query that calculates Year, Wk, Mo, Minutes, Hours values and use this query a basis for subsequent queries.

AttendanceExpanded:
SELECT AttendanceT.*, Year([AttendanceDate]) As Yr, Format([AttendanceDate],"ww") AS Wk, Format([AttendanceDate],"mm") AS Mo, DateDiff("n",[TimeIn],[TimeOut]) As Minutes, Minutes/60 As Hrs
FROM AttendanceT;

WeeklyHoursQ:
SELECT StaffID, Sum(Hrs) AS SumOfHrs, Yr, Wk
FROM AttendanceExpanded
GROUP BY StaffID, Yr, Wk;

MonthlyHoursQ:
SELECT StaffID, Sum(Hrs) AS SumOfHrs, Yr, Mo
FROM AttendanceExpanded
GROUP BY StaffID, Yr, Mo;

etc.

[kizmissy]

Sorry I've taken so long to reply I've finally got round to doing what you advised but the number for the hours on the queries i added are off

StaffNew.zip

[June7]

Off how? The sums look right to me.

[kizmissy]

attendanceexpanded record 1 minutes should be 423 and hours should be 7.05
record 2 minutes should be 357 and hours should be 5.95

they are all slightly off which means the other queries ore off is this something i've done?

[June7]

Record 1 times are 9:10 and 16:15. The difference is 7 hrs 5 minutes = 425 minutes = 7.08 hours.
Record 2 times are 10:20 and 16:15. The difference is 5 hrs 55 minutes = 355 minutes = 5.92 hours.