join multiple queries in one query "Attendance calculation"

  • Thread starter Thread starter Tia
  • Start date Start date
T

Tia

Dear all,

Thank you so much for your help this forum was so much helpfull for me
i haven't any clue how to use the access i am better now because of
you.
I am trying to create a payroll calculation
i have sorted out the list of attendance by Sick leave, working days,
annual leave, Public holiday for each month of attendance.
What i need now is to join all the soreted out queries in one query
that gives me the total of working, sick, annual, public holiday for
each employee.

I am trying to do it but nothing is appearing here is the queries that
i have
Working list query=
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].[1stshiftqu]) AS
CountOf1stshiftqu, [JANUARY 25-12/24-01 ATTENDANCE].[1stshiftqu],
[JANUARY 25-12/24-01 ATTENDANCE].[Date Worked]
FROM [JANUARY 25-12/24-01 ATTENDANCE] AS [JANUARY 25-12/24-01
ATTENDANCE_1] INNER JOIN [JANUARY 25-12/24-01 ATTENDANCE] ON [JANUARY
25-12/24-01 ATTENDANCE_1].ID = [JANUARY 25-12/24-01 ATTENDANCE].ID
GROUP BY [JANUARY 25-12/24-01 ATTENDANCE].ID, [JANUARY 25-12/24-01
ATTENDANCE].Employee, [JANUARY 25-12/24-01 ATTENDANCE].[Work Code],
[JANUARY 25-12/24-01 ATTENDANCE].[1stshiftqu], [JANUARY 25-12/24-01
ATTENDANCE].[Date Worked];

COUNT WRKING DAYS FOR THIS MONTH=
=SELECT [JANURAY SORT BY WORKING DAY].Employee, CDbl(nz(Count([JANURAY
SORT BY WORKING DAY]![Lookup_Work Code.ID]),0)) AS [WORKING DAYS],
[JANURAY SORT BY WORKING DAY].[Work Code]
FROM [JANURAY SORT BY WORKING DAY]
GROUP BY [JANURAY SORT BY WORKING DAY].Employee, [JANURAY SORT BY
WORKING DAY].[Work Code];

count sick leave taken this month=
SELECT [JANURAY SORT BY SICK LEAVE].Employee, CDbl(nz(Count([JANURAY
SORT BY SICK LEAVE]![Lookup_Work Code.ID]),0)) AS [WORKING DAYS],
[JANURAY SORT BY SICK LEAVE].[Work Code]
FROM [JANURAY SORT BY SICK LEAVE]
GROUP BY [JANURAY SORT BY SICK LEAVE].Employee, [JANURAY SORT BY SICK
LEAVE].[Work Code];

our continuos help
Thank you for y
 
The way it is written, it's practically undereadable. Before you crunch
under it's weight, you should start redesign it presto!

First, don't write everything in UPPER CASES, it makes your code very hard
to read. Also stop using blank spaces in the name of your tables/columns;
you're only shooting yourself in the feet with this.

Second, start using aliases to simplify your task:

Select Ja.ID, Ja.Employee, Ja.[Work code],
Count(Ja.[1stshiftqu]) as CountOf1stshiftqu,
Ja.[1stshiftqu], Ja.[Date worked]
From [January 25-12/24-01 Attendance] as Ja1 inner join
[January 25-12/24-01 Attendance] as Ja ON Ja1.ID = Ja.ID
Group By Ja.ID, Ja.Employee, Ja.[Work code],
Ja.[1stshiftqu], Ja.[Date worked];

From this, we can see that the above query make no sense because you are
joining a table with itself with no reason at all.

Finally, in the piece of code below, you are using the Bang ! operator in an
improper way:
COUNT WRKING DAYS FOR THIS MONTH=
=SELECT [JANURAY SORT BY WORKING DAY].Employee, CDbl(nz(Count([JANURAY
SORT BY WORKING DAY]![Lookup_Work Code.ID]),0)) AS [WORKING DAYS],
[JANURAY SORT BY WORKING DAY].[Work Code]
FROM [JANURAY SORT BY WORKING DAY]
GROUP BY [JANURAY SORT BY WORKING DAY].Employee, [JANURAY SORT BY
WORKING DAY].[Work Code];
Click to expand...

This is not valid for referencing a column from a table in SQL; it can be
used only when you are referencing a control on your form or on a report.
Idem for your third piece of code.

Finally, using the date in the name of a table is a very, very, very bad
design and this will kill your project in the long term. The date should be
a column in the (single) table; you don't split your table into multiples
smaller tables with a date in their names.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


Tia said:
Dear all,

