Access Query Help (1 Viewer)

[rplum80]

Hey guys!,

I need a little help and was hoping someone could assist. We are tracking our employees attendance at work, and I built an access database that pulls from our time keeping system however on some instances its pulling the wrong data because a different record is present besides there "Regular Hours". In the picture attached as an example basically what I need to do is for May 4th If Regular Hours is the PAYCODENAME then it needs to disregard any other coding. In this case Unpaid ESA - Family Emergency should not show when I run this query since Regular Hours is present for the same date. I have tried a few IIf statement but I can't seem to get it to work.




Thanks!

 

[Nightowl4933]

Hi rplum80,

I'm definitely not an experienced helper on this forum, but I will try to help, if I can.

I think you'll need to provide a bit more information about what's behind Expr1002 before I can try, though.

Pete

 

[sneuberg]

An expression like:

HasRegularHours:  DCount("*", "[table or query with PAYCODENAME]", "[PERSONFULLNAME] = '" & [PERSONFULLNAME] & "' And [APPLYDATE] = #" & [APPLYDATE] & "# And [PAYCODENAME] = 'Regular Hours'") > 0 And [PAYCODENAME] = "Unpaid ESA - Family Emergency"

would be true when the record's PAYCODENAME was "Unpaid ESA - Family Emergency" and there were one or more records with the same PERSONFULLNAME and APPLYDATE with PAYCODENAME equal to "Regular Hours" . So if an expression like this were given a criteria of False those records would be exclude from the query. Of course you would need to put the correct table name in this and verify the spelling of these field names.

But this assumes that that if a person has two records for the same date one with "Regular Hours" and the other with "Unpaid ESA - Family Emergency" that the one with "Unpaid ESA - Family Emergency" is bogus. How do you know that its' not the other way around?

 

[rplum80]

Hi rplum80,

I'm definitely not an experienced helper on this forum, but I will try to help, if I can.

I think you'll need to provide a bit more information about what's behind Expr1002 before I can try, though.

Pete

Here is the query.

SELECT dbo_VP_TOTALS.PERSONNUM, dbo_VP_TOTALS.PERSONFULLNAME, dbo_VP_TOTALS.PAYCODENAME, dbo_VP_TOTALS.APPLYDATE, Format([APPLYDATE],"dddd") AS [Week Day], dbo_VP_EMPLOYEE.HOMELABORLEVELDSC5 AS Department, Left([GRPSCHEDNAME],3) AS Shift, dbo_VP_EMPLOYEE.ACCRUALPRFLNAME AS [Employment Type], dbo_VP_EMPLOYEE.GRPSCHEDNAME, Sum([timeinseconds])/3600 AS HRS_WORKED, dbo_VP_TOTALS.PAYCODENAME

FROM dbo_VP_TOTALS INNER JOIN dbo_VP_EMPLOYEE ON dbo_VP_TOTALS.PERSONNUM = dbo_VP_EMPLOYEE.PERSONNUM

WHERE (((dbo_VP_EMPLOYEE.EMPLOYMENTSTATUS)="active") AND ((dbo_VP_TOTALS.PAYCODENAME) Like "Stat Unpaid Sick Pay" Or (dbo_VP_TOTALS.PAYCODENAME) Like "Unpaid Sick" Or (dbo_VP_TOTALS.PAYCODENAME) Like "Sick" Or (dbo_VP_TOTALS.PAYCODENAME) Like "Unapproved Absence" Or (dbo_VP_TOTALS.PAYCODENAME) Like "Unpaid ESA - Family Emergency" Or (dbo_VP_TOTALS.PAYCODENAME) Like "Approved*"))

GROUP BY dbo_VP_TOTALS.PERSONNUM, dbo_VP_TOTALS.PERSONFULLNAME, dbo_VP_TOTALS.APPLYDATE, dbo_VP_EMPLOYEE.HOMELABORLEVELDSC5, Left([GRPSCHEDNAME],3), dbo_VP_EMPLOYEE.ACCRUALPRFLNAME, dbo_VP_EMPLOYEE.GRPSCHEDNAME, dbo_VP_TOTALS.PAYCODENAME, dbo_VP_TOTALS.PAYCODENAME

HAVING (((dbo_VP_TOTALS.PERSONFULLNAME)=[Forms]![FrmMainMenu].[combonames]) AND ((dbo_VP_TOTALS.APPLYDATE) Between [Forms]![FrmMainMenu]![fromdate] And [Forms]![FrmMainMenu]![todate]) AND ((Sum([timeinseconds])/3600)>0 And (Sum([timeinseconds])/3600)<=8));

Expr1002 is PAYCODENAME = Like "Stat Unpaid Sick Pay" Or Like "Unpaid Sick" Or Like "Sick" Or Like "Unapproved Absence" Or Like "Unpaid ESA - Family Emergency" Or Like "Approved*"


Thanks

 

[sneuberg]

That query doesn't seem to match the picture in your first post in that it doesn't have Expr1002 in it.

Where is this?

Expr1002 is PAYCODENAME = Like "Stat Unpaid Sick Pay" Or Like "Unpaid Sick" Or Like "Sick" Or Like "Unapproved Absence" Or Like "Unpaid ESA - Family Emergency" Or Like "Approved*

 

[rplum80]

An expression like:

HasRegularHours:  DCount("*", "[table or query with PAYCODENAME]", "[PERSONFULLNAME] = '" & [PERSONFULLNAME] & "' And [APPLYDATE] = #" & [APPLYDATE] & "# And [PAYCODENAME] = 'Regular Hours'") > 0 And [PAYCODENAME] = "Unpaid ESA - Family Emergency"

would be true when the record's PAYCODENAME was "Unpaid ESA - Family Emergency" and there were one or more records with the same PERSONFULLNAME and APPLYDATE with PAYCODENAME equal to "Regular Hours" . So if an expression like this were given a criteria of False those records would be exclude from the query. Of course you would need to put the correct table name in this and verify the spelling of these field names.

But this assumes that that if a person has two records for the same date one with "Regular Hours" and the other with "Unpaid ESA - Family Emergency" that the one with "Unpaid ESA - Family Emergency" is bogus. How do you know that its' not the other way around?

When I run the query after entering your statement it now flags the ESA Days with a -1. Regular Hours is 0.

 

[sneuberg]

When I run the query after entering your statement it now flags the ESA Days with a -1. Regular Hours is 0.

It should be -1 which is the value for True for the ESA days but those records that are flag as True (-1) should also be meeting the condition of the DCount. Anyway if the records that are flagged with -1 are the ones you don't want in the result then just make the criteria for this expression False.

But somehow I feel this is a hack and would hide some underlying problem. Why are these records showing up in the first place?