Want a Formatted report for monthly attendance

[Anand Vaidya]

Hi all,
I wanted a formatted report (in the table format, with rows and cols)for
monthly attendance of all the employees.
What I want is - that all the Day(s) and Date(s) should be horizontally
placed where as Name and Code should be vertically placed in the report.How
do I do this ? something like this---

JUNE---date----1---2---3---4---5----6---7--8---9................
---------day----M---T---W--T---F---S---S--M---T.............
code---name
a1------jill------P----P---P---P---P---A---P---P---P.................
a2------Jack----P----A---P---P---P---P---A---P---P.............
a3...............................................................................and so on.
where P= Present and A= Absent

 

[Allen Browne]

Use a crosstab query.

Presumably you have a table with fields like this:
ID primary key
EmployeeID foreign key to Employee.EmployeeID
AttendDate Date/Time

1. Create a query based on this table, and the one that has the employee
names.

2. Change it to a Crosstab query. (Crosstab on Query menu.)
Access adds Crosstab and Total rows to the design grid.

3. In a fresh column in the Field row, enter:
TheYear: Year([AttendDate])
In the Crosstab row under this, choose Row Heading.
Accept Group By in the Total row.

4. In the next column in the Field row, enter:
TheMonth: Month([AttendDate])
In the Crosstab row, choose Row Heading again.

5. Drag the Code field into the grid.
In the Crosstab row, choose Row Heading again.

6. Drag the employee name into the grid
Again, choose Row Heading.

7. In the next column in the Field row, enter:
TheDay: Day([AttendDate])
In the CrossTab row, choose Column Heading.

8. Drag the primary key field into the grid.
In the CrossTab row, choose Value.
In the Total row, choose Count.

9. Open the Properties box (View menu.)
Beside the Column Headings property, enter a separated list of the numbers
from 1 to 31:
1,2,3,4,5,..., 31

10. Save the query.

You can now use this query to create the report.
You will have a bit of formatting to do, e.g. to change the 0/1 into A/P.

 

[Anand Vaidya]

Allen, Date and Day are to be displayed as column headings and name and code
are to be displayed as row headings ( as shown in figure ).
Can't I generate a report as I require (with date and weekday as column
headings) because the msdn help says -
To create a cross tab query,you must specify-
One or more Row heading option
only one column heading option
and one value option.
I tried to take the Date and Day fields as row headings and Name field as
column heading and "Attendance Status" as Value option in Crosstab column.It
works fine but the problem with displaying Name as column heading is , you
have to display the Names in vertical alignment(Properties-Other-Vertical =
Yes) to save the space between two employees details which doesn't give a
good look.
-----------
Anand Vaidya
I am here to know.

Use a crosstab query.

Presumably you have a table with fields like this:
ID primary key
EmployeeID foreign key to Employee.EmployeeID
AttendDate Date/Time

1. Create a query based on this table, and the one that has the employee
names.

2. Change it to a Crosstab query. (Crosstab on Query menu.)
Access adds Crosstab and Total rows to the design grid.

3. In a fresh column in the Field row, enter:
TheYear: Year([AttendDate])
In the Crosstab row under this, choose Row Heading.
Accept Group By in the Total row.

4. In the next column in the Field row, enter:
TheMonth: Month([AttendDate])
In the Crosstab row, choose Row Heading again.

5. Drag the Code field into the grid.
In the Crosstab row, choose Row Heading again.

6. Drag the employee name into the grid
Again, choose Row Heading.

7. In the next column in the Field row, enter:
TheDay: Day([AttendDate])
In the CrossTab row, choose Column Heading.

8. Drag the primary key field into the grid.
In the CrossTab row, choose Value.
In the Total row, choose Count.

9. Open the Properties box (View menu.)
Beside the Column Headings property, enter a separated list of the numbers
from 1 to 31:
1,2,3,4,5,..., 31

10. Save the query.

You can now use this query to create the report.
You will have a bit of formatting to do, e.g. to change the 0/1 into A/P.

 

[Allen Browne]

I don't think I understand the terms as you are using them.

A crosstab can have only one field as its Column Headings.
The field names (headings over the columns) are derived from the values in
that field.

If you do in fact want the day-of-the-month (values 1 to 31) as your Column
Headings (as per your original example), my previous reply does that.

