Tuff Little Query That Won't Work Right!! 2

[quest4]

This is the "easy" query, qryEmployeeAttendenceBonuses, and here is the code that I started with:

SELECT [tblAttendence].[EmployeeNumber]
FROM tblAttendence
WHERE ((([tblAttendence].[WorkDay1Reason]) Is Null) And (([tblAttendence].[WorkDay2Reason]) Is Null) And (([tblAttendence].[WorkDay3Reason]) Is Null) And (([tblAttendence].[WorkDay4Reason]) Is Null) And (([tblAttendence].[WorkDay5Reason]) Is Null) And (([tblAttendence].[WorkDay6Reason]) Is Null) And ((Month([tblAttendence]![DateWeekStarting]))=Month(Now()-4)))
ORDER BY [tblAttendence].[EmployeeNumber], [tblAttendence].[DateWeekStarting] DESC;

I have one of the dumby employees with a missed day in the middle and both still come up. I need to add some extras to the query but I'm not sure how. The extra parameters are, if any of the WorkDayxReason entries is = to "HolD-A", accept that day(s) as a regular day worked. The next parameter is, is any WorkDayxReason is = to "VacD-A" or "JurD-A" to those days, usally a full week, should be ignored as thought it never exited in the calender. I actuality, I would really like this to age back 13 weeks instead of 4 months, but I haven't figured out how to work in weeks yet. On e last parameter, if "Bonus" appears the Emplyee is not elegable for a bonus that week. If anyone can help me out with the error of my query, it will be great;y appreaciated. Thanks amillion.

 

[quest4]

In a vain effort to generate a list of employees in the above request I tried making some changes which got me even less than than what I have started with, so this is what I tried and failed with:

