Several Dumb Question from a Newbee 2

[quest4]

Well sense noone want ed to help me with my first question, I decided to start this on on own. First question is should I be in a Sub, Function or Other? Next how does this thing know which table the data is in? I thought I would try a loop then send it to a second loop, did I do this right? Here is my attempt:
Private Procedure
Dim dbs As Database
Dim rst As Recordset
Dim EmploeeNumber As String
Dim DateWeekStarting As Date
Dim WorkDay1Reason As String
Dim WorkDay2Reason As String
Dim WorkDay3Reason As String
Dim WorkDay4Reason As String
Dim WorkDay5Reason As String
Dim strSQL
Set dbs = CurrentDb
Sub AttendenceDays()
DayCounter = 0
Do
If WorkDay1Reason = " " Or WorkDay1Reason = "HolD-A" Then
DayCounter = DayCounter + 1
If WorkDay2Reason = " " Or WorkDay2Reason = "HolD-A" Then
DayCounter = DayCounter + 1
If WorkDay3Reason = " " Or WorkDay3Reason = "HolD-A" Then
DayCounter = DayCounter + 1
If WorkDay4Reason = " " Or WorkDay4Reason = "HolD-A" Then
DayCounter = DayCounter + 1
If WorkDay5Reason = " " Or WorkDay5Reason = "HolD-A" Then
DayCounter = DayCounter + 1
Loop Until DayCount = 5
End Sub
Sub AttendenceWeeks()
WeekCounter = 0
myNum = 1
Do
EmployeeNumber
If DayCount = 5 Then
WeekCounter = WeekCounter + 1
Loop Until WeekCounter = 13
End Sub

How far off base am I? Thanks in advance if anyone answers this one.

 

[Mavors]

A few hints.

Your general ideas are good. one thing is that CurrentDB is a VBA item. Look up some information in the help on ADO database recordsets and connnections or if you are using an Access database strictly maybe DAO will be good for you. Both of these provide access to tables and queries within the database.

In your loop remember that you will need to advance your recordset so that you will keep processing new records and keep an eye out for that end of file(EOF) of true.

You have quite a bit of work ahead of you to learn, but I think you have a good start.
Mavors
Cincinnati, Ohio

 

[Mavors]

Quest4,

I just found your first post. So you are programming from within Access.


try setting your recordset = Database.openrecordset("tablename&quot

this will give you access to the table or query you want.

Also can use an SQL in place of that name.

the looping is the same...remember that movenext

Luck to you. Mavors
Cincinnati, Ohio

 

[quest4]

I will, eventually, have to use two different tables, tgblAttendence and tblEmployees. The SQL query I have can't do what I want it to do, so I am stuck trying to learn this now. Please let me now if this look alright so far? Thanks for the response.

 

[quest4]

Well that did work, It just errors out on exit of the db. Thanks anyway. Nothing worked for you line to srtGetSQL. Will anything work in this thing? Is this just another piece of BG junk? Thanks .

 

[scarfhead]

I was preparing a reply earlier and our webserver went down.
Hope this is still helpful:

What does each record represent? A single employee or an employee work-week?

It sounds like you want to select all records for a single employee, then count all of the days of unexcused absence ("HolD-A" or "" [empty string, not null]) for that employee. If the employee has more than 5 days then you take some action.

So, you want to first SELECT the records and return them to a recordset object. Then you can loop through the recordset object and look at each field to see whether to increment your day counter.

Check your Do...Loop Until DayCount = 5. It will keep looking at the same record until DayCount = 5, but if every Workday reason in that record is Jury duty or vacation, it will be an infinite loop.

Try this as a starting point.
This assumes that each record is a single week for an employee.
Fields: EmployeeID, WeekNum, WorkDay[n]Reason/n={1..5}

'using DAO. I'm not sure how you connec the db in DAO
'Assume db is connected
dim db as database
dim rs as recordset
dim strSQL as string
const CONST_TABLENAME = "tblAttendance" 'Table name

strSQL = "SELECT * from " & CONST_TABLENAME & " ORDER BY " & EmployeeID & ", " & WeekNum& ";"
set rs = db.openrecordset strSQL
CountEmployeeDays(rs)
rs.close
set rs = nothing
set db = nothing

private sub CountEmployeeDays(rs as recordset)
dim i as long
dim j as long
dim EmpNum as long 'or string if that's what you've got
dim EmpCount as long 'count of days for employee
dim DayField as string

on error goto errhandler
with rs
.movefirst
EmpNum = .fields("EmployeeID&quot.Value

'Trick here, break out of loop for .eof
'or for change in EmployeeID, but don't get .eof error
While not .eof
while EmpNum = .fields("EmployeeID&quot.Value

'For each WorkDay[n]Reason ={"", "HolD-A"}
'increment employee's count value
for i = 1 to 5
dayfield = "WorkDay" & cstr(i) & "Reason"
if .fields(dayfield) = "" or _
.fields(dayfield = "HolD-A&quot then
empcount = empcount + 1
endif
next i
.movenext 'next record
wend 'New employee number
WriteEmployeeInfo:
'Here is where you write your output
'to another table or update your employee's record
'or whatever
debug.print "Employee number " & empnum & _
" missed " & empcount & " unexcused days."
wend
end with

exit sub
errhandler:
'You will get an error. It will be .eof error when
'the inner while loop hits the end of the recordset
'write out the last employee number.
goto WriteEmployeeInfo
end sub

I'm sure this doesn't fit your problem exactly, but maybe
it will give you some ideas.

scarfhead

 

[quest4]

Current, I am on the verge of giving up on Access2000 for this and taking it back to UNIX where it will be easy. I will give it one last shot. What I am doing aor at least attempting to be is from my table tblAttendence, look at each week for 13 weeks starting one week back, in other words, Date()-7. for a list of employees who have perfect attendence. I need some information from the table tblEmployees, which has all of the non attendence info. It will get a little tuffer when I add in the last conponent, but I figure start with the easy part. I will try your code, after all all mine did was error when I tried to exit Access. Thanks for the respone and the help.

 

[quest4]

Tried putting in your code, it is almost as good as mine. I get RED on the line that starts out strSQL, near the top. Also the get RED on the next line which starts out set rs. I found out it does not like set for anything. Thanks for the help.

 

[scarfhead]

red on strSQL line is because the line breaks, use an underscore to continue at the next line.

red on the set rs = line may be because db is not defined or you may not have a reference to DAO. Note that the comment I put in assumes you have successfully connected db to your database. That was one of my frustrations with DAO, connecting databases when you're not in Access.

Maybe someone else can fill in how to do that. Also, this forum has great FAQs and the messages are searchable by keyword. It's an excellent resource if you check it out.

Sorry, I haven't got all the answers. I'm just trying to point you in the right direction.

scarfhead

 

[quest4]

The red on the SQl line in one long line for me and it give me a compile error: Expected End of statement and it high-lites the semi-collin, ":". Thanks for the help.

 

[quest4]

I found the error a missing space. How do I test run this beast? Thanks.