If you want something different, perhaps someone else can follow what you
asked.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, Date and Day are to be displayed as column headings and name and
code
are to be displayed as row headings ( as shown in figure ).
Can't I generate a report as I require (with date and weekday as column
headings) because the msdn help says -
To create a cross tab query,you must specify-
One or more Row heading option
only one column heading option
and one value option.
I tried to take the Date and Day fields as row headings and Name field as
column heading and "Attendance Status" as Value option in Crosstab
column.It
works fine but the problem with displaying Name as column heading is , you
have to display the Names in vertical alignment(Properties-Other-Vertical
=
Yes) to save the space between two employees details which doesn't give a
good look.
-----------
Anand Vaidya
I am here to know.

Use a crosstab query.

Presumably you have a table with fields like this:
ID primary key
EmployeeID foreign key to Employee.EmployeeID
AttendDate Date/Time

1. Create a query based on this table, and the one that has the employee
names.

2. Change it to a Crosstab query. (Crosstab on Query menu.)
Access adds Crosstab and Total rows to the design grid.

3. In a fresh column in the Field row, enter:
TheYear: Year([AttendDate])
In the Crosstab row under this, choose Row Heading.
Accept Group By in the Total row.

4. In the next column in the Field row, enter:
TheMonth: Month([AttendDate])
In the Crosstab row, choose Row Heading again.

5. Drag the Code field into the grid.
In the Crosstab row, choose Row Heading again.

6. Drag the employee name into the grid
Again, choose Row Heading.

7. In the next column in the Field row, enter:
TheDay: Day([AttendDate])
In the CrossTab row, choose Column Heading.

8. Drag the primary key field into the grid.
In the CrossTab row, choose Value.
In the Total row, choose Count.

9. Open the Properties box (View menu.)
Beside the Column Headings property, enter a separated list of the
numbers
from 1 to 31:
1,2,3,4,5,..., 31

10. Save the query.

You can now use this query to create the report.
You will have a bit of formatting to do, e.g. to change the 0/1 into A/P.

Hi all,
I wanted a formatted report (in the table format, with rows and
cols)for
monthly attendance of all the employees.
What I want is - that all the Day(s) and Date(s) should be horizontally
placed where as Name and Code should be vertically placed in the
report.How
do I do this ? something like this---

JUNE---date----1---2---3---4---5----6---7--8---9................
---------day----M---T---W--T---F---S---S--M---T.............
code---name
a1------jill------P----P---P---P---P---A---P---P---P.................
a2------Jack----P----A---P---P---P---P---A---P---P.............
a3...............................................................................and
so on.
where P= Present and A= Absent

 

[Anand Vaidya]

Allen,this is what I want with a little bit of addition to it.
The addition is - I also want weekday(M,T,W...S) displayed below the
date(values 1 to 31) as shown in 1st thread.
Is this possible, because we can not display more than one field as column
heading in the cross tab.
-----------
Anand Vaidya
I am here to know.

I don't think I understand the terms as you are using them.

A crosstab can have only one field as its Column Headings.
The field names (headings over the columns) are derived from the values in
that field.

If you do in fact want the day-of-the-month (values 1 to 31) as your Column
Headings (as per your original example), my previous reply does that.

If you want something different, perhaps someone else can follow what you
asked.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, Date and Day are to be displayed as column headings and name and
code
are to be displayed as row headings ( as shown in figure ).
Can't I generate a report as I require (with date and weekday as column
headings) because the msdn help says -
To create a cross tab query,you must specify-
One or more Row heading option
only one column heading option
and one value option.
I tried to take the Date and Day fields as row headings and Name field as
column heading and "Attendance Status" as Value option in Crosstab
column.It
works fine but the problem with displaying Name as column heading is , you
have to display the Names in vertical alignment(Properties-Other-Vertical
=
Yes) to save the space between two employees details which doesn't give a
good look.
-----------
Anand Vaidya
I am here to know.

Use a crosstab query.

Presumably you have a table with fields like this:
ID primary key
EmployeeID foreign key to Employee.EmployeeID
AttendDate Date/Time

1. Create a query based on this table, and the one that has the employee
names.

