Tracking dates, I'm stumped 1

[Apollo13a]

I'm trying to figure a way to track the 1st date and last date in my db. I have an employee table and a sign in table. The two are linked by the EmpID. An employee will have couple of sign in dates e.g 10-3-03, 10-10-03, 10-17-03 and 10-24-03. I know how to get the last sign in date(dmax function or last). But the series will start over, e.g. 12-1-03 12-7-03, 12-14-03. How can I group these so I can get the first and last from the group? Also, once the group starts over I still need to keep the records to create a report. I know I need to add some fields to create a method to identfy the group but I'm not sure how keep the groups of sign in dates unique. Any help would be greatly appreciated.
thanks, Jim

 

[JonFer]

How do you want to group the dates? By month?

You can add a field to the query which shows just the year and month using this: Format([DateField],"yyyy-mm&quot. Include that field in your Group By clause, too. Use Max([DateField]) in your query, not DMax or Last([DateField]).

 

[Apollo13a]

Thanks for the reply. No I don't want to group by month. That's my problem. I don't have anything to group these dates on, but I want to be able to pull out the First Sign In Date and the Last Sign In Date but each employee will have more than one Sign In Date and more than one Last Sign In Date. I need to add another field or two that would distinguish each Sign In Date(and each Last Sign In Date) for each employee as unique. Kind of strange, I know.
Thanks, Jim

 

[PaulBricker]

Can you post an example of what you want the data to look like. That might help spark some ideas.

Paul

 

[CajunCenturion]

It's not that strange. Recently, you stated that you don't have anything to group these dates on, but in original question, you stated that the series will start over.

How do you know that the series has started over?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Apollo13a]

Thanks for the replies. Maybe if I explain what I'm trying to do, it would make it clearier. I'm tracking when employee's sign an out of work list(usually every Mon or Tue morning but could be anythime). I am also tracking when they receive their assignments but not necessarily the contractor that they are assigned to. [I will list my tables at the end]. Here's a run down for one employee. Employee A signs the list on 11-3-03 and his status becomes unemployeed. He's signs the list again on 11-10-03 and his status is still unemployeed. He signs again on 11-17-03 with status unemployeed. On 11-18-03 Employee A is given an assignment and his status changes to Employeed. So far,I'm only entering the Sign-In-Date and changing his status to either U or E(employeed or unemployeed). I would like to be able to pull out the First sign in date, the last sign in date and the date he last signed in reference to the current date. Now Employee A finishes his assignment and he signs the list again, say 12-1-03 and now his status changes to unemployeed. Employee A signs again on 12-8-03 with status Unemployeed. He signs again on 12-15-03 with status Unemployeed. On 12-16-03 he receives an assignment and his status changes back to Employeed. So as you can see it's sort of like a cycle of signing the list when your unemployeed and not signing when you are employeed. I need to be able track the cycles of when they sign and find out the First sign date and last sign date for each cycle. I'm also going to track the amount odf days on the list but I figure that out later. here are my tables so far:

tblEmployee:
TicketNo.(PK)Unique as each emp. only gets one and it's the same forever)
EmpID(autonumber)
FirstName
LastName
etc.
StatusID(I think I need to move this)
groupID(from Look up table)
RateID(from lookup table)
ClassID(from Lookup table)

tblSignIn
EmpID
SignInDate

tblAssign:
EmpID
StartDate
EndDate
SignInID
ContrID

tblContractor
Contractor Name and info
ContrID

I know that I need to probable enter some more info during data entry but I'm not sure what.
Any help would be greatly appreciated as i'm am a rookie when it comes to Access and DB design.
Thanks again, Jim G

 

[Apollo13a]

This IS the question, "How do I know that the series has started over"? Maybe I could use some kind of counter for each cycle based on each EmpID and archive them when I'm done? I'm not sure.
Jim

 

[CajunCenturion]

If I'm reading this correctly, it sounds like a cycle is tied directly to status updates to/from employed/unemployed.

Could you add a table tblStatusUpdate with two fields (Date of Status Changes) and (Status after Change). Then you can use this date to get the beginning date of Status Set to Employment, and the next status change to unemployed as the Range of Dates that you can use to filter back to your sign in dates?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Apollo13a]

CajunCenturion, Capital idea. I've added the new table and am working on the rest.
Thanks again,
Jim G