Query the occurrence of a particular absent reason in past 12 months? (1 Viewer)

[KevinSlater]

Hi, i have a query which looks at a table named "attendance" the query includes the fields: "employee number", "absent code" (a 3 letter code), "absent reason" (list of different reasons) "shiftdate" (weekly shift dates in format: DD/MM/YYYY). If possible i would like to be able to promt the user to enter a employee number, then a absent reason (ideally from a list box) and show all occurences (with all the shiftdates) only of that particular absent reason within the last 12 months, and ideally calculates in another field the number of times that employee has been absent for that particular reason for the last 12 months.

Does this make sence?, i know how to promt the user to enter a employee number but not sure how to go about the rest, any help with what seems to be quite a challenging task to me would be excellent.

 

[iago18]

You've asked for a lot...so I'll try to give it.

Steps needed:

1. Create a form with an unbound text or combo box for users to selected employee numbers
2. Create a second unbound combo box of all absent codes for user selection
3. Create an unbound text box and set its default value to DateAdd('y',-1,Date()) and its format to 'ShortDate'. This will be the date used to cut off attendance records. If you don't want users to edit it you can lock it or make it invisible.
3. Create a listbox and set its rowsourcetype to Query and its rowsource data to all records in the attendance table.
4. Add the fields (i.e. shiftdate) to the listbox that you want to display (modify the number and width of the listbox columns accordingly)
5. In the criteria of the listbox's query reference the 3 unbound controls on the form by name. Something like EmpNum = [Forms]![Form1]![EmpNum] By settings these as criteria of the listbox, the listbox won't display any records to start until all these fields have been set.
6. In the AfterUpdate() event of each of the 3 unbound controls put code: Listbox.Requery That will refresh listbox result each time a change to a criteria is made.
7. If you want an extra field to count absenses, put this as another unbound field on the form (not in the listbox). The field should be a locked text box. Set the ControlSource to ={YourListBox}.ListCount without the { signs. This will then display the current count of records displayed in the listbox and should update auyomatically.

I think I'll stop there. That should get you where you want to go, but it might take some time to perfect. Give is a shot and then come back with more questions.

 

[KevinSlater]

Yeah sorry i did ask for quite a lot, thanks very much iago18 for taking the time to explain all of that! & in steps, might take me a while but i will give this a good go and let you know how i get on. I guess it cant be created directly from a query first and has to be created in a form instead?.

 

[iago18]

Yes you can do this all in a query. The query needed would be the one I described for the listbox. You'd have to use 'parameters' to prompt the user to enter the needed emp num and attendance type criteria. Adding a 'attendance count' field to this query would be a bit strange however because each record of the query would need to display the total count. I don't think this would be useful; the user could just note the number of records the query pulled and retrieve the same information.

All in all, I think a form is better. You could also then create a report listing all attendance records and then filter by the criteria of the form. Good luck.

 

[KevinSlater]

Ok thanks for that iago18, sorry about late reply, ive been away, yes sounds like form would be better, im trying what you suggested now, will get back to you to let you know how it goes.