2. Change it to a Crosstab query. (Crosstab on Query menu.)
Access adds Crosstab and Total rows to the design grid.

3. In a fresh column in the Field row, enter:
TheYear: Year([AttendDate])
In the Crosstab row under this, choose Row Heading.
Accept Group By in the Total row.

4. In the next column in the Field row, enter:
TheMonth: Month([AttendDate])
In the Crosstab row, choose Row Heading again.

5. Drag the Code field into the grid.
In the Crosstab row, choose Row Heading again.

6. Drag the employee name into the grid
Again, choose Row Heading.

7. In the next column in the Field row, enter:
TheDay: Day([AttendDate])
In the CrossTab row, choose Column Heading.

8. Drag the primary key field into the grid.
In the CrossTab row, choose Value.
In the Total row, choose Count.

9. Open the Properties box (View menu.)
Beside the Column Headings property, enter a separated list of the
numbers
from 1 to 31:
1,2,3,4,5,..., 31

10. Save the query.

You can now use this query to create the report.
You will have a bit of formatting to do, e.g. to change the 0/1 into A/P.

Hi all,
I wanted a formatted report (in the table format, with rows and
cols)for
monthly attendance of all the employees.
What I want is - that all the Day(s) and Date(s) should be horizontally
placed where as Name and Code should be vertically placed in the
report.How
do I do this ? something like this---

JUNE---date----1---2---3---4---5----6---7--8---9................
---------day----M---T---W--T---F---S---S--M---T.............
code---name
a1------jill------P----P---P---P---P---A---P---P---P.................
a2------Jack----P----A---P---P---P---P---A---P---P.............
a3...............................................................................and
so on.
where P= Present and A= Absent

 

[Duane Hookom]

You can use text boxes as your column headings. Consider using a control
source like:
=DateSerial([YourYearField], [YourMonthField], 1)
=DateSerial([YourYearField], [YourMonthField], 2)
=DateSerial([YourYearField], [YourMonthField], 3)
=DateSerial([YourYearField], [YourMonthField], 4)
You can format these dates any way you want using the Format property.

Allen, Date and Day are to be displayed as column headings and name and
code
are to be displayed as row headings ( as shown in figure ).
Can't I generate a report as I require (with date and weekday as column
headings) because the msdn help says -
To create a cross tab query,you must specify-
One or more Row heading option
only one column heading option
and one value option.
I tried to take the Date and Day fields as row headings and Name field as
column heading and "Attendance Status" as Value option in Crosstab
column.It
works fine but the problem with displaying Name as column heading is , you
have to display the Names in vertical alignment(Properties-Other-Vertical
=
Yes) to save the space between two employees details which doesn't give a
good look.
-----------
Anand Vaidya
I am here to know.

Use a crosstab query.

Presumably you have a table with fields like this:
ID primary key
EmployeeID foreign key to Employee.EmployeeID
AttendDate Date/Time

1. Create a query based on this table, and the one that has the employee
names.

2. Change it to a Crosstab query. (Crosstab on Query menu.)
Access adds Crosstab and Total rows to the design grid.

3. In a fresh column in the Field row, enter:
TheYear: Year([AttendDate])
In the Crosstab row under this, choose Row Heading.
Accept Group By in the Total row.

4. In the next column in the Field row, enter:
TheMonth: Month([AttendDate])
In the Crosstab row, choose Row Heading again.

5. Drag the Code field into the grid.
In the Crosstab row, choose Row Heading again.

6. Drag the employee name into the grid
Again, choose Row Heading.

7. In the next column in the Field row, enter:
TheDay: Day([AttendDate])
In the CrossTab row, choose Column Heading.

8. Drag the primary key field into the grid.
In the CrossTab row, choose Value.
In the Total row, choose Count.

9. Open the Properties box (View menu.)
Beside the Column Headings property, enter a separated list of the
numbers
from 1 to 31:
1,2,3,4,5,..., 31

10. Save the query.

You can now use this query to create the report.
You will have a bit of formatting to do, e.g. to change the 0/1 into A/P.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,
I wanted a formatted report (in the table format, with rows and
cols)for
monthly attendance of all the employees.
What I want is - that all the Day(s) and Date(s) should be horizontally
placed where as Name and Code should be vertically placed in the
report.How
do I do this ? something like this---