Thank you so much for your help this forum was so much helpfull for me
i haven't any clue how to use the access i am better now because of
you.
I am trying to create a payroll calculation
i have sorted out the list of attendance by Sick leave, working days,
annual leave, Public holiday for each month of attendance.
What i need now is to join all the soreted out queries in one query
that gives me the total of working, sick, annual, public holiday for
each employee.

I am trying to do it but nothing is appearing here is the queries that
i have
Working list query=
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].[1stshiftqu]) AS
CountOf1stshiftqu, [JANUARY 25-12/24-01 ATTENDANCE].[1stshiftqu],
[JANUARY 25-12/24-01 ATTENDANCE].[Date Worked]
FROM [JANUARY 25-12/24-01 ATTENDANCE] AS [JANUARY 25-12/24-01
ATTENDANCE_1] INNER JOIN [JANUARY 25-12/24-01 ATTENDANCE] ON [JANUARY
25-12/24-01 ATTENDANCE_1].ID = [JANUARY 25-12/24-01 ATTENDANCE].ID
GROUP BY [JANUARY 25-12/24-01 ATTENDANCE].ID, [JANUARY 25-12/24-01
ATTENDANCE].Employee, [JANUARY 25-12/24-01 ATTENDANCE].[Work Code],
[JANUARY 25-12/24-01 ATTENDANCE].[1stshiftqu], [JANUARY 25-12/24-01
ATTENDANCE].[Date Worked];

COUNT WRKING DAYS FOR THIS MONTH=
=SELECT [JANURAY SORT BY WORKING DAY].Employee, CDbl(nz(Count([JANURAY
SORT BY WORKING DAY]![Lookup_Work Code.ID]),0)) AS [WORKING DAYS],
[JANURAY SORT BY WORKING DAY].[Work Code]
FROM [JANURAY SORT BY WORKING DAY]
GROUP BY [JANURAY SORT BY WORKING DAY].Employee, [JANURAY SORT BY
WORKING DAY].[Work Code];

count sick leave taken this month=
SELECT [JANURAY SORT BY SICK LEAVE].Employee, CDbl(nz(Count([JANURAY
SORT BY SICK LEAVE]![Lookup_Work Code.ID]),0)) AS [WORKING DAYS],
[JANURAY SORT BY SICK LEAVE].[Work Code]
FROM [JANURAY SORT BY SICK LEAVE]
GROUP BY [JANURAY SORT BY SICK LEAVE].Employee, [JANURAY SORT BY SICK
LEAVE].[Work Code];

our continuos help
Thank you for y
Click to expand...
 
Also you can not count items and display the discrete itemes that you are
counting as you tried --
Count([JANUARY 25-12/24-01 ATTENDANCE].[1stshiftqu]) AS
CountOf1stshiftqu, [JANUARY 25-12/24-01 ATTENDANCE].[1stshiftqu],

--
Build a little, test a little.


Sylvain Lafontaine said:
The way it is written, it's practically undereadable. Before you crunch
under it's weight, you should start redesign it presto!

First, don't write everything in UPPER CASES, it makes your code very hard
to read. Also stop using blank spaces in the name of your tables/columns;
you're only shooting yourself in the feet with this.

Second, start using aliases to simplify your task:

Select Ja.ID, Ja.Employee, Ja.[Work code],
Count(Ja.[1stshiftqu]) as CountOf1stshiftqu,
Ja.[1stshiftqu], Ja.[Date worked]
From [January 25-12/24-01 Attendance] as Ja1 inner join
[January 25-12/24-01 Attendance] as Ja ON Ja1.ID = Ja.ID
Group By Ja.ID, Ja.Employee, Ja.[Work code],
Ja.[1stshiftqu], Ja.[Date worked];

From this, we can see that the above query make no sense because you are
joining a table with itself with no reason at all.

Finally, in the piece of code below, you are using the Bang ! operator in an
improper way:
COUNT WRKING DAYS FOR THIS MONTH=
=SELECT [JANURAY SORT BY WORKING DAY].Employee, CDbl(nz(Count([JANURAY
SORT BY WORKING DAY]![Lookup_Work Code.ID]),0)) AS [WORKING DAYS],
[JANURAY SORT BY WORKING DAY].[Work Code]
FROM [JANURAY SORT BY WORKING DAY]
GROUP BY [JANURAY SORT BY WORKING DAY].Employee, [JANURAY SORT BY
WORKING DAY].[Work Code];
Click to expand...

This is not valid for referencing a column from a table in SQL; it can be
used only when you are referencing a control on your form or on a report.
Idem for your third piece of code.

Finally, using the date in the name of a table is a very, very, very bad
design and this will kill your project in the long term. The date should be
a column in the (single) table; you don't split your table into multiples
smaller tables with a date in their names.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


