Solved Can someone please explain what is going on here? (Totals Query)

[raziel3]

I am running a Totals Query on a table but for some reason the sum field is doubling the values for some of the employees.

 

[plog]

You have 2 records in EmpStatus with EID=32. So, every record in TimeSheet with EID=32 (40 records) hits every EmpStatus record with EID=32 (2 records). Then it sums them all up and gets 60 total STDHours.


Your question makes it seem like you expect only 1 record from EmpStatus to be used. Which one from EmpStatus do you expect? And explain that logically. As in give us a logical rule you can apply so that the correct EmpStatus record is used for every EID.

 

[raziel3]

Oh, I see. I just wanted the total hours for an employee between the given dates. I will remove the EmpStatus table and join it in another query.

Thanks.

 

[oleronesoftwares]

Your query uses a left join, that might be the reason there are duplicate values

A left join returns all the tables from a specified “left” column and the corresponding rows that match a particular condition in the “right” column.

Try changing to inner join

 

[Pat Hartman]

Your table structure is wrong also. Foreign keys are data field to PK. You have data field to data field. The FK should point to ESID NOT EID. You need to change this before you move on. If EID is the name of the status, it should have a unique index to prevent duplicates. Sometimes for small lookup tables like these, we use a text field as the PK and do NOT include an autonumber. In that case the EID would be the PK and then the relationship would be correct.

Also, define relationships using the relationship window and enforce RI in all cases. Some people think they are smarter than the database engine so they can't be bothered with RI. They would be wrong. RI is your friend. It will keep you from creating orphan records either by adding a "child" without a FK and also from deleting a "parent" that has "children" UNLESS you specify cascade delete.

 

[raziel3]

Your table structure is wrong also. Foreign keys are data field to PK. You have data field to data field. The FK should point to ESID NOT EID. You need to change this before you move on. If EID is the name of the status, it should have a unique index to prevent duplicates. Sometimes for small lookup tables like these, we use a text field as the PK and do NOT include an autonumber. In that case the EID would be the PK and then the relationship would be correct.

Also, define relationships using the relationship window and enforce RI in all cases. Some people think they are smarter than the database engine so they can't be bothered with RI. They would be wrong. RI is your friend. It will keep you from creating orphan records either by adding a "child" without a FK and also from deleting a "parent" that has "children" UNLESS you specify cascade delete.

I have the Main Employee table that uses EID as the PK. EID is the FK for the TimeSheet and EmployeeStatus tables.

 

[Pat Hartman]

But EID is NOT the PK of the status table and that is what we are talking about. There will be multiple rows in status for any given EID value. That is why the join duplicates the data.

 

[raziel3]

 

[plog]

Here's a few questions I have about your structure:

1. How come an employee can have multiple statuses? EID=32 has 2 statuses, both of which have an Estatus of ACTIVE. So does he really have 2 current statuses or does the one with the latest EffDate take precedence and ESID=21 become an older one?

2. Should TimeSheet be connected to EmpInfo or EmpStatus? I mean, rates in one table and hours are in another, seems like those should be directly related.

3. In TimeSheet whats WDate? I mean you have TimeIN and TimeOut wich are datetimes, what does WDate tell you that could be different?

4. Are all the hours calculated off TimeIn and TimeOut? Theres no data in TimeIn and TimeOut so I can't verify. But if all those hour fields are simple calculations you shouldn't store them.

 

[raziel3]

I ALWAYS use RI.

 

[raziel3]

1. How come an employee can have multiple statuses? EID=32 has 2 statuses, both of which have an Estatus of ACTIVE. So does he really have 2 current statuses or does the one with the latest EffDate take precedence and ESID=21 become an older one?

I am using the Employee Status table to keep track of PayRates, in case they switch departments, their position have changed or they are no longer employed by the company. Since these things can change I am using the EffDate to track it. The most recent will take precedence in calculating salary. The Main employee table will have the fields like DOB, NIS(SSN) etc. things that will never change.

2. Should TimeSheet be connected to EmpInfo or EmpStatus? I mean, rates in one table and hours are in another, seems like those should be directly related.

Yes, this was my error. TimeSheet should have been connected to EmpInfo but I wanted to bring in the current rate based on the last WDate in the totals query. (I was just experimenting)

3. In TimeSheet whats WDate? I mean you have TimeIN and TimeOut wich are datetimes, what does WDate tell you that could be different?

WDate is the Date the employee worked. TimeIn and TimeOut is a funny thing. How it works in the company is that a supervisor verifies the Employee's TimeIn and TimeOut and then passes it to me. Yes I can run a calculation on the TimeIn and TimeOut data but it may have discrepancies the Supervisor is aware of but not me.

4. Are all the hours calculated off TimeIn and TimeOut? Theres no data in TimeIn and TimeOut so I can't verify. But if all those hour fields are simple calculations you shouldn't store them.

So for Weekly paid employees I just use the times that are approved by the supervisor and enter them in the hour field(s) to calculate their pay which is paid hourly. For Monthly paid employees we just want their TimeIn and TimeOut to see their attendance and punctuality. The field is more for reference rather than calculations.

 

[June7]

Build a query that returns the latest pay rate record for each employee, then join that query to timesheet data.

 

[raziel3]

Build a query that returns the latest pay rate record for each employee, then join that query to timesheet data.

Yes, @bastanu already guided me on that in this post

Solved - Return a specific field from a recordset query

I think using saved totals queries like I showed you would be faster than VBA. I only used one dMax in the qryWklyPayroll and even that could probably be replaced by a TOP 1 subquery. You would need to experiment with your real data of course to accurately judge the performance. But even with...

www.access-programmers.co.uk

 

[June7]

Then is this thread essentially the same issue?

 

[raziel3]

Then is this thread essentially the same issue?

No, I wanted to know why STDHours was totaling twice for some employees and it was explained in post #2 by @plog