I'll point out that storing values that you can calculate is only rarely a
good idea. Rather than creating a table of "days since", you'll probably be
better off calculating them.
It sounds like you want to count the number of non-"Rest day" shifts that
follow a "Rest day", up to, but not including the next "Rest day". And to
do this for each person, and for some (?) time frame (e.g., the last month,
the last week, the last year, ...).
I'm wondering if you could use a report, and use the running sum property of
a text control to "count", then "reset" at each "Rest day"... You could use
the same query that gives you the subform data as a source for the report,
but wouldn't display the Shift type, rather a "count".
--
Regards
Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
Laudrup said:
Sorry I wasn't sure what info was needed, I hope this make it more
clearer.
The structure of my database is as follows; (The relevant tables and
attributes for this question)
Table: Employee
EMPID (AutoNo.) - PK
EmpForename (Text)
EmpSurname (Text)
Table: ShiftDetails
SHIFTID (AutoNo.) - PK
ShiftName (Text)
Table: ShiftLog
CMPID (AutoNo.) - PK
EMPID (Number)
SHIFTID (Number)
SDate (Date)
I then have the following queries;
Qry1: IndEmpSearchQry - Used for Master Form
EMPID (Employee)
EmpForename
EmpSurname [Enter Surname]
Qry2 : IndEmpSearchSFQry - Used for Master Form
EmpForename (Employee)
EmpSurname (Employee)
ShiftName (ShiftDetails)
SDate (ShiftLog) (Sort Acceding)
The form then displays the data;
Form.
TextBox - =[EmpForename] & " " & [EmpSurname]
Subform.
TextBox - SDate TextBox - ShiftName
1/8/06 On Site
2/8/06 On Site
3/8/06 Rest Day
4/8/06 On Site
6/8/06 On Site
7/8/06 On Site
*From this I would like the form to show the count of how many shifts
that were done from
the last 'Rest Day', from either an unbound TextBox or create a new
table that can store the shift count*
I hope this explains better. Again any help would be greatly
appreciated.
-----------------------------------------
Jeff said:
Databases ... store data. You've described the Form (how data is
displayed), but not the underlying data structure.
A "how to" depends on how your data is organized (i.e., your table
structure).
--
More info, please ...
Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
I have a database that list names of employees and the days they
worked/rested.
From this I have created a form that allows you to lookup each employee
and display what job was done on each day.
For example....
Form.
Employee Name
Subform.
Date JobDetail
1/8/06 On Site
2/8/06 On Site
3/8/06 Rest Day
4/8/06 On Site
6/8/06 On Site
7/8/06 On Site
I would like to create a count of how many shifts that were done from
the last 'Rest Day'
For example....
Form.
Employee Name
Subform.
Date JobDetail ShiftCount
1/8/06 On Site 3
2/8/06 On Site 4
3/8/06 Rest Day 5
4/8/06 On Site 1
6/8/06 On Site 2
7/8/06 On Site 3
Any ideas on how this is done?
Any help would be greatly appreciated.
