Sick Leave count

[Tia]

DEAR ALL,
I have a list of attendance along with a working code that includes
working day , sick,annual leave
I want to know how much each employee has as sick leave i have used
this query but i am geting data mispatch

SELECT [JANUARY 25-12/24-01 ATTENDANCE].ID, [JANUARY 25-12/24-01
ATTENDANCE].Employee, [JANUARY 25-12/24-01 ATTENDANCE].[Work Code],
Count([JANUARY 25-12/24-01 ATTENDANCE].[Work Code]) AS [CountOfWork
Code]
FROM [JANUARY 25-12/24-01 ATTENDANCE]
GROUP BY [JANUARY 25-12/24-01 ATTENDANCE].ID, [JANUARY 25-12/24-01
ATTENDANCE].Employee, [JANUARY 25-12/24-01 ATTENDANCE].[Work Code]
HAVING ((([JANUARY 25-12/24-01 ATTENDANCE].[Work Code])="sick"));

Please help

 

[James Ford]

mmmmm.....?

 

[Tia]

DEAR ALL,
I have a list of attendance along with a working code that includes
working day , sick,annual leave
I want to know how much each employee has as sick leave i have used
this query but i am geting data mispatch

SELECT [JANUARY 25-12/24-01 ATTENDANCE].ID, [JANUARY 25-12/24-01
ATTENDANCE].Employee, [JANUARY 25-12/24-01 ATTENDANCE].[Work Code],
Count([JANUARY 25-12/24-01 ATTENDANCE].[Work Code]) AS [CountOfWork
Code]
FROM [JANUARY 25-12/24-01 ATTENDANCE]
GROUP BY [JANUARY 25-12/24-01 ATTENDANCE].ID, [JANUARY 25-12/24-01
ATTENDANCE].Employee, [JANUARY 25-12/24-01 ATTENDANCE].[Work Code]
HAVING ((([JANUARY 25-12/24-01 ATTENDANCE].[Work Code])="sick"));

Please help- Hide quoted text -

- Show quoted text -

??????????????????????????????????

 

[AccessVandal via AccessMonster.com]

It appears that you have a unnormalized database. Access is not Excel, you
can't view the tables like a spreadsheet.

In other words, you have more than one table for Attendance say Jan to Dec? I
think you should stop now and go back to the drawing board. Here are some
normalization rules.

Description of database normalization basics in Access 2000 (applies to all
database)
http://support.microsoft.com/kb/209534

"Understanding Relational Database Design" document is available in Download
Center
http://support.microsoft.com/kb/283698/

Microsoft Access Data Normalization Fundamentals from FMS
http://www.fmsinc.com/TPapers/datanorm/index.html

Come back to ask when you have anymore questions.

DEAR ALL,
I have a list of attendance along with a working code that includes
working day , sick,annual leave
I want to know how much each employee has as sick leave i have used
this query but i am geting data mispatch

SELECT [JANUARY 25-12/24-01 ATTENDANCE].ID, [JANUARY 25-12/24-01
ATTENDANCE].Employee, [JANUARY 25-12/24-01 ATTENDANCE].[Work Code],
Count([JANUARY 25-12/24-01 ATTENDANCE].[Work Code]) AS [CountOfWork
Code]
FROM [JANUARY 25-12/24-01 ATTENDANCE]
GROUP BY [JANUARY 25-12/24-01 ATTENDANCE].ID, [JANUARY 25-12/24-01
ATTENDANCE].Employee, [JANUARY 25-12/24-01 ATTENDANCE].[Work Code]
HAVING ((([JANUARY 25-12/24-01 ATTENDANCE].[Work Code])="sick"));

Please help

 

[BruceM via AccessMonster.com]

I agree with another posting in this thread that you seem to have date that
is not normalized. If you have a different table for each year, you
definitely have unnormalized data.

That being said, something like this may help solve the specific problem:

SELECT [ID], [Employee], Count([WorkCode]) AS [CountOfWorkCode]
FROM [JANUARY 25-12/24-01 ATTENDANCE]
WHERE [WorkCode] = "sick"
GROUP BY ID, Employee;

Again, while this may let you generate some numbers for year-end or something
like that, it does not begin to address the structural issues with the
database. Some study may be in order. It will certainly save you time later.
Here are some links that should help. IMHO Crystal's tutorial is as good a
place as any to get started, but all of the links are worth checking out.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

[quoted text clipped - 14 lines]

- Show quoted text -

??????????????????????????????????