JUNE---date----1---2---3---4---5----6---7--8---9................
---------day----M---T---W--T---F---S---S--M---T.............
code---name
a1------jill------P----P---P---P---P---A---P---P---P.................
a2------Jack----P----A---P---P---P---P---A---P---P.............
a3...............................................................................and
so on.
where P= Present and A= Absent

 

[Anand Vaidya]

I was told to use cross tab query for this problem,
in cross tab query i took---
Name as row heading
Date as column heading
now I need to take Employee's Attendance status [A's (Absent) and P's
(Present)] in the Value option.How do I take this field because this field is
based on the two queries-

For all the employees "Present" on a perticular date--

SELECT tblemployees.PK_Emp_Id, tblemployees.Name
FROM tblemployees
WHERE (((tblemployees.PK_Emp_Id) In (select fk_emp_id from
tblDaily_Attendance where Attendance_Date=#5/1/2006#)));

For all the employees "Absent" on a perticular date--

SELECT tblemployees.PK_Emp_Id, tblemployees.Name
FROM tblemployees
WHERE (((tblemployees.PK_Emp_Id) Not In (select fk_emp_id from
tblDaily_Attendance where Attendance_Date=#5/1/2006#)));

Could any one suggest me with a brief description,how to take the cross tab
query in my case.


-----------
Anand Vaidya
I am here to know.

You can use text boxes as your column headings. Consider using a control
source like:
=DateSerial([YourYearField], [YourMonthField], 1)
=DateSerial([YourYearField], [YourMonthField], 2)
=DateSerial([YourYearField], [YourMonthField], 3)
=DateSerial([YourYearField], [YourMonthField], 4)
You can format these dates any way you want using the Format property.

Allen, Date and Day are to be displayed as column headings and name and
code
are to be displayed as row headings ( as shown in figure ).
Can't I generate a report as I require (with date and weekday as column
headings) because the msdn help says -
To create a cross tab query,you must specify-
One or more Row heading option
only one column heading option
and one value option.
I tried to take the Date and Day fields as row headings and Name field as
column heading and "Attendance Status" as Value option in Crosstab
column.It
works fine but the problem with displaying Name as column heading is , you
have to display the Names in vertical alignment(Properties-Other-Vertical
=
Yes) to save the space between two employees details which doesn't give a
good look.
-----------
Anand Vaidya
I am here to know.

Use a crosstab query.

Presumably you have a table with fields like this:
ID primary key
EmployeeID foreign key to Employee.EmployeeID
AttendDate Date/Time

1. Create a query based on this table, and the one that has the employee
names.

2. Change it to a Crosstab query. (Crosstab on Query menu.)
Access adds Crosstab and Total rows to the design grid.

3. In a fresh column in the Field row, enter:
TheYear: Year([AttendDate])
In the Crosstab row under this, choose Row Heading.
Accept Group By in the Total row.

4. In the next column in the Field row, enter:
TheMonth: Month([AttendDate])
In the Crosstab row, choose Row Heading again.

5. Drag the Code field into the grid.
In the Crosstab row, choose Row Heading again.

6. Drag the employee name into the grid
Again, choose Row Heading.

7. In the next column in the Field row, enter:
TheDay: Day([AttendDate])
In the CrossTab row, choose Column Heading.

8. Drag the primary key field into the grid.
In the CrossTab row, choose Value.
In the Total row, choose Count.

9. Open the Properties box (View menu.)
Beside the Column Headings property, enter a separated list of the
numbers
from 1 to 31:
1,2,3,4,5,..., 31

10. Save the query.

You can now use this query to create the report.
You will have a bit of formatting to do, e.g. to change the 0/1 into A/P.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,
I wanted a formatted report (in the table format, with rows and
cols)for
monthly attendance of all the employees.
What I want is - that all the Day(s) and Date(s) should be horizontally
placed where as Name and Code should be vertically placed in the
report.How
do I do this ? something like this---

JUNE---date----1---2---3---4---5----6---7--8---9................
---------day----M---T---W--T---F---S---S--M---T.............
code---name
a1------jill------P----P---P---P---P---A---P---P---P.................
a2------Jack----P----A---P---P---P---P---A---P---P.............
a3...............................................................................and
so on.
where P= Present and A= Absent

 

[Anand Vaidya]

Allen , thanks for the step wise explanation for creating a crosstab.
but Allen, I want to create a crosstab query dynamically based on the range
of dates I select in the frmReports form.( which has From Date and To Date)
say, suppose I select 13-7-2006 in From Date and 20-7-2006 in To Date, then
the Attendance report should be generated for these dates only for all the
employees.

-----------
Anand Vaidya
I am here to know.

Use a crosstab query.

Presumably you have a table with fields like this:
ID primary key
EmployeeID foreign key to Employee.EmployeeID
AttendDate Date/Time

1. Create a query based on this table, and the one that has the employee
names.

2. Change it to a Crosstab query. (Crosstab on Query menu.)
Access adds Crosstab and Total rows to the design grid.

3. In a fresh column in the Field row, enter:
TheYear: Year([AttendDate])
In the Crosstab row under this, choose Row Heading.
Accept Group By in the Total row.

4. In the next column in the Field row, enter:
TheMonth: Month([AttendDate])
In the Crosstab row, choose Row Heading again.

5. Drag the Code field into the grid.
In the Crosstab row, choose Row Heading again.

6. Drag the employee name into the grid
Again, choose Row Heading.

7. In the next column in the Field row, enter:
TheDay: Day([AttendDate])
In the CrossTab row, choose Column Heading.

8. Drag the primary key field into the grid.
In the CrossTab row, choose Value.
In the Total row, choose Count.

9. Open the Properties box (View menu.)
Beside the Column Headings property, enter a separated list of the numbers
from 1 to 31:
1,2,3,4,5,..., 31

10. Save the query.

You can now use this query to create the report.
You will have a bit of formatting to do, e.g. to change the 0/1 into A/P.

 

[Allen Browne]

It might be good to get that part working before you try to create the
report, and starting assigning Control Sources and hiding the unused
controls.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen , thanks for the step wise explanation for creating a crosstab.
but Allen, I want to create a crosstab query dynamically based on the
range
of dates I select in the frmReports form.( which has From Date and To
Date)
say, suppose I select 13-7-2006 in From Date and 20-7-2006 in To Date,
then
the Attendance report should be generated for these dates only for all the
employees.

-----------
Anand Vaidya
I am here to know.

Use a crosstab query.

Presumably you have a table with fields like this:
ID primary key
EmployeeID foreign key to Employee.EmployeeID
AttendDate Date/Time

1. Create a query based on this table, and the one that has the employee
names.

2. Change it to a Crosstab query. (Crosstab on Query menu.)
Access adds Crosstab and Total rows to the design grid.

3. In a fresh column in the Field row, enter:
TheYear: Year([AttendDate])
In the Crosstab row under this, choose Row Heading.
Accept Group By in the Total row.

4. In the next column in the Field row, enter:
TheMonth: Month([AttendDate])
In the Crosstab row, choose Row Heading again.

5. Drag the Code field into the grid.
In the Crosstab row, choose Row Heading again.

6. Drag the employee name into the grid
Again, choose Row Heading.

7. In the next column in the Field row, enter:
TheDay: Day([AttendDate])
In the CrossTab row, choose Column Heading.

8. Drag the primary key field into the grid.
In the CrossTab row, choose Value.
In the Total row, choose Count.

9. Open the Properties box (View menu.)
Beside the Column Headings property, enter a separated list of the
numbers
from 1 to 31:
1,2,3,4,5,..., 31

10. Save the query.

You can now use this query to create the report.
You will have a bit of formatting to do, e.g. to change the 0/1 into A/P.

Hi all,
I wanted a formatted report (in the table format, with rows and
cols)for
monthly attendance of all the employees.
What I want is - that all the Day(s) and Date(s) should be horizontally
placed where as Name and Code should be vertically placed in the
report.How
do I do this ? something like this---

JUNE---date----1---2---3---4---5----6---7--8---9................
---------day----M---T---W--T---F---S---S--M---T.............
code---name
a1------jill------P----P---P---P---P---A---P---P---P.................
a2------Jack----P----A---P---P---P---P---A---P---P.............
a3...............................................................................and
so on.
where P= Present and A= Absent