Interesting HR Database - Queries

[Allie]

I am having 2 issues with an employee absentee database.
Here is my set up

-Table "Records" which contains all employee absentee information (name, id,
dates,...) (Based on a linked excel spreadsheet from a network drive, which
gets updated every week.)

-Query "Records Query" which sums each employee's total absences, so there
is 1 record for each id number and it's corresponding sum. (Based on
"Records" table)

-Query for sums greater than 5 (Based on "Records Query")
-Query for sums greater than 6 (Based on "Records Query")
-Query for sums greater than 7 (Based on "Records Query")

I need to be able to have a report (preferable in spreadsheet form) that
will, for example, so ALL absences for ALL employees who have missed 7 days.
How can I connect the data from the table based on the badge numbers from the
query. So in other words, if the badge number shows up on the query for
greater than 7 absences, then I want to see what those absences are.

My second issue, and this is a big one, is that I only want to see the
absences for a year long period, but that period has a different start date
for each employee. The period begins on the date of the 1st absence. So if an
employees 1st absence was 5/5/08 and then throughout the year missed 3 more
days, then on 5/5/09, I want all 4 records to delete (or maybe we could just
report on current ones, but still not sure how to accomplish) Any ideas on
this one?

 

[Dale Fye]

Personally,

I'd start with a single query (qry_Absences) that returns the date of the
first absense, and the number of absences for each person.

SELECT EmpID, Min(AbsDate) as FirstAbs, COUNT(EmpID) as CountOfAbs
FROM your table
GROUP BY EmpID

Then, you can create another query that returns the absence data for those
with 7 or more absenses:

SELECT EmpID, AbsDate
FROM yourTable
INNER JOIN qry_Absences
ON yourTable.EmpID = qry_Absences.EmpID
WHERE qry_Absences.CountOfAbs >= 7

I'll have to think about the 2nd issue, but If I understand you correctly,
what you want is to count the number of absenses since the most recent
anniversary of their first absense. Is that correct?
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Allie]

Hi Dale,

Yes I think you have it right. So for example:

2/8/08 - absent
5/10/08 - absent
7/12/08 - absent
Today, if I run the report, employee A would have 3 absences.
now lets say...
12/20/08 - absent
2/10/09 - absent
Now if i run the report, it should say 1 absence.

I will keep playing after I attempt your first advice
Thanks!

 

[Allie]

Can anyone answer the 1st part of my question, how do I return ALL the data
for those resulting from the sum query? I am not sure if I was clear before
but i would like those employees who have more than 7 absences to return
(which they do, with the sum and also the 1st absence date) but I need all
the data that goes with it...dates and reason of all absences, name, etc.
from the table.

 

[Dale Fye]

Allie, take my second query, and modify it to include all of the fields from
"yourTable" that you need, but you have to create the first query and then
link it to your table. It will look like:

SELECT yourTable.*
FROM yourTable
INNER JOIN qry_Absences
ON yourTable.EmpID = qry_Absences.EmpID
WHERE qry_Absences.CountOfAbs >= 7


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Allie]

Hi Dale,
Thanks for the quick response. I believe that is how I have it set up but it
is returning all the records.

Query 1
SELECT DISTINCTROW Records.BADGE, Sum(Records.DAYS) AS [Sum Of DAYS],
Min(Records.DATE) AS [First Absence DATE]
FROM Records
GROUP BY Records.BADGE;

Query 2
SELECT records.BADGE, records.NAME, records.CC, records.TEXT, records.DATE
FROM Records
INNER JOIN [Records Query] ON Records.BADGE = [Records Query].BADGE
WHERE [Records Query].[First Absence DATE] >= 7;

 

[Dale Fye]

For the second part, you should be able to use the following:

SELECT EmpID, AbsDate
FROM yourTable
INNER JOIN qry_Absences
ON yourTable.EmpID = qry_Absences.EmpID
WHERE yourTable.AbsDate > fnPreviousAnniversary(qry_Absences.FirstAbs)

But in order for this to work, you will have to copy the following function
and paste it into a code module. There is probably an easier way to get the
most recently past anniversary of a date, but one doesn't come to mind at the
moment.

Public Function fnPreviousAnniversary(ByVal SomeDate As Date) As Date

fnNextAnniversary = DateSerial(Year(Date), Month(SomeDate), Day(SomeDate))
fnNextAnniversary = fnNextAnniversary + (fnNextAnniversary > Date)

End Function


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Allie]

EDIT
I see what I did wrong. [ ] around the field name.

 

[Dale Fye]

my query 1 does not have a DISTINCTROW (get rid of this), and has a Count not
a sum. Why are summing the Days field? Does this keep track of the number
of days in an absence? If so, I guess that makes sense, but in your previous
statement you talked about counting absences. In my mind, an 3 day absence
for the flu is a single absence, therefore I used the Count function.

Also, the where clause in your 2nd query is refering to a date and comparing
it to the value 7. You should be comparing the [Sum of Days] >= 7.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