SELECT tblAttendence.EmployeeNumber, tblEmployees.NameFirst, tblEmployees.NameInitial, tblEmployees.NameLast
FROM tblEmployees, tblAttendence
GROUP BY tblAttendence.EmployeeNumber, tblEmployees.NameFirst, tblEmployees.NameInitial, tblEmployees.NameLast, tblEmployees.EmpStatusType, tblEmployees.PayType, tblAttendence.DateWeekStarting
HAVING (((tblEmployees.EmpStatusType)="Active&quot AND ((tblEmployees.PayType)="per Hour&quot AND ((tblAttendence.DateWeekStarting) Between Date() And Month(Date()-4)) AND ((Count(tblAttendence.WorkDay1Reason)) Is Null) AND ((Count(tblAttendence.WorkDay2Reason)) Is Null) AND ((Count(tblAttendence.WorkDay3Reason)) Is Null) AND ((Count(tblAttendence.WorkDay4Reason)) Is Null) AND ((Count(tblAttendence.WorkDay5Reason)) Is Null) AND ((Sum(tblAttendence.DateWeekStarting))=0))
ORDER BY tblAttendence.EmployeeNumber, tblAttendence.DateWeekStarting DESC;

This is a separate query, I did keep the above one also. Any help would be greatly appreciated. Thanks.

 

[tempclerk]

I'm not sure what other problems you're having, but
Month(Now()-4)
Month(Date()-4)
both give you the Month of four days ago. To get four months less, you need to put your -4 outside the brackets; However, I don't think even that will work for you during January, February, March or April.

 

[tlbroadbent]

You can use Dateadd to find the date 13 weeks ago. Just add -13 and use a datepart of weeks (ww). NOTE: I use aliases to shorten the query and aid readability.

1st query:

SELECT a.[EmployeeNumber]
FROM tblAttendence As a
WHERE a.[WorkDay1Reason] Is Null
AND a.[WorkDay2Reason] Is Null
AND a.[WorkDay3Reason] Is Null
AND a.[WorkDay4Reason] Is Null
AND a.[WorkDay5Reason] Is Null
AND a.[WorkDay6Reason] Is Null
AND a.[DateWeekStarting] >= Dateadd("ww",-13,date())
ORDER BY a.[EmployeeNumber], a.[DateWeekStarting] DESC;

2nd query: Omitting the Group By, Sum and Count which seem unnecessary.

SELECT
a.EmployeeNumber,
e.NameFirst,
e.NameInitial,
a.NameLast
FROM tblEmployees e
INNER JOIN tblAttendence a
ON e.EmployeeNumber=a.EmployeeNumber
WHERE a.EmpStatusType="Active"
AND a.PayType="per Hour"
AND a.DateWeekStarting Between dateadd("ww",-13,date()) AND Date()
AND a.WorkDay1Reason Is Null
AND a.WorkDay2Reason Is Null
AND a.WorkDay3Reason Is Null
AND a.WorkDay4Reason Is Null
AND a.WorkDay5Reason Is Null
ORDER BY
a.EmployeeNumber,
a.DateWeekStarting DESC; Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[quest4]

OK, I decided to go with query 2, and I added in tw of the three variable parameters and this is the way that it looks now:
SELECT tblAttendence.EmployeeNumber, tblEmployees.NameFirst, tblEmployees.NameInitial, tblEmployees.NameLast
FROM tblEmployees INNER JOIN tblAttendence ON tblEmployees.EmployeeNumber = tblAttendence.EmployeeNumber
WHERE tblEmployees.EmpStatusType="Active" AND
tblEmployees.PayType = "per Hour" AND
tblAttendence.DateWeekStarting Between DateAdd("ww", -13, Date() AND Date() AND
tblAttendence.WorkDay1Reason Is Null) OR (tblAttendence.WorkDay1 = &quot;HolD-A&quot; AND tbl.Attendence.WorkDay1<> &quot;Bonus&quot; AND
tblAttendence.WorkDay2Reason Is Null OR tblAttendence.WorkDay2 = &quot;HolD-A&quot; AND tbl.Attendence.WorkDay2 <> &quot;Bonus&quot; AND
tblAttendence.WorkDay3Reason Is Null OR tblAttendence.WorkDay3 = &quot;HolD-A&quot; AND tbl.Attendence.WorkDay3<> &quot;Bonus&quot; AND
tblAttendence.WorkDay4Reason Is Null OR tblAttendence.WorkDay4 = &quot;HolD-A&quot; AND tbl.Attendence.WorkDay4<> &quot;Bonus&quot; AND
tblAttendence.WorkDay5Reason Is Null OR tblAttendence.WorkDay5 = &quot;HolD-A&quot; AND tbl.Attendence.WorkDay5<> &quot;Bonus&quot; AND
ORDER BY tblAttendence.EmployeeNumber, tblAttendence.DateWeekStarting DESC;

When I go and save it I get a syntax error (missing operator)in query expresion, then it lists out the WHERE section of the query. I am vaguely starting to see where you are going, but I need a little help on the syntax. Thanks a million, Terry. You are really good at this, how long did it take you to get this down so pat? Thanks a again.

 

[quest4]

OK, it helps to get out the typing misakes. It runs, but it now asks for WorkDay1-5 parameter values and with the way I set up the test table, I should get one EmployeeNumber with his full name, I get 10 of one employee and nine of the other. Here id the corrected query:
SELECT [tblAttendence].[EmployeeNumber],
[tblEmployees].[NameFirst],
[tblEmployees].[NameInitial],
[tblEmployees].[NameLast]
FROM tblEmployees INNER JOIN tblAttendence ON
[tblEmployees].[EmployeeNumber]=[tblAttendence].[EmployeeNumber]
WHERE [tblEmployees].[EmpStatusType]=&quot;Active&quot; And
[tblEmployees].[PayType]=&quot;per Hour&quot; And
[tblAttendence].[DateWeekStarting] Between DateAdd(&quot;ww&quot;,-13,Date()) And Date() And
[tblAttendence].[WorkDay1Reason] Is Null Or
[tblAttendence].[WorkDay1]=&quot;HolD-A&quot; And
tbl.Attendence.WorkDay1<>&quot;Bonus&quot; And
[tblAttendence].[WorkDay2Reason] Is Null Or
[tblAttendence].[WorkDay2]=&quot;HolD-A&quot; And
tbl.Attendence.WorkDay2<>&quot;Bonus&quot; And
[tblAttendence].[WorkDay3Reason] Is Null Or
[tblAttendence].[WorkDay3]=&quot;HolD-A&quot; And
tbl.Attendence.WorkDay3<>&quot;Bonus&quot; And
[tblAttendence].[WorkDay4Reason] Is Null Or
[tblAttendence].[WorkDay4]=&quot;HolD-A&quot; And
tbl.Attendence.WorkDay4<>&quot;Bonus&quot; And
[tblAttendence].[WorkDay5Reason] Is Null Or
[tblAttendence].[WorkDay5]=&quot;HolD-A&quot; And
tbl.Attendence.WorkDay5<>&quot;Bonus&quot;
ORDER BY [tblAttendence].[EmployeeNumber],
[tblAttendence].[DateWeekStarting] DESC;

I am sure the there is one missing ingredient in this recipe, I just can't see it. Thanks for all of the help.

 

[tlbroadbent]

I think you need some parentheses around parts of the query to group the criteria properly. Additionally, part of your criteria may be unnecessary.

tblAttendence.WorkDay1=&quot;HolD-A&quot; And tbl.Attendence.WorkDay1<>&quot;Bonus&quot;

If WorkDay1=&quot;Hold-A&quot;, it is <> &quot;Bonus&quot; so the 2nd test may not be needed. However, this depends on how you really want to structure the criteria. How should the criteria be grouped?

Which of the following is correct?

([tblAttendence].[WorkDay1Reason] Is Null Or
[tblAttendence].[WorkDay1]=&quot;HolD-A&quot And
tbl.Attendence.WorkDay1<>&quot;Bonus&quot;

OR

([tblAttendence].[WorkDay1Reason] Is Null Or
([tblAttendence].[WorkDay1]=&quot;HolD-A&quot; And
tbl.Attendence.WorkDay1<>&quot;Bonus&quot)

If the 2nd grouping is correct thaen the test <> &quot;Bonus&quot; isn't needed. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums. NOTE: Reference to the FAQ is not directed at any individual.

 

[quest4]

The first one is correct. Basically, it is like look at a five day calinder. If you are 13 straight weeks without any time off you get a bonus. Only one bonus per 13 weeks. Holidays can count a a regular work day. Does this make it easier to understand what I am tring to do? This query will produce a list of those employees, by EmployeeNumber and full name, who have worked 13 straight weeks without missing any time. Thanks again, you are good, I will let yuou know how it works out.

 

[quest4]

I made the changes in WHERE that you mentioned, I still get the parameter value popup boxes for the WorkDay1-5 and now no one is in the final list. I double checked and there should be one name in the list, one employee has a missed day, and one is terminated.

 

[tempclerk]

check your spelling: tbl.Attendance?

 

[quest4]

You were right about the tblAttendence spelling. Now I don't get any popup windows, I get a list of both active employees the first, which is the only one that should be on the list is listed eight time, and the second employee is listed seven times. Can't really figure out what is really happening, because I would think the first employee should have 13 entries and then maybe I would understand it, but not eight. Thanks for the help, one step closer I how.

 

[quest4]

I figured out how to get rid of the the duplicate entries, by using SELECT DISTINCT instead of SELECT. I still get both employees though, when I should only get one. The second employee was sick eight weeks ago and missed the day and was marked in the WorkDay3Reason box. So that box was not Null, yet the query is missing it. Any help? Any suggestions? Here is the query now:

SELECT DISTINCT [tblAttendence].[EmployeeNumber], [tblEmployees].[NameFirst],
[tblEmployees].[NameInitial],
[tblEmployees].[NameLast]
FROM tblEmployees INNER JOIN tblAttendence ON [tblEmployees].[EmployeeNumber]=[tblAttendence].[EmployeeNumber]
WHERE [tblEmployees].[EmpStatusType]=&quot;Active&quot; AND
[tblEmployees].[PayType]=&quot;per Hour&quot; AND
[tblAttendence].[DateWeekStarting] BETWEEN
DateAdd(&quot;ww&quot;,-13,Date()) AND Date() AND
([tblAttendence].[WorkDay1Reason] Is Null OR [tblAttendence].[WorkDay1]=&quot;HolD-A&quot AND
[tblAttendence].[WorkDay1]<>&quot;Bonus&quot; AND
([tblAttendence].[WorkDay2Reason] Is Null OR [tblAttendence].[WorkDay2]=&quot;HolD-A&quot AND
[tblAttendence].[WorkDay2]<>&quot;Bonus&quot; AND
([tblAttendence].[WorkDay3Reason] Is Null OR [tblAttendence].[WorkDay3]=&quot;HolD-A&quot AND
[tblAttendence].[WorkDay3]<>&quot;Bonus&quot; AND
([tblAttendence].[WorkDay4Reason] Is Null OR [tblAttendence].[WorkDay4]=&quot;HolD-A&quot AND
[tblAttendence].[WorkDay4]<>&quot;Bonus&quot; AND
([tblAttendence].[WorkDay5Reason] Is Null OR [tblAttendence].[WorkDay5]=&quot;HolD-A&quot AND
[tblAttendence].[WorkDay5]<>&quot;Bonus&quot;
ORDER BY [tblAttendence].[EmployeeNumber];

 

[quest4]

I thought I found my error, using a wrong field, so I made the changes and my problenm is now worse:
SELECT DISTINCT [tblAttendence].[EmployeeNumber], [tblEmployees].[NameFirst],
[tblEmployees].[NameInitial],
[tblEmployees].[NameLast]
FROM tblEmployees INNER JOIN tblAttendence ON
[tblEmployees].[EmployeeNumber]=[tblAttendence].[EmployeeNumber]
WHERE [tblEmployees].[EmpStatusType]=&quot;Active&quot; And
[tblEmployees].[PayType]=&quot;per Hour&quot; And
[tblAttendence].[DateWeekStarting] Between
DateAdd(&quot;ww&quot;,-13,Date()) And Date() And
([tblAttendence].[WorkDay1Reason] Is Null Or [tblAttendence].[WorkDay1Reason]=&quot;HolD-A&quot And [tblAttendence].[WorkDay1Reason]<>&quot;Bonus&quot; And ([tblAttendence].[WorkDay2Reason] Is Null Or [tblAttendence].[WorkDay2Reason]=&quot;HolD-A&quot And [tblAttendence].[WorkDay2Reason]<>&quot;Bonus&quot; And ([tblAttendence].[WorkDay3Reason] Is Null Or [tblAttendence].[WorkDay3Reason]=&quot;HolD-A&quot And [tblAttendence].[WorkDay3Reason]<>&quot;Bonus&quot; And ([tblAttendence].[WorkDay4Reason] Is Null Or [tblAttendence].[WorkDay4Reason]=&quot;HolD-A&quot And [tblAttendence].[WorkDay4Reason]<>&quot;Bonus&quot; And ([tblAttendence].[WorkDay5Reason] Is Null Or [tblAttendence].[WorkDay5Reason]=&quot;HolD-A&quot And [tblAttendence].[WorkDay5Reason]<>&quot;Bonus&quot;
ORDER BY [tblAttendence].[EmployeeNumber];

This produces a list of no one, when it should have one employee. Any suggestions? Thanks.

 

[quest4]

The wierd gets wierder and to me this is getting to wierd. I changed thIs Null to =&quot;&quot; and of course it did not like that until I remove the fild after the OR and that now says ([tblAttendence].[WorkDayxReason]=&quot;&quot; OR &quot;HolD-A&quot. Guess what the dang thing liked it and ran and prduced a list with only one name on it, the bad news is, it was the wrong employee. If anyone has a suggestion yet me know. Thanks.

 

[quest4]

I have discovered something about Access queries, beer is very helpful. Last night I sat down and really went over this thing and decided to take one step backwards. I removed DISTINCT and add DateWeekStarting to the SELECT and the ORDER BY and a few other minor things and got this:

SELECT [tblAttendence].[EmployeeNumber],
[tblEmployees].[NameFirst],
[tblEmployees].[NameInitial],
[tblEmployees].[NameLast],
[tblAttendence].[DateWeekStarting]
FROM tblEmployees LEFT JOIN tblAttendence
ON [tblEmployees].[EmployeeNumber]=[tblAttendence].[EmployeeNumber]
WHERE ([tblEmployees].[EmpStatusType]=&quot;Active&quot;
And [tblEmployees].[PayType]=&quot;per Hour&quot
And ([tblAttendence].[DateWeekStarting]
Between Nz([Enter Start Date],Date()-7)
And Nz([Enter End Date],DateAdd(&quot;ww&quot;,-13,Date()-7)))
And (([tblAttendence].[WorkDay1Reason]=&quot;&quot; Or &quot;HolD-A&quot
And ([tblAttendence].[WorkDay2Reason]=&quot;&quot; Or &quot;HolD-A&quot
And ([tblAttendence].[WorkDay3Reason]=&quot;&quot; Or &quot;HolD-A&quot
And ([tblAttendence].[WorkDay4Reason]=&quot;&quot; Or &quot;HolD-A&quot
And ([tblAttendence].[WorkDay5Reason]=&quot;&quot; Or &quot;HolD-A&quot)
ORDER BY [tblAttendence].[EmployeeNumber], [tblAttendence].[DateWeekStarting] DESC;

Now I figured this would give me a list of employees, where the employee had nothing in the WorkDayxReason field or had &quot;HolD-A&quot; in the WorkDayxReason field. Thing should give me a list of 13 rows, one for each week, except if something else was in one of the WeekDayxReasons field, like &quot;Bonus&quot; or &quot;SicD-A&quot;, then that week should not be included on the list. Well, I ran the query, for the second employee I enter a &quot;SicD-A&quot; in the WeekDay3Reason of the week 7/16/2001, and I thought that that week would NOT be on the list, but guess what, it was. I thought that by putting a paren aroung the WorkDayxReason statements, all would have to be true, in other word &quot; &quot; or &quot;HolD-A&quot; in order to print. I thought taht once I got it to print corectly, then we could add a parameter or control to print only the name(s) of employees who have &quot; &quot; or &quot;HolD-A&quot; in all 13 week rows. First things first, anybody have any idaes why this query list all week rows regardless of the entry in the DayWorkxReason field? This is just a list of employees who did not miss any time for 13 weeks. Thanks a lot for any help that may come my way.

 

[quest4]

OK, we are not in UNIX any more and Access obviously can do things as well, but I added a [tblAttendence].[WorkDayxReason] to both sides of the OR and now it is producing exactly what I wanted. It makes no sense why, but it works. Now the tweny dollar question, how do I get this query to stop list the employees with less than 13 weeks perfect attendence, which will be 13 rows on the query table? I don't want to see the 8's and 10's only the 13's. Thanks for any help offered.

 

[quest4]

As an attempt to cure my above mentioned problem, I tried a second WHERE and DCOUNT, but I get a syntax error, missing operator, any ideas why? Here are the modifications:
SELECT DISTINCTROW [tblAttendence].[EmployeeNumber], [tblEmployees].[NameFirst],
[tblEmployees].[NameInitial],
[tblEmployees].[NameLast],
[tblAttendence].[DateWeekStarting]
FROM tblEmployees LEFT JOIN tblAttendence
ON [tblEmployees].[EmployeeNumber]=[tblAttendence].[EmployeeNumber]
WHERE ([tblEmployees].[EmpStatusType]=&quot;Active&quot; And [tblEmployees].[PayType]=&quot;per Hour&quot
And ([tblAttendence].[DateWeekStarting]
Between Nz([Enter Start Date],Date()-7)
And Nz([Enter End Date],DateAdd(&quot;ww&quot;,-13,Date()-7)))
And (([tblAttendence].[WorkDay1Reason] Is Null Or [tblAttendence].[WorkDay1Reason]=&quot;HolD-A&quot
And ([tblAttendence].[WorkDay2Reason] Is Null Or [tblAttendence].[WorkDay2Reason]=&quot;HolD-A&quot
And ([tblAttendence].[WorkDay3Reason] Is Null Or [tblAttendence].[WorkDay3Reason]=&quot;HolD-A&quot
And ([tblAttendence].[WorkDay4Reason] Is Null Or [tblAttendence].[WorkDay4Reason]=&quot;HolD-A&quot
And ([tblAttendence].[WorkDay5Reason] Is Null Or [tblAttendence].[WorkDay5Reason]=&quot;HolD-A&quot)
GROUP BY [tblAttendence].[EmployeeNumber]
WHERE DCount(&quot;[EmployNumber]&quot;, &quot;tblAttendence&quot=&quot;13&quot;
ORDER BY [tblAttendence].[EmployeeNumber], [tblAttendence].[DateWeekStarting] DESC;

Any help will be greatly appreciated, I have hit a stone wall.

 

[tlbroadbent]

Any WHERE clause must appear before the GROUP BY. Use the HAVING clause after the GROUP BY.

However, I don't believe the DCount function will provide the answer you want. It does a table query independent of the query in which it is embedded and as written will return a count of all the rows in the tblAttendence. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums. NOTE: Reference to the FAQ is not directed at any individual.

 

[quest4]

I understand. How then can I display only the Employees who have 13 listings and not the ones with 12 or less listings. The listings are in the diplay view and appear as a table. When I remove the DateWeekStarting, the DISTINCTROW should remove the duplications of the people who ahave 13 weeks of perfect attendence. Thanks for all of your help, you have been great.

 

[quest4]

I tried to replace theORDER BY statement with:
GROUP BY Att.EmployeeNumber
HAVING Count(Att.EmployeeNmber)=&quot;13&quot;;
When run I get a message box stating:&quot;You tried to execute a query that does not include the speciied expression 'NameFirst' as part of the aggregate function.&quot;
Why would I even get this? What does NameFirt even have to do with this? My eye teeth for a few good if's and then's and a couple of else's. I think I'm getting ready to trash is silly thick and take it to UNIX where it will be a piece of cake and I won't need to beg for help. Thanks if anyone answers or helps me with this thing.