Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem Query Need HELP???? 2

Status
Not open for further replies.
quest4

quest4

Technical User
Aug 27, 2001
735
US
This is a select query to find a list of employees who have 13 weeks of perfect attendence. Simple right. Holidays are counted as regular days and one or two vacation days or jury duty days are also counted as regular days. Simple right. Here is where it gets tuff, three , four or five vaction days or jury duty days, the week is to be ignored or not counted. I found that Or Exists with an Abs count can get me close. Here is section of my code that I am using:

Or Exists(SELECT Att.EmployeeNumber,
Att.DateWeekStarting
FROM tblAttendence
WHERE ((Abs((Nz(Att.WorkDay1Reason="JurD-A")
Or Nz(Att.WorkDay1Reason="VacD-A"))
+(Nz(Att.WorkDay2Reason="JurD-A")
Or Nz(Att.WorkDay2Reason="VacD-A"))
+(Nz(Att.WorkDay3Reason="JurD-A")
Or Nz(Att.WorkDay3Reason="VacD-A"))
+(Nz(Att.WorkDay4Reason="JurD-A")
Or Nz(Att.WorkDay4Reason="VacD-A"))
+(Nz(Att.WorkDay5Reason="JurD-A")
Or Nz(Att.WorkDay5Reason="VacD-A")))=3)
Or (Abs((Nz(Att.WorkDay1Reason="JurD-A")
Or Nz(Att.WorkDay1Reason="VacD-A"))
+(Nz(Att.WorkDay2Reason="JurD-A")
Or Nz(Att.WorkDay2Reason="VacD-A"))
+(Nz(Att.WorkDay3Reason="JurD-A")
Or Nz(Att.WorkDay3Reason="VacD-A"))
+(Nz(Att.WorkDay4Reason="JurD-A")
Or Nz(Att.WorkDay4Reason="VacD-A"))
+(Nz(Att.WorkDay5Reason="JurD-A")
Or Nz(Att.WorkDay5Reason="VacD-A")))=4)
Or (Abs((Nz(Att.WorkDay1Reason="JurD-A")
Or Nz(Att.WorkDay1Reason="VacD-A"))
+(Nz(Att.WorkDay2Reason="JurD-A")
Or Nz(Att.WorkDay2Reason="VacD-A"))
+(Nz(Att.WorkDay3Reason="JurD-A")
Or Nz(Att.WorkDay3Reason="VacD-A"))
+(Nz(Att.WorkDay4Reason="JurD-A")
Or Nz(Att.WorkDay4Reason="VacD-A"))
+(Nz(Att.WorkDay5Reason="JurD-A")
Or Nz(Att.WorkDay5Reason="VacD-A")))=5)))

I would like to add this condition to the end, but I'm not sure how. This is what I would like:

Then GetNumWeeks()+1 AND RecCnt+0

Also I would need to know how to reset the GetNumWeeks() to the default when the next EmployeeNumber comes up. I have tryed several different things, but I keep getting syntax errors or not a valid SQL command. Thank you in advance for anyone who assists me.
 
Sort by date Sort by votes
If you are using Access, I would create a function to calculate the days. This way, if you create a new record you can set the default value and you can always loop and update the information.
 
Upvote 0 Downvote
Thanks for the response. This is the bottoom 1/3 of a query. And yes I am using Access 2000 to run this monster. I am not really to good at VB, yet, and I was lucky enough to get this here. I just want to make sure that the Where iif stuff would work here. Thanks again for the help.
 
Upvote 0 Downvote
You will need to add one more condition to your query, to test for the new record. And yes, the IIf statement should work.
 
Upvote 0 Downvote
The test woould be for a new EmployeeNumber, correct? Would that go after the query above? Thank you for your help.
 
Upvote 0 Downvote
There is a .NewRecord command, that would be before you run the dates test, because if the NewRecord is true you don't need to continue just set the default value.
 
Upvote 0 Downvote
Ok, I guess I will try and figure this out and see what I can come up with. Thank you for all of your help.
 
Upvote 0 Downvote
I take it back WHERE IIf or even IIf gives you a syntax error. I guess it is back to square one Thanks anyway.
 
Upvote 0 Downvote
Where are you using this, and what syntax did you use? I tried using IIf in the criteria of a query, and it worked fine.
 
