Finding Occurences

[p85ki]

Hi,

I have been trying to find the answer everywhere but kind of struggling with any forum or knowledge of how to do this. Hope anyone can assist.

I am sorting out a database that relates to attendance. I have mapped out the attendance accordingly with days in, days off, sick, so I now have visibility of where staff are.

I am trying to write Ideally in Access a method of checking the occurrence of their attendance. As an example below.

A Smith
January
1st Day In
2nd Sick
3rd Sick
4th Day Off
5th Sick
6th Sick
7th Day In
8th Day In
9th Day In
10th Sick

So between the 2nd and 6th January, I would treat this as 1 occurrence of sick as the 7th would be a Day In. The 10th would be a 2nd Occurrence.

I'm not sure if there is any way I can write this to check. I am struggling to think of a way to write this (if it's even possible).

Any help please

 

[LockeGarmin]

It's a little messy but here it goes:

1. Create 'Table1' with the following fields: 'ID' (AutoNumber), 'Field1' (Text). Put your "Day In", "Day Out", and "Sick" data in Field1.

2. Create 'Query1' and put in the following SQL:

SELECT Table1.ID, Table1.Field1
FROM Table1
WHERE Table1.Field1 IN ("Sick", "Day In");

3. Create 'Query2' and put in the following SQL:

SELECT (SELECT COUNT(*) FROM Query1 WHERE A.ID>=ID) AS RowNum, A.Field1
FROM Query1 AS A
ORDER BY A.ID;

4. Create 'Query3' and put in the following SQL:

SELECT Count(Query2.Field1) AS Expr1
FROM Query2 AS Query2_1 INNER JOIN Query2 ON Query2_1.RowNum+1 = Query2.RowNum
WHERE (((Query2.Field1)="Sick") AND ((Query2_1.Field1)="Day In"));

You can now use the following line in VBA to find the Sick Occurrence Count:

DFirst("Expr1","Query3")

Hope that helps!

 

[HiTechCoach]

I'm not sure if there is any way I can write this to check. I am struggling to think of a way to write this (if it's even possible).

Any help please

It would really help to have more detail of how you need the results.

If this for a report? Is yes, what should it look like?

If all you need is:

A Smith
[B]January[/B]
  Day In
  Sick
  Day In
  Sick

Then it can be easily done in a report with no VBA code required.