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.

 

[tlbroadbent]

SQL Author, Joe Celko, said, "The single biggest challenge to learning SQL programming is unlearning procedural programming."

When writing queries you have to learn to think differently - think in terms of sets of data and relations. It is also helpful to learn the language. In procedural languages, one would never write "Else... If... Then..." Likewise in SQL, you need to put clauses and predicates in the correct order such as putting the Group By clause after the Where clause.

Additionally, the language has rules for use, which can be found in the documentation. For example, Group By is an aggregate clause. If Group By is used, then all columns in the Select list must be included in the clause or in an aggregate function. Thus you cannot Group By EmployeeNumber only if the Select list includes other columns.

I suggest that you remove the date from the select list. Remove the DISTINCT keyword. Add the other selected columns to the Group By clause. And then add a having clause as noted below. This should return 1 line per employee with 13 weeks "perfect attendance."

SELECT [tblAttendence].[EmployeeNumber],
[tblEmployees].[NameFirst],
[tblEmployees].[NameInitial],
[tblEmployees].[NameLast]
Count([tblAttendence].[EmployeeNumber]) As RecCnt
FROM tblEmployees LEFT JOIN tblAttendence
ON [tblEmployees].[EmployeeNumber]=[tblAttendence].[EmployeeNumber]
WHERE ([tblEmployees].[EmpStatusType]="Active" And [tblEmployees].[PayType]="per Hour&quot
And ([tblAttendence].[DateWeekStarting]
Between Nz([Enter Start Date],Date()-7)
And Nz([Enter End Date],DateAdd("ww",-14,Date())))
And (([tblAttendence].[WorkDay1Reason] Is Null Or [tblAttendence].[WorkDay1Reason]="HolD-A&quot
And ([tblAttendence].[WorkDay2Reason] Is Null Or [tblAttendence].[WorkDay2Reason]="HolD-A&quot
And ([tblAttendence].[WorkDay3Reason] Is Null Or [tblAttendence].[WorkDay3Reason]="HolD-A&quot
And ([tblAttendence].[WorkDay4Reason] Is Null Or [tblAttendence].[WorkDay4Reason]="HolD-A&quot
And ([tblAttendence].[WorkDay5Reason] Is Null Or [tblAttendence].[WorkDay5Reason]="HolD-A&quot)
Group By
[tblAttendence].[EmployeeNumber],
[tblEmployees].[NameFirst],
[tblEmployees].[NameInitial],
[tblEmployees].[NameLast],
HAVING Count([tblAttendence].[EmployeeNumber])=13
ORDER BY [tblAttendence].[EmployeeNumber]; 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]

OK, did everything you said to do and I get a syntax err, missing operator on the Count, right after the SELECT. FYI, I do agree and understand exactly what you said. I am slated to go off to the week VB class, but that won't be for a month or two. Thanks for the help that you have given me, it is deeply appreciated.

 

[tlbroadbent]

Sorry, I left off a comma at the end of the line before the COUNT. The placement is highlighted in RED below.

[tblEmployees].[NameLast],
Count([tblAttendence].[EmployeeNumber]) As RecCnt

By the way, VB class really won't be much help in learning SQL. But it will be immensely helpful when using Access. Good luck. And you are welcome. 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]

OK, I foundout what was wrong, we were missing a comma after the firstNameLast and we had an extra comma are the last NameLast, which I removed. Then I ran it, the first window came and I entered the starting date, the second window came up and I entered the ending date. Then a message came up that zI have never seen before, it said that the query was either typed wrong or to complex to process. Thanks again.

 

[tlbroadbent]

Access sure is irritating! I've had that particular error occur more frequently with Access 2000 than any other version. Your query isn't very complex compared to many I've worked with.

One thing that might help is to define the parameters as DateTime fields. That eliminated the "too complex" error for me in a couple of instances. No guarantees this time.

Add the following at the beginning of the query, before the Select statement.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime; 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 found the problem and now it is working. Now all I have to do in somehow incorporate the final parameter into this query and it is done. I saved it to last because it is a mind bender in the world of logic. It is VacD-A, vacation day(s) and week(s), and JurD-A, Jury Duty day(s) and week(s). If JurD-A or VacD-A is one or two days, no problem, just treat it like HolD-Aand count it as worked, but if eirther one is 3 or 4 days or a week, that week should be ignored completely as if it does not exist. For example, normally we would look at weeks 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13. But if a VacD-A was used in week 10 we would then, for that employee only, look at weeks 1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, 14. Is Acces even capabile of doing something like this or is it beyond the ability of the quey? I think I would like your option before starting the last leg of my journey. Thank you very much for all of the help that you have provided me, I wouldn't have been able to get this far without it.

 

[tlbroadbent]

You should be able to do most if not all of what you need in queries. I recommend that you create simple queries that answer one question at a time. Create the queries that answer these questions and save them. You should be able get all of the information from tblAttendence. Then when you are satisfied that each of the individual queries answers the question properly, you can join the queries with other queries or tables as needed.

Just to get you started, here is one possibility for selecting an employee with more than two days of jury duty or vacation in a week. The highlighted portion adds the number of days with JurD-A or VacD-A and select records that have more than 2 such days. It includes the ABS function to get the absolute value because True = -1 in Access.

Select EmployeeNumber, DateWeekStarting
From tblAttendence
Where (DateWeekStarting Between
Nz([Enter Start Date],Date()-7) And
Nz([Enter End Date],DateAdd("ww",-14,Date())))
And Abs(([tblAttendence].[WorkDay1Reason] = "JurD-A" Or [tblAttendence].[WorkDay1Reason]="VacD-A&quot
+ ([tblAttendence].[WorkDay2Reason] = "JurD-A" Or [tblAttendence].[WorkDay2Reason]="VacD-A&quot
+ ([tblAttendence].[WorkDay3Reason] = "JurD-A" Or [tblAttendence].[WorkDay3Reason]="VacD-A&quot
+ ([tblAttendence].[WorkDay4Reason] = "JurD-A" Or [tblAttendence].[WorkDay4Reason]="VacD-A&quot
+ ([tblAttendence].[WorkDay5Reason] = "JurD-A" Or [tblAttendence].[WorkDay5Reason]="VacD-A&quot) > 2 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 install the above code. Then I altered my dumbies in my test database table, so that one employee has a week vacation and one employee has two days off in one week and one day off in another. I then ran the query and got no one. So I thought lets work on the one/two day off senario first. Here is wwhat I have so far:
SELECT Att.EmployeeNumber, Emp.NameFirst,
Emp.NameInitial,
Emp.NameLast,
COUNT(Att.EmployeeNumber) AS RecCnt
FROM tblEmployees AS Emp LEFT JOIN tblAttendence AS Att
ON Emp.EmployeeNumber=Att.EmployeeNumber
WHERE (Emp.EmpStatusType="Active"
And Emp.PayType="per Hour&quot
And (Att.DateWeekStarting
Between Nz([Enter Start Date],Date()-7)
And Nz([Enter End Date],DateAdd("ww",-14,Date())))
And Abs((Att.WorkDay1Reason="JurD-A"
Or Att.WorkDAy1Reason="VacD-A&quot
+ (Att.WorkDay2Reason="JurD-A"
Or Att.WorkDay2Reason="VacD-A&quot
+ (Att.WorkDay3Reason="JurD-A"
Or Att.WorkDay3Reason="VacD-A&quot
+ (Att.WorkDay4Reason="JurD-A"
Or Att.WorkDay4Reason="VacD-A&quot
+ (Att.WorkDay5Reason="JurD-A"
Or Att.WorkDay5Reason="VacD-A&quot)>2
And ((Att.WorkDay1Reason Is Null
Or Att.WorkDay1Reason="HolD-A&quot
And (Att.WorkDay2Reason Is Null
Or Att.WorkDay2Reason="HolD-A&quot
And (Att.WorkDay3Reason Is Null
Or Att.WorkDay3Reason="HolD-A&quot
And (Att.WorkDay4Reason Is Null
Or Att.WorkDay4Reason="HolD-A&quot
And (Att.WorkDay5Reason Is Null
Or Att.WorkDay5Reason="HolD-A&quot)
GROUP BY Att.EmployeeNumber,
Emp.NameFirst,
Emp.NameInitial,
Emp.NameLast
HAVING COUNT(Att.EmployeeNumber)=13
ORDER BY Att.EmployeeNumber;

Now to get the occurances where VacD-A or JurD-A is one or two per week, should I just add an AND after each HolD-A? Or would another ABS be the answer? Thanks.

 

[quest4]

In a vain effort to finish this, I tried removing the ABS statement and added a Or "JurD-A" and a Or "VacD-A" to the end of each "HolD-A" and I got both employees which, means I got the oone/two day employee as well as the week employee. I expected that, so I put the ABS statement back in and ran the query and got no one again. Am I maybe missing an IIf or something to tell the ABS what to do if true or false? Thanks, again.

 

[tlbroadbent]

You have created a query with conflicting criteria. You cannot choose the vacation/jury duty and hope to get 13 records that the Having clause requires. How about trying a simpler query as I suggested? Take things one step at a time. 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 what you are saying, but I'm not sure of just how or where to split it up. I understand that we can us a union query to being them together in the end. Should I copy the current query and remove the Abs staement? If so no problem, but what do I do to the new copy? Or are you thinking of something else altogether? Thanks.

 

[quest4]

Just a thought, is there any way to look at the data by the row? If have looked everywhere in help and only see it available in forms and reports. Do you have any little tricks? Thanks.

 

[tlbroadbent]

Example: Suppose you save the query I created as qVacJuryRecs. You can then JOIN it to tblEmployees.

Select
[qVacJuryRecs].[EmployeeNumber],
[tblEmployees].[NameFirst],
[tblEmployees].[NameInitial],
[tblEmployees].[NameLast],
[qVacJuryRecs].[DateWeekStarting]
From tblEmployees Inner Join qVacJuryRecs
On tblEmployee.EmployeeNumber=qVacJuryRecs.EmployeeNumber

You can open a table and view the data in the table. Is that what you mean by "view by the row?" 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 created qryVacJuryRecs, with one change, I added a ";" to the end of the last line. If I try an run it, I get a "Circular Reference caused by 'qryVacJuryRecs'." I also get that message if I try to go from the SQL view to the Display view.
As far as BY ROW goes, in my other world you could use that command in a query to control your data in whole rows, like all five of the WorkDayxReason's aa once, which means you can set your parameters/conditions for each row of data to be counted. I think that here that is a pipe dream.
I still can not visualize what it is that you see or want me to see. And I defenately don't understand what this query is supposed to do. thanks for all of the help, you would make a good teacher.

 

[tlbroadbent]

I provided two queries, repeated here for ease of viewing. Save the first one as qryVacJuryRecs and save the second with another name. It cannot be named the same or it overwrites the first and creates the circular reference.

First query: qryVacJuryRecs

NOTE: This query finds the employees who had more than two jury or vacation days in a week. It returns the employee number and the week of the leave.

Select EmployeeNumber, DateWeekStarting
From tblAttendence
Where (DateWeekStarting Between
Nz([Enter Start Date],Date()-7) And
Nz([Enter End Date],DateAdd("ww",-14,Date())))
And Abs(([tblAttendence].[WorkDay1Reason] = "JurD-A" Or [tblAttendence].[WorkDay1Reason]="VacD-A&quot
+ ([tblAttendence].[WorkDay2Reason] = "JurD-A" Or [tblAttendence].[WorkDay2Reason]="VacD-A&quot
+ ([tblAttendence].[WorkDay3Reason] = "JurD-A" Or [tblAttendence].[WorkDay3Reason]="VacD-A&quot
+ ([tblAttendence].[WorkDay4Reason] = "JurD-A" Or [tblAttendence].[WorkDay4Reason]="VacD-A&quot
+ ([tblAttendence].[WorkDay5Reason] = "JurD-A" Or [tblAttendence].[WorkDay5Reason]="VacD-A&quot) > 2

2nd query: qryVacJuryRecsPerEmployee (or whatever you name it)

NOTE: This query adds employee name to the query above. That's all it does. It is provided to show how to JOIN a table to a query.

Select
[qryVacJuryRecs].[EmployeeNumber],
[tblEmployees].[NameFirst],
[tblEmployees].[NameInitial],
[tblEmployees].[NameLast],
[qryVacJuryRecs].[DateWeekStarting]
From tblEmployees Inner Join qryVacJuryRecs
On tblEmployee.EmployeeNumber=qryVacJuryRecs.EmployeeNumber

I still don't understand your meaning of By Row. In Access, you are dealing with the whole row of data. Results sets are returned in rows.

As far as my ability to teach, it is obvious that I'm not teaching much in this thread. I feel that is because you've stepped into something that is way over your head and you should start with a basic book or a class in Access or SQL. The concepts I've tried to explain are, for the most part, basic SQL and Access concepts.

One cannot start learning SQL by choosing a complex query and trying to determine what it does. One must start with basic concepts and build upon them. Every time I've introduced a basic concept, you've added to and complicated it. That's makes learning very difficult. 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 created the qryVacJuryRecs, with only two modifacations. The first is I used an alias and the second is I put a ; that the end , and yes I did try it without, also. I ran it and it came back with the one week of vacation from my table, then it got I tried to save the changes and it when I clicked the YES botton to save the query I got a message box that says "Record is Deleted." I get the same thing if I try and click the closed botton. This happens with and without the ; at the end. Here is the code and I have read it and reread it:
SELECT EmployeeNumber,
DateWeekStarting
FROM tblAttendence AS Att
WHERE (Att.DateWeekStarting
Between Nz([Enter Start Date],Date()-7)
And Nz([Enter End Date],DateAdd("ww",-14,Date())))
And Abs((Att.WorkDay1Reason="JurD-A"
Or Att.WorkDAy1Reason="VacD-A&quot
+(Att.WorkDay2Reason="JurD-A"
Or Att.WorkDay2Reason="VacD-A&quot
+(Att.WorkDay3Reason="JurD-A"
Or Att.WorkDay3Reason="VacD-A&quot
+(Att.WorkDay4Reason="JurD-A"
Or Att.WorkDay4Reason="VacD-A&quot
+(Att.WorkDay5Reason="JurD-A"
Or Att.WorkDay5Reason="VacD-A&quot)>2;
It works, but how do I save it? Thanks.

 

[quest4]

FYI, Terry. I have taken the three lavels of MS's Access training courses from MS approvered instructors, but instructors are not teachers. I have seen only two instructors who were teachers in my entire life. Also, I have learned more in work on this and a couple of other questions than the entire four days with those instructors. You, are a teacher, at least as I see it, not an instructor. I always beleave that you can learn the most by going into projects that are over your head, and grant this one definately is over my head, but I am learning alot about Access and what you can possibly do with it. And I have build several other Access databases, some fairly complicated, but this has puzzled me on more than one occasion, hence the title. This is just FYI.

 

[tlbroadbent]

I apologize if I've judged wrongly. May I suggest that you start another thread for additional questions. Others may be more inclined to reply to a new thread than to this long thread. I'm also certain that someone else will be better able to help.

I don't know why you can't save the query. Perhaps you could save it with another name. Perhaps you could fix the problem by doing a Repair/Compact of the database. These are things I would try.

Good luck. 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]

Bingo, it is working now. That is a first for me. Thanks you again for all of your help, I will take your advise and do that on Moday. One last thing thow, now that I have the employees and the weeks that are to be exempted, should I make a similar query for the two and less entries? I copy almost of the current 2 queries. And shouldn't I also remove the JurD-A and VacD-A statements or should I trash that completely? And with these answers I will end this question.

 

[quest4]

Just to let you know, the query works great at finding weeks off, but it can't find any 3 and 4 days off occurances. I will send this out Monday to the formum. Thank you again for all of your help, I have really learned alot.