Creating a report horizontally, sort of...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I just got some wonderful help in another problem from two people, and I
wanted to say thank you! ^_^

Of course, though, I do have another issue on something I don't even know if
Access can do.

Anyway, as before, I'm working on a database to keep track of employee
vacation time in our warehouse. I have just about everything completed, but
I am stuck.

Is it possible to create a report that would list calendar days 1-31 in one
line across, and underneath, have a listing of all employees, and an X or
their hours taken that day listed? Kind of like this:

Jan: 1 2 3 4 ... 29 30 31
Bob: - - X X ...
Tim: X - - - ...

Sitting here working on everything else, I'm not even sure if that's possible.

Thank you for your time!
Stephanie
 
You can do that with a crosstab query.
The "how" depends on how you store the data.

Can we assume you have a table something like this:
WorkID primary key
EmployeeID relates to Employee.EmployeeID
ShiftDate Date/Time
HoursWorked Number

1. Create a query using this table and your employee table.
There should be a line joining the 2 tables on the EmployeeID fields.

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

3. In the Field row, type:
TheYear: Year([ShiftDate])
In the Crosstab row, choose Row Heading.

4. In the next column in the field row, type:
TheMonth: Month([ShiftDate])
In the Crosstab row, choose Row Heading.

5. In the next column in the field row, type:
TheDay: Day([ShiftDate])
In the Crosstab row, choose Column Heading

6. Add the Employee name field to the grid (from the Employee table.)
In the Crosstab row, choose Row Heading.

7. Add the HoursWorked field to the grid.
In the Crosstab row, choose Value.
In the Total row, choose Sum.

8. Open the Properties box (View menu.)
In the Column Headings property, enter a list of numbers from 1 to 31:
1,2,3,...
(If you don't see the property, you are looking at the properties of a field
instead of the properties of the query.)

The query now shows:
- a column for the year (step 3), month (step 4), and employee (step 6);
- a column for each day from 1 to 31 (steps 6 and 8);
- the sum of hours at the intersection point.

You can now build a report from this query.
 
Thank you Allen! I will give it a try a little later on today and see how it
works out.

Your guess on how my table is set up is very close, enough that I should be
able to adapt it pretty easily.

Thank you again!
Stephanie

Allen Browne said:
You can do that with a crosstab query.
The "how" depends on how you store the data.

Can we assume you have a table something like this:
WorkID primary key
EmployeeID relates to Employee.EmployeeID
ShiftDate Date/Time
HoursWorked Number

1. Create a query using this table and your employee table.
There should be a line joining the 2 tables on the EmployeeID fields.

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

3. In the Field row, type:
TheYear: Year([ShiftDate])
In the Crosstab row, choose Row Heading.

4. In the next column in the field row, type:
TheMonth: Month([ShiftDate])
In the Crosstab row, choose Row Heading.

5. In the next column in the field row, type:
TheDay: Day([ShiftDate])
In the Crosstab row, choose Column Heading

6. Add the Employee name field to the grid (from the Employee table.)
In the Crosstab row, choose Row Heading.

7. Add the HoursWorked field to the grid.
In the Crosstab row, choose Value.
In the Total row, choose Sum.

8. Open the Properties box (View menu.)
In the Column Headings property, enter a list of numbers from 1 to 31:
1,2,3,...
(If you don't see the property, you are looking at the properties of a field
instead of the properties of the query.)

The query now shows:
- a column for the year (step 3), month (step 4), and employee (step 6);
- a column for each day from 1 to 31 (steps 6 and 8);
- the sum of hours at the intersection point.

You can now build a report from this query.

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

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

Luna Saisho said:
I just got some wonderful help in another problem from two people, and I
wanted to say thank you! ^_^

Of course, though, I do have another issue on something I don't even know
if
Access can do.

Anyway, as before, I'm working on a database to keep track of employee
vacation time in our warehouse. I have just about everything completed,
but
I am stuck.

Is it possible to create a report that would list calendar days 1-31 in
one
line across, and underneath, have a listing of all employees, and an X or
their hours taken that day listed? Kind of like this:

Jan: 1 2 3 4 ... 29 30 31
Bob: - - X X ...
Tim: X - - - ...

Sitting here working on everything else, I'm not even sure if that's
possible.

Thank you for your time!
Stephanie
Click to expand...
Click to expand...
 
Hi Allen,

I wasn't able to log in on Friday to let you know how it went, as our entire
plant lost Internet for a good majority of the night.

I do, however, need to thank you! I tried what you told me, and it works
like a charm! I was unsure what to do with it at first, but when I went and
set the query as the data source, and saw how the fields came up, it was
obvious. It re-creates their Excel based ones in reports, but now they can
get the info they need, and modify it, a lot easier.

The only odd thing is it changed "TheDay: Day([ShiftDate])" to "Expr1:
Day([Dates])" but as I need to work on a Monthly basis, it hasn't been an
issue.

If Microsoft has done anything in this world right, it was to create these
newsgroups! My thanks go out to you, Allen, and the many people that have
helped me in the past month. THANK YOU! ^_^

