By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,594 Members | 3,552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,594 IT Pros & Developers. It's quick & easy.

Format a report with rows and columns

P: n/a
ACCESS 2k

I need to design a report based on a rota system for staff at various
shops. The data is effectively stored in a single table, along the
lines of:

Initials (e.g. BH, FG, RM etc.)
Day (e.g. Monday, Tuesday etc.)
Shop (e.g. Shop1, Shop2 etc.)
StartTime (e.g. 8, 13 etc.)
EndTime (e.g. 12, 17 etc.)

The format of the report needs to be something like:

| MONDAY | TUEDAY | WEDNESDAY | etc.
-----------------------------------------------
Shop1 | 8-12 BH | 8-12 FG | 8-12 RM |
| 13-17 RT | 13-18 BH | 13-17 TY |
-----------------------------------------------
Shop2 | 8-12 TU | 8-13 NN | 8-12 MK |

etc.

Any ideas?

Thanks in advance

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi Edward,

Looks like a nice simple thing, but when I tried I found it's trickier than
it looks. But it can be done, with a series of subreports, and the only
thing my solution won't cope with is multiple entries for the same person at
the same location on the same day.

Basically, what I did was a query to concatenate StartTime, EndTime and
Initials into a single field (which I named "ManAtWork"), which will be what
appears in the grid of the report (you might want to concatenate a leading
zero to StartTime to get sort order right). Then create a Crosstab query,
with Day as a column heading, Shop and Initials as row headings, and
FirstOfManAtWork as the value. Add an IN clause to the PIVOT section of the
crosstab query in SQL view, to cope with days which do not yet have an entry
in the data (ie ... PIVOT qryWork.Day IN
('Monday','Tuesday','Wednesday','Thursday','Friday ','Saturday','Sunday').
This basically gives what you want, but it will have lots of null fields,
and you can't just use it as the datasource for your report.

To get rid of these null fields, set up a separate query for each day, using
just the Shop and xxxDay fields from the crosstab query, and using "Is Not
Null" as the criterion for the day field. This set of queries will be used
as the datasource for a corresponding set of subreports, each containing
textboxes for Shop and Day. Make the Shop textboxes not visible. The
report needs to be based on a datasource which contains Shop (as Distinct
values, to prevent duplicates - the original data table will do, or you
could set up a separate query for this); each row of the report contains a
textbox for Shop, and the subreports for each day, linked via the Shop
field. Make the subreports large enough for one row of data, and set the
CanGrow property to Yes.

To draw nice borders around the cells, use a Variable Height box border
routine, such as that in Woody's Access Watch V4 No 17
(http://www.woodyswatch.com/access/archtemplate.asp?4-17) I'm not sure if
this particular one works with subreports, but there is one that does -
unfortunately it's on my work computer, so I can't tell you exactly where I
found it right now.

Hope this helps ...

Rob

"Edward" <te********@hotmail.com> wrote in message
news:25**************************@posting.google.c om...
ACCESS 2k

I need to design a report based on a rota system for staff at various
shops. The data is effectively stored in a single table, along the
lines of:

Initials (e.g. BH, FG, RM etc.)
Day (e.g. Monday, Tuesday etc.)
Shop (e.g. Shop1, Shop2 etc.)
StartTime (e.g. 8, 13 etc.)
EndTime (e.g. 12, 17 etc.)

The format of the report needs to be something like:

| MONDAY | TUEDAY | WEDNESDAY | etc.
-----------------------------------------------
Shop1 | 8-12 BH | 8-12 FG | 8-12 RM |
| 13-17 RT | 13-18 BH | 13-17 TY |
-----------------------------------------------
Shop2 | 8-12 TU | 8-13 NN | 8-12 MK |

etc.

Any ideas?

Thanks in advance

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

Nov 13 '05 #2

P: n/a
And a little more:

To cope with multiple entries for the same person at the same location on
the same day, simply add StartTime as another row heading in the crosstab
query.

But it can also be simpler, in that the crosstab is really only providing a
means of separating each day. The set of queries for the daily subreports
can be run on the table, doing both the concatenation of the fields and
using Day as the criterion; this eliminates two layers of queries (only a
single one in each).

Rob

"Rob Parker" <robpparker at optusnet dot com dot au> wrote in message
news:40***********************@news.optusnet.com.a u...
Hi Edward,

Looks like a nice simple thing, but when I tried I found it's trickier than it looks. But it can be done, with a series of subreports, and the only
thing my solution won't cope with is multiple entries for the same person at the same location on the same day.

Basically, what I did was a query to concatenate StartTime, EndTime and
Initials into a single field (which I named "ManAtWork"), which will be what appears in the grid of the report (you might want to concatenate a leading
zero to StartTime to get sort order right). Then create a Crosstab query,
with Day as a column heading, Shop and Initials as row headings, and
FirstOfManAtWork as the value. Add an IN clause to the PIVOT section of the crosstab query in SQL view, to cope with days which do not yet have an entry in the data (ie ... PIVOT qryWork.Day IN
('Monday','Tuesday','Wednesday','Thursday','Friday ','Saturday','Sunday').
This basically gives what you want, but it will have lots of null fields,
and you can't just use it as the datasource for your report.

To get rid of these null fields, set up a separate query for each day, using just the Shop and xxxDay fields from the crosstab query, and using "Is Not
Null" as the criterion for the day field. This set of queries will be used as the datasource for a corresponding set of subreports, each containing
textboxes for Shop and Day. Make the Shop textboxes not visible. The
report needs to be based on a datasource which contains Shop (as Distinct
values, to prevent duplicates - the original data table will do, or you
could set up a separate query for this); each row of the report contains a
textbox for Shop, and the subreports for each day, linked via the Shop
field. Make the subreports large enough for one row of data, and set the
CanGrow property to Yes.

To draw nice borders around the cells, use a Variable Height box border
routine, such as that in Woody's Access Watch V4 No 17
(http://www.woodyswatch.com/access/archtemplate.asp?4-17) I'm not sure if this particular one works with subreports, but there is one that does -
unfortunately it's on my work computer, so I can't tell you exactly where I found it right now.

Hope this helps ...

Rob

"Edward" <te********@hotmail.com> wrote in message
news:25**************************@posting.google.c om...
ACCESS 2k

I need to design a report based on a rota system for staff at various
shops. The data is effectively stored in a single table, along the
lines of:

Initials (e.g. BH, FG, RM etc.)
Day (e.g. Monday, Tuesday etc.)
Shop (e.g. Shop1, Shop2 etc.)
StartTime (e.g. 8, 13 etc.)
EndTime (e.g. 12, 17 etc.)

The format of the report needs to be something like:

| MONDAY | TUEDAY | WEDNESDAY | etc.
-----------------------------------------------
Shop1 | 8-12 BH | 8-12 FG | 8-12 RM |
| 13-17 RT | 13-18 BH | 13-17 TY |
-----------------------------------------------
Shop2 | 8-12 TU | 8-13 NN | 8-12 MK |

etc.

Any ideas?

Thanks in advance

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk


Nov 13 '05 #3

P: n/a
"Rob Parker" <robpparker at optusnet dot com dot au> wrote in message news:<40***********************@news.optusnet.com. au>...
And a little more:

[big snip]

I've been away for a couple of weeks, but have just returned to your
very useful and considered reply, for which many belated thanks.

Edward
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.