Is There a Way to Convert Part of A Query to VB Code? 2

[quest4]

This is the bottonm 1/3 of a query and to get it to work right, I have been told that I must put this part in a module. Ok, but I done really knowe to much about VB at this time. Is there any way, preferablly easy, to convert an SQL query to VB? Here is the part of the query, which does not work in SQL:

Or Exists(SELECT Att.EmployeeNumber,
Att.DateWeekStarting
FROM tblAttendence
WHERE IIf((Abs((Nz(Att.WorkDay1Reason="JurD-A&quot
Or Nz(Att.WorkDay1Reason="VacD-A&quot)
+(Nz(Att.WorkDay2Reason="JurD-A&quot
Or Nz(Att.WorkDay2Reason="VacD-A&quot)
+(Nz(Att.WorkDay3Reason="JurD-A&quot
Or Nz(Att.WorkDay3Reason="VacD-A&quot)
+(Nz(Att.WorkDay4Reason="JurD-A&quot
Or Nz(Att.WorkDay4Reason="VacD-A&quot)
+(Nz(Att.WorkDay5Reason="JurD-A&quot
Or Nz(Att.WorkDay5Reason="VacD-A&quot))=3)
Or (Abs((Nz(Att.WorkDay1Reason="JurD-A&quot
Or Nz(Att.WorkDay1Reason="VacD-A&quot)
+(Nz(Att.WorkDay2Reason="JurD-A&quot
Or Nz(Att.WorkDay2Reason="VacD-A&quot)
+(Nz(Att.WorkDay3Reason="JurD-A&quot
Or Nz(Att.WorkDay3Reason="VacD-A&quot)
+(Nz(Att.WorkDay4Reason="JurD-A&quot
Or Nz(Att.WorkDay4Reason="VacD-A&quot)
+(Nz(Att.WorkDay5Reason="JurD-A&quot
Or Nz(Att.WorkDay5Reason="VacD-A&quot))=4)
Or (Abs((Nz(Att.WorkDay1Reason="JurD-A&quot
Or Nz(Att.WorkDay1Reason="VacD-A&quot)
+(Nz(Att.WorkDay2Reason="JurD-A&quot
Or Nz(Att.WorkDay2Reason="VacD-A&quot)
+(Nz(Att.WorkDay3Reason="JurD-A&quot
Or Nz(Att.WorkDay3Reason="VacD-A&quot)
+(Nz(Att.WorkDay4Reason="JurD-A&quot
Or Nz(Att.WorkDay4Reason="VacD-A&quot)
+(Nz(Att.WorkDay5Reason="JurD-A&quot
Or Nz(Att.WorkDay5Reason="VacD-A&quot))=5)),
GetNumWeeks() + 1 And RecCnt +0)

Basically iI am saying that if any one of the WHERE conponents if TRUE then add one to the variable GetNumWeeks() and don't add anything to RecCnt, the record count. I am not even wooried about resetting GetNumWeeks() to the default at this time. I would be eternally greatful to anyone you could help me get through this mess. Thanks in advance.

 

[turtlemaster]

You first need to creat a module with a funtion in it to return the value to your query. Modules are put into memory simply by having the database open and all variables and funtions are accessible from any object as long as your database is open. Create your function so that you can pass it the parameters you need and then put your logic in your funtion to return the value to your query.

Where clause in query

WHERE valueA = myFunction(parameterA, parameterB)

Function in module

Function myFunction(parameter as (variabletype), ...)
logic
logic
logic (you can use you paramenters here)
myfunction = value
end function

Presto your query will be based upon the value in a function. i have used this method before that storing values in global varibales in modules and basing query on functions that return those values. You can do really neat stuff that way!!

 

[quest4]

Currently I am trying to redo the whole thing in VB. I just don't think SQL is capable of what I want to do. Which is increase a duration of time and then reset it back to the default. It is a roundabout way of making the query ignore weeks, which is what I want it to do when one of those conditions is true. This is turning into the DB from hell right now, first I had to learn this and now VB, too. Oh well, Thank you very much for your response, I may have to come back to it yet, even thow I don't quite understand all of it, I do understand most of it.

 

[turtlemaster]

A yes -- the db from hell. i have written my fair share of those (usually resulting from a cheap employer and a lack of resources). Remember though that VBA uses the same dll as VB and most everything that you can do in VB you can do in a code module in access (but most of the work is done for you!!!) switching to VB may add much time to your development where as with Access you never have to worry about connections, data access forms or simply to medium reporting needs. Just food for thought.

 

[quest4]

Well I really am in the module section and this isn't for the boss it is those dang accounts. I still fairly new at this, but I am aging quickly because of it. Thanks for the advise and help.

 

[tempclerk]

ACTUALLY, SQL is QUITE capable of doing what you want it to do, just not the way you seem determined to do it. After a little research on the forum and a quarter-hour's tinkering this afternoon, with a dozen lines of SQL I got all but the final step of your process figured out - I just don't remember what final result you're looking for.

Step 1: Use the criteria you've already figured out to define each week for each employee as Bonus, NoBonus, or NonCounting

Step 2: Select from above only weeks that are not designated NonCounting.

Step 3: Select from above the most recent 13 weeks per Employee

Step 4: If none of the weeks in step 3 are NoBonus, the employee gets Bonus.

I would probably use 3 or 4 separate queries, but a determined expert could probably even nest them into 1.

 

[BoxHead]

Quest4,

This is the 12th thread you've posted in various forums with the same question. I'm on the verge of saying "No Bonuses for anyone!"

Please, just one time, list your tables and your fields and explain what you need from the very beginning without any code. It honestly seems that there may be a basic flaw in the approach and all of the code trouble-shooting in the world can't fix that.

John

 

[quest4]

BoxHead, I have no problem in doing that. Here is the table layout:
EmployeeNumber DateWeekStarting WorkDay1 WorkDay1Reason WorkDay2 WorkDay2Reason WorkDay3 WorkDay3Reason WorkDay4 WorkDay4Reason WorkDay5 WorkDay5Reason
I started with aquery, but have been told that to finish this the way I need it to work is to use DAO/VB. Simply put I am tring to set a start date and automatically set the end date with a variable for the time period and that works. I am trying to. in that period, which is 13 weeks, find a list of employees who have 13 weeks of perfect attendence. This means that they should have all "" or Null and we will count all holidays, HolD-A, as a workded day. That was the easy part. This is where SQL starts to lose it. We also nee to look at vacation days, VacD-A and jury days, JurD-A, in which we look at his way; if there are 1 or 2 vacation/jurty days in a week, we count them as regular worked days, like holidays. But if we have 3, 4, or 5 vacation/jury days we want to add 1 to GetNumWeeks(), the variable for the time period, making it 14 weeks instead of 13 weeks, and add 0 to the RecCnt, which is the count of perfect weeks. This will in essense ignore the vacation/jury week and look at an extra week in place of it. Then we must reset the GetNumWeeks() back to the default of 13. I did not want to use DAO Because I am firly new at this, but I am really learning from it. This is my "last" query/report for this Access2000 database and when it is done, it is testing time. Thank you for your assistance, I really appreciate any help I can get, and yes, I am going to get more training in the future, as soon as the economy gets a little better.

 

[BoxHead]

Quest4,

Thank you. That made it much clearer.

I started by building a query that would calculate the number of days each week that an employee showed up for work, was on Jury Duty or on Vacation. It also identified the weeks where three or more days were Vacation or Jury days (I assumed an employee who took two vacation days and two jury days in one week would not have that week qualify for perfect attendance by showing up for just one day of work).

I built the query in the QBE grid because it's easier to check my progress. I wanted to change everything to zero and one values so we could add up an actual score.

In the QBE, we have the following columns (most are aliases):

EmployeeID,    'criteria [Enter Employee ID]
DateWeekStarting, 
D1: IIf(nz([workDay1Reason])="",1,0) 'value is 1 if at work
D2: IIf(nz([workDay2Reason])="",1,0) 'value is 0 if off
D3: IIf(nz([workDay3Reason])="",1,0)
D4: IIf(nz([workDay4Reason])="",1,0)
D5: IIf(nz([workDay5Reason])="",1,0)
Sc1: [d1]+[d2]+[D3]+[D4]+[D5]         'Total days at work
J1: IIf(nz([workDay1Reason])="JurD",1,0) 'val = 1 if on
J2: IIf(nz([workDay2Reason])="JurD",1,0) 'jury duty 
J3: IIf(nz([workDay3Reason])="JurD",1,0)
J4: IIf(nz([workDay4Reason])="JurD",1,0)
J5: IIf(nz([workDay5Reason])="JurD",1,0) 
Sc2: [J1]+[J2]+[J3]+[J4]+[J5]         'total jury days
V1: IIf(nz([workDay1Reason])="VacD",1,0)
V2: IIf(nz([workDay2Reason])="VacD",1,0) 'val = 1 if vac
V3: IIf(nz([workDay3Reason])="VacD",1,0)
V4: IIf(nz([workDay4Reason])="VacD",1,0)
V5: IIf(nz([workDay5Reason])="VacD",1,0)
Sc3: [V1]+[V2]+[V3]+[V4]+[V5]          'total Vac days
JV: [Sc2]+[Sc3]                 'total combined jury & vac
AdjSc: IIf([JV]<3,[SC1]+[jv],&quot;X&quot;)  'Adjusted score

The AdjSc looks at the number of Jury & Vacation Days, if it's less than three, it adds those days to SC1 (actual days worked). If Vacation & Jury Days = 3 or more, the Adjusted Score = "X".

Next, I built a simple query based on the first query. Four columns: EmployeeID, DateWeekStarting, AdjSc, & Count.

In the criteria for AdjSc, we use <> &quot;X&quot;. This eliminates all weeks where Jury and Vacation days were 3+.

Count: IIf(AdjSc = 5, 1, 0)

This gives you a value to sum. If the sum of count = 13, then the employee has had 13 consecutive weeks of perfect attendance.

The query is sorted DESC by date and limited to the Top 13 records.

Quest, I hope this is helpful.

John

 

[BoxHead]

Quest4,

It occurred to me to post the SQL behind the queries since all of your questions have been posed in SQL.

The first Query (qryAttendanceScore):

SELECT tblWorkdays.EmployeeNumber, tblWorkdays.DateWeekStarting,
IIf(nz([workDay1Reason])=&quot;&quot;,1,0) AS D1,
IIf(nz([workDay2Reason])=&quot;&quot;,1,0) AS D2,
IIf(nz([workDay3Reason])=&quot;&quot;,1,0) AS D3,
IIf(nz([workDay4Reason])=&quot;&quot;,1,0) AS D4,
IIf(nz([workDay5Reason])=&quot;&quot;,1,0) AS D5,
[d1]+[d2]+[D3]+[D4]+[D5] AS Sc1,
IIf(nz([workDay1Reason])=&quot;JurD&quot;,1,0) AS J1,
IIf(nz([workDay2Reason])=&quot;JurD&quot;,1,0) AS J2,
IIf(nz([workDay3Reason])=&quot;JurD&quot;,1,0) AS J3,
IIf(nz([workDay4Reason])=&quot;JurD&quot;,1,0) AS J4,
IIf(nz([workDay5Reason])=&quot;JurD&quot;,1,0) AS J5,
[J1]+[J2]+[J3]+[J4]+[J5] AS Sc2,
IIf(nz([workDay1Reason])=&quot;VacD&quot;,1,0) AS V1,
IIf(nz([workDay2Reason])=&quot;VacD&quot;,1,0) AS V2,
IIf(nz([workDay3Reason])=&quot;VacD&quot;,1,0) AS V3,
IIf(nz([workDay4Reason])=&quot;VacD&quot;,1,0) AS V4,
IIf(nz([workDay5Reason])=&quot;VacD&quot;,1,0) AS V5,
[V1]+[V2]+[V3]+[V4]+[V5] AS Sc3, [Sc2]+[Sc3] AS JV,
IIf([JV]<3,[SC1]+[jv],&quot;X&quot AS AdjSc
FROM tblWorkdays
WHERE (((tblWorkdays.EmployeeNumber)=[Enter Employee ID]))
ORDER BY tblWorkdays.DateWeekStarting DESC;


the second Query (qryAttendanceScore Query):


SELECT TOP 13 qryAttendanceScore.EmployeeNumber, qryAttendanceScore.DateWeekStarting, qryAttendanceScore.AdjSc, IIf([AdjSc]=5,&quot;1&quot;,&quot;0&quot AS Count
FROM qryAttendanceScore
WHERE (((qryAttendanceScore.AdjSc)<>&quot;X&quot);


John

 

[quest4]

This is great, I can't thank you enough. It runs, but returns no one, but that is OK for now. I know you didn't see the beginning of the query, but there was some criteria there which neds to be added in and sesnse this is eventually going to be a big program I thought I would ask you where to put this to make it run effeciently. Here is the code:
WHERE (Emp.EmpStatusType=&quot;Active&quot;
And Emp.PayType=&quot;per Hour&quot
And (Att.DateWeekStarting Between Nz([Enter Start Date], DateAdd(&quot;ww&quot;,-1-GetNumWeeks(), Date()))
And Dateadd(&quot;ww&quot;, GetNumWeeks(),
Nz([Enter Start Date],
DateAdd(&quot;ww&quot;,-1-GetNumWeeks(),
Date())))

This is the module that works with the variable:
Function GetNumWeeks()
GetNumWeeks = 13
End Function

There is is also a left join:
FROM tblEmployees AS Emp
LEFT JOIN tblAttendence AS Att
ON Emp.EmployeeNumber=Att.EmployeeNumber


This is to output to a report which has the Emp.EmployeeNumber, Emp.NameFirst, Emp.NameInitial, Emp.NameLast, and the count. My only real concern is the use of top13. In the occurance of a 3-5 day vacation/jury duty week, the week is to be ignored. I thought about using the GetNumWeeks() and adding one to it, which in reality would count 14 weeks, but would find 13 perfect weeks in the count and therefore would be added to the list. This was my idea, or insanity, which ever it may be. I thought I would go over this with you before doing anything and screwing up something. Thanks you again for all of the help you have giv me.

 

[BoxHead]

Quest, I've got something else to work on this morning. I'll try to hash out the List of Employees at lunch.

In the meantime, I used the Top 13 limit in the second query because the first query identified weeks with 3-5 vac/jury days with an &quot;X&quot; value in the week's AdjSc.

The second query has its criteria set to:

WHERE (((qryAttendanceScore.AdjSc)<>&quot;X&quot;));

so it only returns the top 13 weeks where there were not 3-5 vac/jury days. E.g.: If someone spent two weeks on a jury and one week on vacation, the query actually shows 13 of the last 16 weeks.

HTH

John

 

[quest4]

Ok, that gives me a little better understanding of the goings on of the new queries. Remember, codes like &quot;SicD-N&quot;, which is out sick, and other codes are to remove the employee from the final list. The only good codes are:
&quot; &quot; or Null
HolD-A
VacD-A -> Only if 1 or 2 days
JurD-A -> Only if 1 or 2 days
There is about six or seven other codes which are there to oliminate the employee from receiving the 13 week bonus. Thanks again for all of the help you have given me I own you one big time.

PS Should the top of the the first query read something like this:
IIf(nz([workDay1Reason])=&quot;&quot; OR &quot;HolD-A&quot;,1,0) AS D1,
Just a thought.

 

[quest4]

I figured out what I did wrong, you can't use a&quot;*&quot; without a LIKE. It now list out 26 listing for my test employee and the DESC is not even being paid any attention to. I added the OR &quot;HolD-A&quot; to the &quot;&quot; and it seems to like that. Thanks again.

 

[quest4]

I got it working with the top 13. I added ORDER BY qryEmployeeAttendance1.DateWeekStarting DESC to the end of the second query. I also tried to add a AND EmployeeNumber to the ORDER BY but it kind of messed things up also. This is just FYI, thanks again.

 

[BoxHead]

Hi Quest,

Great to hear you got it working.

I know you had struggled with it a long time.

Congrats,

John

 

[quest4]

It is working, but not the way I need it to work. It must create a list of employees who have 13 weeks of perfct attendance. I was hoping you would tell me the most effectient place to put the criteria I posted eariler. I am going to start experimenting with this and see if I can figure it out. Thanks you again for all of the help.

 

[quest4]

Got things somewhat working the way I want, but I need a little help here. I add this to the bottom of query 1:
FROM tblEmployees AS Emp LEFT JOIN tblAttendance AS Att
ON Emp.EmployeeNumber=Att.EmployeeNumber
WHERE (Emp.EmpStatusType=&quot;Active&quot; And Emp.PayType=&quot;per Hour&quot
And (Att.DateWeekStarting Between Nz([Enter Start Date],DateAdd(&quot;ww&quot;,-1-GetNumWeeks(),Date()))
And Dateadd(&quot;ww&quot;,GetNumWeeks(),Nz([Enter Start Date],
DateAdd(&quot;ww&quot;,-1-GetNumWeeks(),Date()))))
ORDER BY Att.EmployeeNumber;
Now this some what produces what I am looking for at this point. But in query to, I tried everything to make the [Count] =13 instead of what is there, and I keep getting invalid aguements and the like. Also I have had some problems adding NameFirst, NameInitial, and NameLast form tblEmployees to the query 2 table. Thank you again for all the help that you have given me, this thing is just a couple of lines from done when this is corrected.

 

[quest4]

Made some changes and got closer yet, but now I am into errors. Here is the new and improved query2:
SELECT EAB1.EmployeeNumber,
Emp.NameFirst,
Emp.NameInitial,
Emp.NameLast,
EAB1.DateWeekStarting,
EAB1.AdjSc,
IIf(EAB1.AdjSc=5, 1, 0) AS [Count]
FROM qryEmployeeAttendanceBonuses1 AS EAB1 LEFT JOIN
tblEmployees AS Emp ON Emp.EmployeeNumber=EAB1.EmployeeNumber
WHERE (((EAB1.AdjSc)<>&quot;X&quot)
HAVING Count(EAB1.AdjSc)=13
ORDER BY EAB1.EmployeeNumber;
The messages is: &quot;You tried to execute a query That does not includethe specific expression 'EmployeeNumber' as part of an aggregate function.&quot;
I have an idea that maybe I am counting the wrong thing? Thanks again.

 

[quest4]

Got it figured out, I think. Almost home, but this is the part that killed it last time. I aam lost to even start this, so here goes nothing, if JV=>3 then GetNumWeeks()+1.
Reset GetNumWeeks() to default with when new EmployeeNumber.
What this in essense will do is allow en employee with a JV=>3, in his 13 week period, to ignore that week and look at an extra week in its' place, in otherword, 14 weeks will realy be examined instead of the usual 13 weeks. Then when the next employee comes up the period is reset to 13 weeks, which is the default for GetNumWeeks(). I hope I made this clear enough? When this is done, I AM DONE AND OUT OF HERE and into testing. Thank you again for all of your help, I hope you can show me the last ray of light.