Tia said:
Dear all,

Thank you so much for your help this forum was so much helpfull for me
i haven't any clue how to use the access i am better now because of
you.
I am trying to create a payroll calculation
i have sorted out the list of attendance by Sick leave, working days,
annual leave, Public holiday for each month of attendance.
What i need now is to join all the soreted out queries in one query
that gives me the total of working, sick, annual, public holiday for
each employee.

I am trying to do it but nothing is appearing here is the queries that
i have
Working list query=
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].[1stshiftqu]) AS
CountOf1stshiftqu, [JANUARY 25-12/24-01 ATTENDANCE].[1stshiftqu],
[JANUARY 25-12/24-01 ATTENDANCE].[Date Worked]
FROM [JANUARY 25-12/24-01 ATTENDANCE] AS [JANUARY 25-12/24-01
ATTENDANCE_1] INNER JOIN [JANUARY 25-12/24-01 ATTENDANCE] ON [JANUARY
25-12/24-01 ATTENDANCE_1].ID = [JANUARY 25-12/24-01 ATTENDANCE].ID
GROUP BY [JANUARY 25-12/24-01 ATTENDANCE].ID, [JANUARY 25-12/24-01
ATTENDANCE].Employee, [JANUARY 25-12/24-01 ATTENDANCE].[Work Code],
[JANUARY 25-12/24-01 ATTENDANCE].[1stshiftqu], [JANUARY 25-12/24-01
ATTENDANCE].[Date Worked];

COUNT WRKING DAYS FOR THIS MONTH=
=SELECT [JANURAY SORT BY WORKING DAY].Employee, CDbl(nz(Count([JANURAY
SORT BY WORKING DAY]![Lookup_Work Code.ID]),0)) AS [WORKING DAYS],
[JANURAY SORT BY WORKING DAY].[Work Code]
FROM [JANURAY SORT BY WORKING DAY]
GROUP BY [JANURAY SORT BY WORKING DAY].Employee, [JANURAY SORT BY
WORKING DAY].[Work Code];

count sick leave taken this month=
SELECT [JANURAY SORT BY SICK LEAVE].Employee, CDbl(nz(Count([JANURAY
SORT BY SICK LEAVE]![Lookup_Work Code.ID]),0)) AS [WORKING DAYS],
[JANURAY SORT BY SICK LEAVE].[Work Code]
FROM [JANURAY SORT BY SICK LEAVE]
GROUP BY [JANURAY SORT BY SICK LEAVE].Employee, [JANURAY SORT BY SICK
LEAVE].[Work Code];

our continuos help
Thank you for y
Click to expand...


.
Click to expand...
 
And you spelled (or misspelled) the month two ways:

"JANURAY" and "JANUARY"


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Sylvain said:
The way it is written, it's practically undereadable. Before you crunch
under it's weight, you should start redesign it presto!

First, don't write everything in UPPER CASES, it makes your code very hard
to read. Also stop using blank spaces in the name of your tables/columns;
you're only shooting yourself in the feet with this.

Second, start using aliases to simplify your task:

Select Ja.ID, Ja.Employee, Ja.[Work code],
Count(Ja.[1stshiftqu]) as CountOf1stshiftqu,
Ja.[1stshiftqu], Ja.[Date worked]
From [January 25-12/24-01 Attendance] as Ja1 inner join
[January 25-12/24-01 Attendance] as Ja ON Ja1.ID = Ja.ID
Group By Ja.ID, Ja.Employee, Ja.[Work code],
Ja.[1stshiftqu], Ja.[Date worked];

From this, we can see that the above query make no sense because you are
joining a table with itself with no reason at all.

Finally, in the piece of code below, you are using the Bang ! operator in an
improper way:
COUNT WRKING DAYS FOR THIS MONTH=
=SELECT [JANURAY SORT BY WORKING DAY].Employee, CDbl(nz(Count([JANURAY
SORT BY WORKING DAY]![Lookup_Work Code.ID]),0)) AS [WORKING DAYS],
[JANURAY SORT BY WORKING DAY].[Work Code]
FROM [JANURAY SORT BY WORKING DAY]
GROUP BY [JANURAY SORT BY WORKING DAY].Employee, [JANURAY SORT BY
WORKING DAY].[Work Code];
Click to expand...

This is not valid for referencing a column from a table in SQL; it can be
used only when you are referencing a control on your form or on a report.
Idem for your third piece of code.

Finally, using the date in the name of a table is a very, very, very bad
design and this will kill your project in the long term. The date should be
a column in the (single) table; you don't split your table into multiples
smaller tables with a date in their names.
Click to expand...
 
Back
Top