Upvote 0 Downvote
Forgive me but it is late here and I have been playing with this for 3 or 4 days now and it is so close to done that I could scream right now. Here is what I added:
Or Exists(SELECT Att.EmployeeNumber,
Att.DateWeekStarting
FROM tblAttendence
WHERE IIf((Abs((Nz(Att.WorkDay1Reason="JurD-A")
Or Nz(Att.WorkDay1Reason="VacD-A"))
+(Nz(Att.WorkDay2Reason="JurD-A")
Or Nz(Att.WorkDay2Reason="VacD-A"))
+(Nz(Att.WorkDay3Reason="JurD-A")
Or Nz(Att.WorkDay3Reason="VacD-A"))
+(Nz(Att.WorkDay4Reason="JurD-A")
Or Nz(Att.WorkDay4Reason="VacD-A"))
+(Nz(Att.WorkDay5Reason="JurD-A")
Or Nz(Att.WorkDay5Reason="VacD-A")))=3)
Or (Abs((Nz(Att.WorkDay1Reason="JurD-A")
Or Nz(Att.WorkDay1Reason="VacD-A"))
+(Nz(Att.WorkDay2Reason="JurD-A")
Or Nz(Att.WorkDay2Reason="VacD-A"))
+(Nz(Att.WorkDay3Reason="JurD-A")
Or Nz(Att.WorkDay3Reason="VacD-A"))
+(Nz(Att.WorkDay4Reason="JurD-A")
Or Nz(Att.WorkDay4Reason="VacD-A"))
+(Nz(Att.WorkDay5Reason="JurD-A")
Or Nz(Att.WorkDay5Reason="VacD-A")))=4)
Or (Abs((Nz(Att.WorkDay1Reason="JurD-A")
Or Nz(Att.WorkDay1Reason="VacD-A"))
+(Nz(Att.WorkDay2Reason="JurD-A")
Or Nz(Att.WorkDay2Reason="VacD-A"))
+(Nz(Att.WorkDay3Reason="JurD-A")
Or Nz(Att.WorkDay3Reason="VacD-A"))
+(Nz(Att.WorkDay4Reason="JurD-A")
Or Nz(Att.WorkDay4Reason="VacD-A"))
+(Nz(Att.WorkDay5Reason="JurD-A")
Or Nz(Att.WorkDay5Reason="VacD-A")))=5)), GetNumWeeks()+1 And RecCnt+0)

I thought I would try just this part without a reset to default on GetNumWeeks(). Basically all I was trying to do is increase GetNumWeeks() by 1 and not add anything to the RecCnt. Thanks very much for your response.
 
Upvote 0 Downvote
Is this actually part of a query or is it part of some code?

I think you are misinterpreting the function of a WHERE clause. All it does is define which records to select. Perhaps if these records exist you want to update some other value? Sorry, I just can't quite follow your logic enough to know what to tell you.
 
Upvote 0 Downvote
Yes, this is the bottom 1/3 of a query. Basically I am using Abs to count the days of a week which have vacation days or jury duty days. If there are 3, 4 or 5 day in the week with vacation or jury duty, I don't want it counted in RecCnt and I want to increase GetNumWeeks() which is a variable with a 13 week default. In other words I want to count 14 weeks for this employee, only. This will give the illusion of being ignored or by-passed. I hope this will simplify what I am attempting to do. Thanks you again for your help.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

serino
  • Locked
  • Question
Help with a Query. 1
Replies
13
Views
974
strongm
strongm
BugZap13
  • Locked
  • Question
Need help creating a date from two fields to be used in where clause
Replies
5
Views
679
mikrom
mikrom
C
  • Locked
  • Question
Query expression negative to a 0
Replies
6
Views
482
dhookom
dhookom
C
  • Locked
  • Question
Run-time error '3075': Syntax error (missing operator) in query expression 'Birthday = and Birthmonth ='.
Replies
3
Views
419
OzFoxy
OzFoxy
breezett93
  • Question
Query takes 30 seconds - 1 minute to process. Trying to rework it to take 5 or less
Replies
8
Views
469
breezett93
breezett93

Part and Inventory Search

Sponsor

Back
Top