Allen Browne said:
You can do that with a crosstab query.
The "how" depends on how you store the data.

Can we assume you have a table something like this:
WorkID primary key
EmployeeID relates to Employee.EmployeeID
ShiftDate Date/Time
HoursWorked Number

1. Create a query using this table and your employee table.
There should be a line joining the 2 tables on the EmployeeID fields.

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

3. In the Field row, type:
TheYear: Year([ShiftDate])
In the Crosstab row, choose Row Heading.

4. In the next column in the field row, type:
TheMonth: Month([ShiftDate])
In the Crosstab row, choose Row Heading.

5. In the next column in the field row, type:
TheDay: Day([ShiftDate])
In the Crosstab row, choose Column Heading

6. Add the Employee name field to the grid (from the Employee table.)
In the Crosstab row, choose Row Heading.

7. Add the HoursWorked field to the grid.
In the Crosstab row, choose Value.
In the Total row, choose Sum.

8. Open the Properties box (View menu.)
In the Column Headings property, enter a list of numbers from 1 to 31:
1,2,3,...
(If you don't see the property, you are looking at the properties of a field
instead of the properties of the query.)

The query now shows:
- a column for the year (step 3), month (step 4), and employee (step 6);
- a column for each day from 1 to 31 (steps 6 and 8);
- the sum of hours at the intersection point.

You can now build a report from this query.

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

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

Luna Saisho said:
I just got some wonderful help in another problem from two people, and I
wanted to say thank you! ^_^

Of course, though, I do have another issue on something I don't even know
if
Access can do.

Anyway, as before, I'm working on a database to keep track of employee
vacation time in our warehouse. I have just about everything completed,
but
I am stuck.

Is it possible to create a report that would list calendar days 1-31 in
one
line across, and underneath, have a listing of all employees, and an X or
their hours taken that day listed? Kind of like this:

Jan: 1 2 3 4 ... 29 30 31
Bob: - - X X ...
Tim: X - - - ...

Sitting here working on everything else, I'm not even sure if that's
possible.

Thank you for your time!
Stephanie
Click to expand...
Click to expand...
 
The alias (Expr1 or TheDay) does not matter.
If the field is named Dates rather than ShiftDate, the expresion is correct.

That's a beautiful expression of appreciation, Stephanie.

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

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

Luna Saisho said:
Hi Allen,

I wasn't able to log in on Friday to let you know how it went, as our
entire
plant lost Internet for a good majority of the night.

I do, however, need to thank you! I tried what you told me, and it works
like a charm! I was unsure what to do with it at first, but when I went
and
set the query as the data source, and saw how the fields came up, it was
obvious. It re-creates their Excel based ones in reports, but now they
can
get the info they need, and modify it, a lot easier.

The only odd thing is it changed "TheDay: Day([ShiftDate])" to "Expr1:
Day([Dates])" but as I need to work on a Monthly basis, it hasn't been an
issue.

If Microsoft has done anything in this world right, it was to create these
newsgroups! My thanks go out to you, Allen, and the many people that have
helped me in the past month. THANK YOU! ^_^

Allen Browne said:
You can do that with a crosstab query.
The "how" depends on how you store the data.

Can we assume you have a table something like this:
WorkID primary key
EmployeeID relates to Employee.EmployeeID
ShiftDate Date/Time
HoursWorked Number

1. Create a query using this table and your employee table.
There should be a line joining the 2 tables on the EmployeeID fields.

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

3. In the Field row, type:
TheYear: Year([ShiftDate])
In the Crosstab row, choose Row Heading.

4. In the next column in the field row, type:
TheMonth: Month([ShiftDate])
In the Crosstab row, choose Row Heading.

5. In the next column in the field row, type:
TheDay: Day([ShiftDate])
In the Crosstab row, choose Column Heading

6. Add the Employee name field to the grid (from the Employee table.)
In the Crosstab row, choose Row Heading.

7. Add the HoursWorked field to the grid.
In the Crosstab row, choose Value.
In the Total row, choose Sum.

8. Open the Properties box (View menu.)
In the Column Headings property, enter a list of numbers from 1 to 31:
1,2,3,...
(If you don't see the property, you are looking at the properties of a
field
instead of the properties of the query.)

The query now shows:
- a column for the year (step 3), month (step 4), and employee (step 6);
- a column for each day from 1 to 31 (steps 6 and 8);
- the sum of hours at the intersection point.

You can now build a report from this query.

Luna Saisho said:
I just got some wonderful help in another problem from two people, and I
wanted to say thank you! ^_^

Of course, though, I do have another issue on something I don't even
know
if
Access can do.

Anyway, as before, I'm working on a database to keep track of employee
vacation time in our warehouse. I have just about everything
completed,
but
I am stuck.

Is it possible to create a report that would list calendar days 1-31 in
one
line across, and underneath, have a listing of all employees, and an X
or
their hours taken that day listed? Kind of like this:

Jan: 1 2 3 4 ... 29 30 31
Bob: - - X X ...
Tim: X - - - ...

Sitting here working on everything else, I'm not even sure if that's
possible.

Thank you for your time!
Stephanie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top