Cut and paste error. That function should read:

Public Function fnPrevAnniversary(ByVal SomeDate As Date) As Date

fnPrevAnniversary = DateSerial(Year(Date), Month(SomeDate),
Day(SomeDate))
fnPrevAnniversary = fnPrevAnniversary + (fnPrevAnniversary > Date)

End Function

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Allie]

Have to sum the dates not count because some are 1/2 days.

my query 1 does not have a DISTINCTROW (get rid of this), and has a Count not
a sum. Why are summing the Days field? Does this keep track of the number
of days in an absence? If so, I guess that makes sense, but in your previous
statement you talked about counting absences. In my mind, an 3 day absence
for the flu is a single absence, therefore I used the Count function.

Also, the where clause in your 2nd query is refering to a date and comparing
it to the value 7. You should be comparing the [Sum of Days] >= 7.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi Dale,
Thanks for the quick response. I believe that is how I have it set up but it
is returning all the records.

Query 1
SELECT DISTINCTROW Records.BADGE, Sum(Records.DAYS) AS [Sum Of DAYS],
Min(Records.DATE) AS [First Absence DATE]
FROM Records
GROUP BY Records.BADGE;

Query 2
SELECT records.BADGE, records.NAME, records.CC, records.TEXT, records.DATE
FROM Records
INNER JOIN [Records Query] ON Records.BADGE = [Records Query].BADGE
WHERE [Records Query].[First Absence DATE] >= 7;

 

[Dale Fye]

Did you fix the other things I recommended? Did that work?
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Have to sum the dates not count because some are 1/2 days.

my query 1 does not have a DISTINCTROW (get rid of this), and has a Count not
a sum. Why are summing the Days field? Does this keep track of the number
of days in an absence? If so, I guess that makes sense, but in your previous
statement you talked about counting absences. In my mind, an 3 day absence
for the flu is a single absence, therefore I used the Count function.

Also, the where clause in your 2nd query is refering to a date and comparing
it to the value 7. You should be comparing the [Sum of Days] >= 7.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi Dale,
Thanks for the quick response. I believe that is how I have it set up but it
is returning all the records.

Query 1
SELECT DISTINCTROW Records.BADGE, Sum(Records.DAYS) AS [Sum Of DAYS],
Min(Records.DATE) AS [First Absence DATE]
FROM Records
GROUP BY Records.BADGE;

Query 2
SELECT records.BADGE, records.NAME, records.CC, records.TEXT, records.DATE
FROM Records
INNER JOIN [Records Query] ON Records.BADGE = [Records Query].BADGE
WHERE [Records Query].[First Absence DATE] >= 7;

 

[Allie]

Haven't gotten the anniversary date quite working yet. I am trying to use a
DELETE QUERY to get rid of all the dates past the anniversary date, except
the most recent, which would be the new anniversary date.

So I have changed everything up a bit - comp crashed lost it all -
thankfully I had this thread to look back on!

SELECT Records.BADGE, Sum(Records.DAYS) AS SumOfDAYS, Min(Records.DATE) AS
MinOfDATE, Max(Records.DATE) AS MaxOfDATE, [MaxOfDate]-[MinOfDate] AS
DIFFERENCE
FROM Records
GROUP BY Records.BADGE;

So I have the badge #, the sum of days absent, the 1st date and the last
date, and the days in between.

What I have so far for the delete query would be if the DIFFERENCE between
1st date and last date is greater than 1 year, delete all but the last.

DELETE Records.*
FROM Records
INNER JOIN Sums ON Records.BADGE=Sums.BADGE
WHERE Sums.DIFFERENCE>365 And Sums.MaxOfDATE<>Records.Date

Keep getting an error that it cannot delete the records though. I think this
is because it's a linked table from excel on a network drive.

 

[Dale Fye]

You are correct. You will not be able to make changes to an EXCEL
spreadsheet from within Access, at least not with a query or even by opening
the link and trying to edit the cells. There was a lawsuit a couple of years
ago, where some gentleman claimed that Microsoft had illegally used a
technology he had rights to. Don't know the specifics, but the result was
that in 2003 and later, Office has lost the ability to directly edit the
contents of an Excel spreadsheet via linked tables. You can still look at
the data, and manipulate it via SELECT queries, but cannot change or delete
the values.

Having said that, why don't you just create a local table, and keep the
current records (from the spreadsheet) in it?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Allie]

Yes, I guess I could do that. I would go that route if I were going to be the
one managing this db. But it's going to be in the hands of HR, who have even
less Access experience than me. That's why I went with the linked table, so
they could just replace the file on their drive every week and then Access
would automatically update.

This is the 1st time I've used a DELETE QUERY. Gotta catch up on my reading!

 

[Dale Fye]

You could set it up so that the application automatically updates the local
table from the Excel file each time it loads, or the first time it is opened
each week, or something like that.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.