Pivot table view detailed row height

[Rick]

Does anyone know the code to set the height of the rows in the detailed
section of a pivot table form?

 

[Rob Oldfield]

I've had a quick play and a quick Google, but haven't got anywhere. The
problem is referencing an Excel OLE object in a form in a way that exposes
Excel properties.

Do you actually need the functionality of Excel pivot tables? If you don't
then I could come up with a solution.

 

[Rick]

Hi Rob:

I am not really sure if I require the functionality of Excel pivot tables in
my application. I have never actually used Pivot Tables in Excel to any
extent and am not quite sure if Access is generating the Pivot Table view of
my data in Access or creating a link to Excel somehow. Perhaps if I explain
the purpose. I have a form in Pivot Table view based on a query which the
result looks something like:

For the Week Beginning V
09/06/2004
09/12/2004
+-
+-
StaffMemberV HrsV Job AssignedV InV HrsV Job AssignedV
InV
Bill + 10 Pro Company 05/12/04 13 XZY
Company 02/24/04
- 13 Pictures Ltd. 07/18/04 22 Old
Barns Co. 08/11/04
17 Sams Records 09/01/04
[totals line] 40 35
Jill + 7 Liams Ltd. 08/27/04 10 Pork
House Co. 08/22/04
- 29 CarsRUs 08/29/04 17 Boxes
Inc. 07/31/04
2 All About Us 09/02/04 25
Willow Bends 08/20/04
[totals line] 38 52
Grand Total + 78 87
-

Where the "V" following a field represents the drop down selector.

Idea is to generate a Excel type spreadsheet (using Pivot Table view) to
show the staff schedule each week; by each staff member; with total hours
assigned to work and with grand totals of all hours assigned to staff for the
week.

Problem is a staff member may be assigned over 20 small jobs each week.
When the Pivot table form opens it only shows 12 lines of detail. True; the
other 8 lines can be viewed on screen by using a verticle scroll bar but this
doesn't address the issue of printing a schedule showing all lines.

Purpose of reducing the height of the detailed lines is to attempt to show
as many lines as possible on the printout.

The user needs to be able to click on the drop down arrow for the field "For
the Week Beginning" and select the current week and the subsequent 3 week
period for printing. Actually, this is the only drop down the user has to
access to get the job done. All other drop downs are provided by the Pivot
Table view but unnessary.

Hope this presents the issue in an understandable way. I could provide you
with a snapshot of the actual schedule if this is any help.

Thanks for your interest in this problem.

Rick

 

[Rob Oldfield]

Hmmm. From your description I don't think it looks like you actually need
the Excel functionality (though I may still be wrong of course).

(That functionality is, by the way, that you can update/reorganise the pivot
table on the fly. Suppose, in a personnel situation, that you had total
salaries broken down by Location, Department and Sex. Location and
Department as row headings down the left, and Sexes across the top. In
Excel, you can just drag the Department group from being a row heading to
being a column heading - same information, just organised differently.)

So I would definitely see if you can get the same result by purely using
Access.... this would be done by using a group query to calculate your
figures (or an extended type of group query called a crosstab query). In
addition, I should point out that forms are really designed just to be
displayed on screen, not for printing. Reports are for printing - and you
can get total figures on those either by just basing the report on the group
query, or in a slightly different way by basing the report on the base data
and then totalling by sections and hiding detail rows.

Or... you could use the pivot table to display the info on screen, and build
a report to print it as you want.

So... does the information on screen look OK at the moment? Is the problem
just with getting a printable version? If that's the case then you just
need a report.

Is that any use?

Hi Rob:

I am not really sure if I require the functionality of Excel pivot tables in
my application. I have never actually used Pivot Tables in Excel to any
extent and am not quite sure if Access is generating the Pivot Table view of
my data in Access or creating a link to Excel somehow. Perhaps if I explain
the purpose. I have a form in Pivot Table view based on a query which the
result looks something like:

For the Week Beginning V
09/06/2004
09/12/2004
+-
+-
StaffMemberV HrsV Job AssignedV InV HrsV Job AssignedV
InV
Bill + 10 Pro Company 05/12/04 13 XZY
Company 02/24/04
- 13 Pictures Ltd. 07/18/04 22 Old
Barns Co. 08/11/04
17 Sams Records 09/01/04
[totals line] 40 35
Jill + 7 Liams Ltd. 08/27/04 10 Pork
House Co. 08/22/04
- 29 CarsRUs 08/29/04 17 Boxes
Inc. 07/31/04
2 All About Us 09/02/04 25
Willow Bends 08/20/04
[totals line] 38 52
Grand Total + 78 87
-

Where the "V" following a field represents the drop down selector.

Idea is to generate a Excel type spreadsheet (using Pivot Table view) to
show the staff schedule each week; by each staff member; with total hours
assigned to work and with grand totals of all hours assigned to staff for the
week.

Problem is a staff member may be assigned over 20 small jobs each week.
When the Pivot table form opens it only shows 12 lines of detail. True; the
other 8 lines can be viewed on screen by using a verticle scroll bar but this
doesn't address the issue of printing a schedule showing all lines.

Purpose of reducing the height of the detailed lines is to attempt to show
as many lines as possible on the printout.

The user needs to be able to click on the drop down arrow for the field "For
the Week Beginning" and select the current week and the subsequent 3 week
period for printing. Actually, this is the only drop down the user has to
access to get the job done. All other drop downs are provided by the Pivot
Table view but unnessary.

Hope this presents the issue in an understandable way. I could provide you
with a snapshot of the actual schedule if this is any help.

Thanks for your interest in this problem.

Rick

I've had a quick play and a quick Google, but haven't got anywhere. The
problem is referencing an Excel OLE object in a form in a way that exposes
Excel properties.

Do you actually need the functionality of Excel pivot tables? If you don't
then I could come up with a solution.

 

[Rick]

Rob:

The screen display of the Pivot Table is OK, it is just the report that is
giving me grief. You idea of having a separate report is good but I have
never been able to figure out how to generate such a report in Access so that
it looks like a spreadsheet with specific sections running both verticially
and horizontially.

For example I need to show grouped sections for each employee running
vertically. That's the easy part. However; at the same time I need to show
several columns running horizontally within each staff section. For example:
WeekBegin WeekBegin
WeekBegin
Staff 01/02/05 01/09/05
01/16/05
Jack XYZ Co Peter's U'Pick
AllAboutFaces
Big Stop Rest
NS Paving Co.
Harry's Wholesale CarsRUs
Beehive Honey
Total Jobs 3 2
3
Ann Sewing Shop Co. Winnie Restaurant
BookStore Ltd.
Mary's Candies
Ocean Contract
Sam's Records
ATV Vehicles Ltd
Wallace Optical
Bells & Chimes
Porters Auto
Repair
Total Jobs 1 5
4
Grand Totals 4 7
7

A pivot table appears to work fine with the exception the number of lines
displayed in the detailed section is restricted to about 12 lines which is
not always enough.

Rick

Hmmm. From your description I don't think it looks like you actually need
the Excel functionality (though I may still be wrong of course).

(That functionality is, by the way, that you can update/reorganise the pivot
table on the fly. Suppose, in a personnel situation, that you had total
salaries broken down by Location, Department and Sex. Location and
Department as row headings down the left, and Sexes across the top. In
Excel, you can just drag the Department group from being a row heading to
being a column heading - same information, just organised differently.)

So I would definitely see if you can get the same result by purely using
Access.... this would be done by using a group query to calculate your
figures (or an extended type of group query called a crosstab query). In
addition, I should point out that forms are really designed just to be
displayed on screen, not for printing. Reports are for printing - and you
can get total figures on those either by just basing the report on the group
query, or in a slightly different way by basing the report on the base data
and then totalling by sections and hiding detail rows.

Or... you could use the pivot table to display the info on screen, and build
a report to print it as you want.

So... does the information on screen look OK at the moment? Is the problem
just with getting a printable version? If that's the case then you just
need a report.

Is that any use?

Hi Rob:

I am not really sure if I require the functionality of Excel pivot tables in
my application. I have never actually used Pivot Tables in Excel to any
extent and am not quite sure if Access is generating the Pivot Table view of
my data in Access or creating a link to Excel somehow. Perhaps if I explain
the purpose. I have a form in Pivot Table view based on a query which the
result looks something like:

For the Week Beginning V
09/06/2004
09/12/2004
+-
+-
StaffMemberV HrsV Job AssignedV InV HrsV Job AssignedV
InV
Bill + 10 Pro Company 05/12/04 13 XZY
Company 02/24/04
- 13 Pictures Ltd. 07/18/04 22 Old
Barns Co. 08/11/04
17 Sams Records 09/01/04
[totals line] 40 35
Jill + 7 Liams Ltd. 08/27/04 10 Pork
House Co. 08/22/04
- 29 CarsRUs 08/29/04 17 Boxes
Inc. 07/31/04
2 All About Us 09/02/04 25
Willow Bends 08/20/04
[totals line] 38 52
Grand Total + 78 87
-

Where the "V" following a field represents the drop down selector.

Idea is to generate a Excel type spreadsheet (using Pivot Table view) to
show the staff schedule each week; by each staff member; with total hours
assigned to work and with grand totals of all hours assigned to staff for the
week.

Problem is a staff member may be assigned over 20 small jobs each week.
When the Pivot table form opens it only shows 12 lines of detail. True; the
other 8 lines can be viewed on screen by using a verticle scroll bar but this
doesn't address the issue of printing a schedule showing all lines.

Purpose of reducing the height of the detailed lines is to attempt to show
as many lines as possible on the printout.

The user needs to be able to click on the drop down arrow for the field "For
the Week Beginning" and select the current week and the subsequent 3 week
period for printing. Actually, this is the only drop down the user has to
access to get the job done. All other drop downs are provided by the Pivot
Table view but unnessary.

Hope this presents the issue in an understandable way. I could provide you
with a snapshot of the actual schedule if this is any help.

Thanks for your interest in this problem.

Rick

I've had a quick play and a quick Google, but haven't got anywhere. The
problem is referencing an Excel OLE object in a form in a way that exposes
Excel properties.

Do you actually need the functionality of Excel pivot tables? If you don't
then I could come up with a solution.

Does anyone know the code to set the height of the rows in the detailed
section of a pivot table form?

 

[Rob Oldfield]

Hmmm. That's not an easy one. But it can definitely be done solely in
Access. One question though - what's the structure of the table that
contains the base data that determines what comes out here?

Rob:

The screen display of the Pivot Table is OK, it is just the report that is
giving me grief. You idea of having a separate report is good but I have
never been able to figure out how to generate such a report in Access so that
it looks like a spreadsheet with specific sections running both verticially
and horizontially.

For example I need to show grouped sections for each employee running
vertically. That's the easy part. However; at the same time I need to show
several columns running horizontally within each staff section. For example:
WeekBegin WeekBegin
WeekBegin
Staff 01/02/05 01/09/05
01/16/05
Jack XYZ Co Peter's U'Pick
AllAboutFaces
Big Stop Rest
NS Paving Co.
Harry's Wholesale CarsRUs
Beehive Honey
Total Jobs 3 2
3
Ann Sewing Shop Co. Winnie Restaurant
BookStore Ltd.
Mary's Candies
Ocean Contract
Sam's Records
ATV Vehicles Ltd
Wallace Optical
Bells & Chimes
Porters Auto
Repair
Total Jobs 1 5
4
Grand Totals 4 7
7

A pivot table appears to work fine with the exception the number of lines
displayed in the detailed section is restricted to about 12 lines which is
not always enough.

Rick

Hmmm. From your description I don't think it looks like you actually need
the Excel functionality (though I may still be wrong of course).

(That functionality is, by the way, that you can update/reorganise the pivot
table on the fly. Suppose, in a personnel situation, that you had total
salaries broken down by Location, Department and Sex. Location and
Department as row headings down the left, and Sexes across the top. In
Excel, you can just drag the Department group from being a row heading to
being a column heading - same information, just organised differently.)

So I would definitely see if you can get the same result by purely using
Access.... this would be done by using a group query to calculate your
figures (or an extended type of group query called a crosstab query). In
addition, I should point out that forms are really designed just to be
displayed on screen, not for printing. Reports are for printing - and you
can get total figures on those either by just basing the report on the group
query, or in a slightly different way by basing the report on the base data
and then totalling by sections and hiding detail rows.

Or... you could use the pivot table to display the info on screen, and build
a report to print it as you want.

So... does the information on screen look OK at the moment? Is the problem
just with getting a printable version? If that's the case then you just
need a report.

Is that any use?

Hi Rob:

I am not really sure if I require the functionality of Excel pivot

tables
in

my application. I have never actually used Pivot Tables in Excel to any
extent and am not quite sure if Access is generating the Pivot Table

view
of

my data in Access or creating a link to Excel somehow. Perhaps if I explain
the purpose. I have a form in Pivot Table view based on a query which the
result looks something like:

For the Week Beginning V
09/06/2004
09/12/2004
+-
+-
StaffMemberV HrsV Job AssignedV InV HrsV Job AssignedV
InV
Bill + 10 Pro Company 05/12/04 13 XZY
Company 02/24/04
- 13 Pictures Ltd. 07/18/04 22 Old
Barns Co. 08/11/04
17 Sams Records 09/01/04
[totals line] 40 35
Jill + 7 Liams Ltd. 08/27/04 10 Pork
House Co. 08/22/04
- 29 CarsRUs 08/29/04 17 Boxes
Inc. 07/31/04
2 All About Us 09/02/04 25
Willow Bends 08/20/04
[totals line] 38 52
Grand Total + 78 87
-

Where the "V" following a field represents the drop down selector.

Idea is to generate a Excel type spreadsheet (using Pivot Table view) to
show the staff schedule each week; by each staff member; with total hours
assigned to work and with grand totals of all hours assigned to staff

for
the

week.

Problem is a staff member may be assigned over 20 small jobs each week.
When the Pivot table form opens it only shows 12 lines of detail.

True;
the

other 8 lines can be viewed on screen by using a verticle scroll bar

but
this

doesn't address the issue of printing a schedule showing all lines.

Purpose of reducing the height of the detailed lines is to attempt to show
as many lines as possible on the printout.

The user needs to be able to click on the drop down arrow for the

field
"For

the Week Beginning" and select the current week and the subsequent 3 week
period for printing. Actually, this is the only drop down the user has to
access to get the job done. All other drop downs are provided by the Pivot
Table view but unnessary.

Hope this presents the issue in an understandable way. I could

provide
you

with a snapshot of the actual schedule if this is any help.

Thanks for your interest in this problem.

Rick


:

I've had a quick play and a quick Google, but haven't got anywhere. The
problem is referencing an Excel OLE object in a form in a way that exposes
Excel properties.

Do you actually need the functionality of Excel pivot tables? If

you
don't

then I could come up with a solution.

Does anyone know the code to set the height of the rows in the detailed
section of a pivot table form?

 

[Rick]

The structure of the query that generates the pivot table is as follows:

Staff table
StaffID PK autonumber
StaffGivenName text
StaffSurname text

Weeks table
WeeID PK autonumber
BeginWeek date format (ie "01/10/05")
EndWeek date format (ie "01/16/05")

JobsInProgressAssigned table

JIPAssignID PK autonumber
JIPStaffID lookup to Staff table
JIPWeekID lookup to Weeks table
JIPAssignment text (name of customer have job done)
JIPHoursBudget long integer (hours estimated for job to be completed)
JIPHoursLeft long integer (hours currently remaining to complete job)

Hmmm. That's not an easy one. But it can definitely be done solely in
Access. One question though - what's the structure of the table that
contains the base data that determines what comes out here?

Rob:

The screen display of the Pivot Table is OK, it is just the report that is
giving me grief. You idea of having a separate report is good but I have
never been able to figure out how to generate such a report in Access so that
it looks like a spreadsheet with specific sections running both verticially
and horizontially.

For example I need to show grouped sections for each employee running
vertically. That's the easy part. However; at the same time I need to show
several columns running horizontally within each staff section. For example:
WeekBegin WeekBegin
WeekBegin
Staff 01/02/05 01/09/05
01/16/05
Jack XYZ Co Peter's U'Pick
AllAboutFaces
Big Stop Rest
NS Paving Co.
Harry's Wholesale CarsRUs
Beehive Honey
Total Jobs 3 2
3
Ann Sewing Shop Co. Winnie Restaurant
BookStore Ltd.
Mary's Candies
Ocean Contract
Sam's Records
ATV Vehicles Ltd
Wallace Optical
Bells & Chimes
Porters Auto
Repair
Total Jobs 1 5
4
Grand Totals 4 7
7

A pivot table appears to work fine with the exception the number of lines
displayed in the detailed section is restricted to about 12 lines which is
not always enough.

Rick

Hmmm. From your description I don't think it looks like you actually need
the Excel functionality (though I may still be wrong of course).

(That functionality is, by the way, that you can update/reorganise the pivot
table on the fly. Suppose, in a personnel situation, that you had total
salaries broken down by Location, Department and Sex. Location and
Department as row headings down the left, and Sexes across the top. In
Excel, you can just drag the Department group from being a row heading to
being a column heading - same information, just organised differently.)

So I would definitely see if you can get the same result by purely using
Access.... this would be done by using a group query to calculate your
figures (or an extended type of group query called a crosstab query). In
addition, I should point out that forms are really designed just to be
displayed on screen, not for printing. Reports are for printing - and you
can get total figures on those either by just basing the report on the group
query, or in a slightly different way by basing the report on the base data
and then totalling by sections and hiding detail rows.

Or... you could use the pivot table to display the info on screen, and build
a report to print it as you want.

So... does the information on screen look OK at the moment? Is the problem
just with getting a printable version? If that's the case then you just
need a report.

Is that any use?



Hi Rob:

I am not really sure if I require the functionality of Excel pivot tables
in
my application. I have never actually used Pivot Tables in Excel to any
extent and am not quite sure if Access is generating the Pivot Table view
of
my data in Access or creating a link to Excel somehow. Perhaps if I
explain
the purpose. I have a form in Pivot Table view based on a query which the
result looks something like:

For the Week Beginning V
09/06/2004
09/12/2004
+-
+-
StaffMemberV HrsV Job AssignedV InV HrsV Job
AssignedV
InV
Bill + 10 Pro Company 05/12/04 13 XZY
Company 02/24/04
- 13 Pictures Ltd. 07/18/04 22
Old
Barns Co. 08/11/04
17 Sams Records 09/01/04
[totals line] 40
35
Jill + 7 Liams Ltd. 08/27/04 10
Pork
House Co. 08/22/04
- 29 CarsRUs 08/29/04 17
Boxes
Inc. 07/31/04
2 All About Us 09/02/04 25
Willow Bends 08/20/04
[totals line] 38
52
Grand Total + 78 87
-

Where the "V" following a field represents the drop down selector.

Idea is to generate a Excel type spreadsheet (using Pivot Table view) to
show the staff schedule each week; by each staff member; with total hours
assigned to work and with grand totals of all hours assigned to staff for
the
week.

Problem is a staff member may be assigned over 20 small jobs each week.
When the Pivot table form opens it only shows 12 lines of detail. True;
the
other 8 lines can be viewed on screen by using a verticle scroll bar but
this
doesn't address the issue of printing a schedule showing all lines.

Purpose of reducing the height of the detailed lines is to attempt to show
as many lines as possible on the printout.

The user needs to be able to click on the drop down arrow for the field
"For
the Week Beginning" and select the current week and the subsequent 3 week
period for printing. Actually, this is the only drop down the user has to
access to get the job done. All other drop downs are provided by the
Pivot
Table view but unnessary.

Hope this presents the issue in an understandable way. I could provide
you
with a snapshot of the actual schedule if this is any help.

Thanks for your interest in this problem.

Rick


:

I've had a quick play and a quick Google, but haven't got anywhere. The
problem is referencing an Excel OLE object in a form in a way that
exposes
Excel properties.

Do you actually need the functionality of Excel pivot tables? If you
don't
then I could come up with a solution.

Does anyone know the code to set the height of the rows in the
detailed
section of a pivot table form?

 

[Rob Oldfield]

Ugh. This really is horrible. Another question: is the data you need to
see always for three weeks or does that need to be flexible?

The structure of the query that generates the pivot table is as follows:

Staff table
StaffID PK autonumber
StaffGivenName text
StaffSurname text

Weeks table
WeeID PK autonumber
BeginWeek date format (ie "01/10/05")
EndWeek date format (ie "01/16/05")

JobsInProgressAssigned table

JIPAssignID PK autonumber
JIPStaffID lookup to Staff table
JIPWeekID lookup to Weeks table
JIPAssignment text (name of customer have job done)
JIPHoursBudget long integer (hours estimated for job to be completed)
JIPHoursLeft long integer (hours currently remaining to complete job)

Hmmm. That's not an easy one. But it can definitely be done solely in
Access. One question though - what's the structure of the table that
contains the base data that determines what comes out here?

Rob:

The screen display of the Pivot Table is OK, it is just the report that is
giving me grief. You idea of having a separate report is good but I have
never been able to figure out how to generate such a report in Access

so
that

it looks like a spreadsheet with specific sections running both verticially
and horizontially.

For example I need to show grouped sections for each employee running
vertically. That's the easy part. However; at the same time I need

to
show

several columns running horizontally within each staff section. For example:
WeekBegin WeekBegin
WeekBegin
Staff 01/02/05 01/09/05
01/16/05
Jack XYZ Co Peter's U'Pick
AllAboutFaces
Big Stop Rest
NS Paving Co.
Harry's Wholesale CarsRUs
Beehive Honey
Total Jobs 3 2
3
Ann Sewing Shop Co. Winnie Restaurant
BookStore Ltd.
Mary's Candies
Ocean Contract
Sam's Records
ATV Vehicles Ltd
Wallace Optical
Bells & Chimes
Porters Auto
Repair
Total Jobs 1 5
4
Grand Totals 4 7
7

A pivot table appears to work fine with the exception the number of lines
displayed in the detailed section is restricted to about 12 lines which is
not always enough.

Rick

:

Hmmm. From your description I don't think it looks like you

actually
need

the Excel functionality (though I may still be wrong of course).

(That functionality is, by the way, that you can update/reorganise

the
pivot

table on the fly. Suppose, in a personnel situation, that you had total
salaries broken down by Location, Department and Sex. Location and
Department as row headings down the left, and Sexes across the top. In
Excel, you can just drag the Department group from being a row

heading
to

being a column heading - same information, just organised differently.)

So I would definitely see if you can get the same result by purely using
Access.... this would be done by using a group query to calculate your
figures (or an extended type of group query called a crosstab

query).
In

addition, I should point out that forms are really designed just to be
displayed on screen, not for printing. Reports are for printing -

and
you

can get total figures on those either by just basing the report on

the
group

query, or in a slightly different way by basing the report on the

base
data

and then totalling by sections and hiding detail rows.

Or... you could use the pivot table to display the info on screen,

and
build

a report to print it as you want.

So... does the information on screen look OK at the moment? Is the problem
just with getting a printable version? If that's the case then you just
need a report.

Is that any use?



Hi Rob:

I am not really sure if I require the functionality of Excel pivot tables
in
my application. I have never actually used Pivot Tables in Excel

to
any

extent and am not quite sure if Access is generating the Pivot

Table
view

of
my data in Access or creating a link to Excel somehow. Perhaps if I
explain
the purpose. I have a form in Pivot Table view based on a query

which
the

result looks something like:

For the Week Beginning V
09/06/2004
09/12/2004
+-
+-
StaffMemberV HrsV Job AssignedV InV HrsV Job
AssignedV
InV
Bill + 10 Pro Company 05/12/04 13 XZY
Company 02/24/04
- 13 Pictures Ltd. 07/18/04 22
Old
Barns Co. 08/11/04
17 Sams Records 09/01/04
[totals line] 40
35
Jill + 7 Liams Ltd. 08/27/04 10
Pork
House Co. 08/22/04
- 29 CarsRUs 08/29/04 17
Boxes
Inc. 07/31/04
2 All About Us 09/02/04 25
Willow Bends 08/20/04
[totals line] 38
52
Grand Total + 78 87
-

Where the "V" following a field represents the drop down selector.

Idea is to generate a Excel type spreadsheet (using Pivot Table

view)
to

show the staff schedule each week; by each staff member; with

total
hours

assigned to work and with grand totals of all hours assigned to

staff
for

the
week.

Problem is a staff member may be assigned over 20 small jobs each week.
When the Pivot table form opens it only shows 12 lines of detail. True;
the
other 8 lines can be viewed on screen by using a verticle scroll

bar
but

this
doesn't address the issue of printing a schedule showing all lines.

Purpose of reducing the height of the detailed lines is to attempt

to
show

as many lines as possible on the printout.

The user needs to be able to click on the drop down arrow for the field
"For
the Week Beginning" and select the current week and the subsequent

3
week

period for printing. Actually, this is the only drop down the

user
has to

access to get the job done. All other drop downs are provided by the
Pivot
Table view but unnessary.

Hope this presents the issue in an understandable way. I could provide
you
with a snapshot of the actual schedule if this is any help.

Thanks for your interest in this problem.

Rick


:

I've had a quick play and a quick Google, but haven't got

anywhere.
The

problem is referencing an Excel OLE object in a form in a way that
exposes
Excel properties.

Do you actually need the functionality of Excel pivot tables?

If
you

don't
then I could come up with a solution.

Does anyone know the code to set the height of the rows in the
detailed
section of a pivot table form?

 

[Rick]

The selection of the weeks needs to be flexible. Sometimes the user will
only want to print 2 weeks, another time three weeks, another time four weeks
and occasionally the scheduling committee may want to print 52 weeks in
advance.

Ugh. This really is horrible. Another question: is the data you need to
see always for three weeks or does that need to be flexible?

The structure of the query that generates the pivot table is as follows:

Staff table
StaffID PK autonumber
StaffGivenName text
StaffSurname text

Weeks table
WeeID PK autonumber
BeginWeek date format (ie "01/10/05")
EndWeek date format (ie "01/16/05")

JobsInProgressAssigned table

JIPAssignID PK autonumber
JIPStaffID lookup to Staff table
JIPWeekID lookup to Weeks table
JIPAssignment text (name of customer have job done)
JIPHoursBudget long integer (hours estimated for job to be completed)
JIPHoursLeft long integer (hours currently remaining to complete job)

Hmmm. That's not an easy one. But it can definitely be done solely in
Access. One question though - what's the structure of the table that
contains the base data that determines what comes out here?


Rob:

The screen display of the Pivot Table is OK, it is just the report that is
giving me grief. You idea of having a separate report is good but I have
never been able to figure out how to generate such a report in Access so
that
it looks like a spreadsheet with specific sections running both
verticially
and horizontially.

For example I need to show grouped sections for each employee running
vertically. That's the easy part. However; at the same time I need to
show
several columns running horizontally within each staff section. For
example:
WeekBegin WeekBegin
WeekBegin
Staff 01/02/05 01/09/05
01/16/05
Jack XYZ Co Peter's U'Pick
AllAboutFaces
Big Stop Rest
NS Paving Co.
Harry's Wholesale CarsRUs
Beehive Honey
Total Jobs 3 2
3
Ann Sewing Shop Co. Winnie Restaurant
BookStore Ltd.
Mary's
Candies
Ocean Contract
Sam's Records
ATV Vehicles Ltd
Wallace
Optical
Bells & Chimes
Porters Auto
Repair
Total Jobs 1 5
4
Grand Totals 4 7
7

A pivot table appears to work fine with the exception the number of lines
displayed in the detailed section is restricted to about 12 lines which is
not always enough.

Rick

:

Hmmm. From your description I don't think it looks like you actually
need
the Excel functionality (though I may still be wrong of course).

(That functionality is, by the way, that you can update/reorganise the
pivot
table on the fly. Suppose, in a personnel situation, that you had total
salaries broken down by Location, Department and Sex. Location and
Department as row headings down the left, and Sexes across the top. In
Excel, you can just drag the Department group from being a row heading
to
being a column heading - same information, just organised differently.)

So I would definitely see if you can get the same result by purely using
Access.... this would be done by using a group query to calculate your
figures (or an extended type of group query called a crosstab query).
In
addition, I should point out that forms are really designed just to be
displayed on screen, not for printing. Reports are for printing - and
you
can get total figures on those either by just basing the report on the
group
query, or in a slightly different way by basing the report on the base
data
and then totalling by sections and hiding detail rows.

Or... you could use the pivot table to display the info on screen, and
build
a report to print it as you want.

So... does the information on screen look OK at the moment? Is the
problem
just with getting a printable version? If that's the case then you just
need a report.

Is that any use?



Hi Rob:

I am not really sure if I require the functionality of Excel pivot
tables
in
my application. I have never actually used Pivot Tables in Excel to
any
extent and am not quite sure if Access is generating the Pivot Table
view
of
my data in Access or creating a link to Excel somehow. Perhaps if I
explain
the purpose. I have a form in Pivot Table view based on a query which
the
result looks something like:

For the Week Beginning V
09/06/2004
09/12/2004
+-
+-
StaffMemberV HrsV Job AssignedV InV HrsV Job
AssignedV
InV
Bill + 10 Pro Company 05/12/04 13
XZY
Company 02/24/04
- 13 Pictures Ltd. 07/18/04 22
Old
Barns Co. 08/11/04
17 Sams Records 09/01/04
[totals line] 40
35
Jill + 7 Liams Ltd. 08/27/04 10
Pork
House Co. 08/22/04
- 29 CarsRUs 08/29/04 17
Boxes
Inc. 07/31/04
2 All About Us 09/02/04 25
Willow Bends 08/20/04
[totals line] 38
52
Grand Total + 78
87
-

Where the "V" following a field represents the drop down selector.

Idea is to generate a Excel type spreadsheet (using Pivot Table view)
to
show the staff schedule each week; by each staff member; with total
hours
assigned to work and with grand totals of all hours assigned to staff
for
the
week.

Problem is a staff member may be assigned over 20 small jobs each
week.
When the Pivot table form opens it only shows 12 lines of detail.
True;
the
other 8 lines can be viewed on screen by using a verticle scroll bar
but
this
doesn't address the issue of printing a schedule showing all lines.

Purpose of reducing the height of the detailed lines is to attempt to
show
as many lines as possible on the printout.

The user needs to be able to click on the drop down arrow for the
field
"For
the Week Beginning" and select the current week and the subsequent 3
week
period for printing. Actually, this is the only drop down the user
has to
access to get the job done. All other drop downs are provided by the
Pivot
Table view but unnessary.

Hope this presents the issue in an understandable way. I could
provide
you
with a snapshot of the actual schedule if this is any help.

Thanks for your interest in this problem.

Rick


:

I've had a quick play and a quick Google, but haven't got anywhere.
The
problem is referencing an Excel OLE object in a form in a way that
exposes
Excel properties.

Do you actually need the functionality of Excel pivot tables? If
you
don't
then I could come up with a solution.

Does anyone know the code to set the height of the rows in the
detailed
section of a pivot table form?

 

[Rob Oldfield]

Now how did I know you were going to say that?

Right. I've got most of it sorted out I think. Not quite polished off but
let's see what you think so far. Again, this really is quite nasty.

Your problem is, pretty much, that you're attempting to take a normalised
set of data and push it back into a completely non-normalised format.
Nothing wrong with that, but it does make things complicated.

I don't think there's any way of getting at what you're after without some
serious playing around with the data. It might be possible to do it by
using subreports but I think that looks at least as messy.

So...

First thing that I've done is set up a query that I've called DatesCT. SQL
is

TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP BY 1
PIVOT Weeks.WeekID;

....basically using a crosstab query to create a structure with weeks as
column headings.

Next...another query that is a make table in order to permanently create a
structure that I'm going to use to store the data...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));

Note that I want the Staff field (which I really should have called StaffID
again) to be numeric (so that I can put StaffIDs into it) which is where the
"1" in DatesCT comes from - to ensure that it comes out that way.
Similarly, I want the data type of the 1, 2, 3 fields to be text (so that I
can drop the names of jobs into them) - hence the Max('x'). The <>1
criterion is just so that the existing row doesn't get added to the new
table.

OK. Next bit. Take the normalised version of your data and push it into
this new table. To do this I have another query called ReportData...

SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID, Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;

To take this data and push it into the ReportBasis table I've needed to
resort to code...

Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where the
WeekID field
'hasn't been completed yet
.FindFirst ("[Staff]=" + CStr(rs!staffid) + _
" and isnull([" + CStr(rs!WeekID) + "])")
'If there isn't one, then we need a new row
If .NoMatch Then
'and complete the information
rsoutput.AddNew
rsoutput!Staff = rs!staffid
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
Else
'otherwise, just fill in the blank
rsoutput.Edit
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
End If
End With
'and move to the next record
rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub

You could obviously apply some kind of date criteria to this process as you
require.

You can now create a report based on this new table... but...

The need to keep the flexibility to print out the report for 52 weeks is the
last complication, and the one that I haven't dug into in detail as yet.
Obviously, there's no way in the world you're going to be able to fit 52
weeks across a single piece of paper so...

My idea would be to create the report with the maximum number of fields
across the page... say five. Given an outside parameter - i.e. the first
week number required in the report - then you can do something like this in
the Open event of the report...

Me.[1_label].Caption = 'look up the outside number in the weeks table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by default, using my
methodology) is called 1 but you can remap it to something different by
doing this.

I think you should, therefore, be able to loop through values in a form..

For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next

....and each time the report opens it references forms!formname!whatever to
see the first week it should be working with.

Simple, isn't it?

We do seem to have got a little way away from where this thread started out,
but I think it's going to be tricky to come up with an automated solution no
matter how you try and do it (if you use the pivottable approach, then
that's going to be equally useless when you have 52 weeks to cope with).
The simple approach would be to just push the Excel data out into a
spreadsheet, or just create a new spreadsheet linked in to the database, but
that's always going to require the user to be able to manipulate the
spreadsheet in order to get the required output.

I'm going for a nice lie down now anyway....

 

[Rick]

Query DatesCT; query 2 which I have name StoreSchedData and query ReportData
appear to function as expected.

ReportData query generates a table showing the WeekID; StaffID and
JIPAssignment.

However; I am confused about the code used to "take this dta and push it
into the ReportBasis table". Not sure if this code is supposed to reside in
yet another query or added to ReportData query. Attempted copying the code
into a new query and running it with the Access error "SQL Error - expecting
INPUT, DELETE ...."
Guess you can tell by now I am far from an Access programmer.

Incidentally, I have noted an error in my original comments about the
structure of the Staff table. The StaffID PK is not actually an autonumber;
but rather a three letter index. Doesn't appear to affect your suggestions
to date but the reference to the numeric 1 might have to be altered.

Now how did I know you were going to say that?

Right. I've got most of it sorted out I think. Not quite polished off but
let's see what you think so far. Again, this really is quite nasty.

Your problem is, pretty much, that you're attempting to take a normalised
set of data and push it back into a completely non-normalised format.
Nothing wrong with that, but it does make things complicated.

I don't think there's any way of getting at what you're after without some
serious playing around with the data. It might be possible to do it by
using subreports but I think that looks at least as messy.

So...

First thing that I've done is set up a query that I've called DatesCT. SQL
is

TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP BY 1
PIVOT Weeks.WeekID;

....basically using a crosstab query to create a structure with weeks as
column headings.

Next...another query that is a make table in order to permanently create a
structure that I'm going to use to store the data...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));

Note that I want the Staff field (which I really should have called StaffID
again) to be numeric (so that I can put StaffIDs into it) which is where the
"1" in DatesCT comes from - to ensure that it comes out that way.
Similarly, I want the data type of the 1, 2, 3 fields to be text (so that I
can drop the names of jobs into them) - hence the Max('x'). The <>1
criterion is just so that the existing row doesn't get added to the new
table.

OK. Next bit. Take the normalised version of your data and push it into
this new table. To do this I have another query called ReportData...

SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID, Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;

To take this data and push it into the ReportBasis table I've needed to
resort to code...

Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where the
WeekID field
'hasn't been completed yet
.FindFirst ("[Staff]=" + CStr(rs!staffid) + _
" and isnull([" + CStr(rs!WeekID) + "])")
'If there isn't one, then we need a new row
If .NoMatch Then
'and complete the information
rsoutput.AddNew
rsoutput!Staff = rs!staffid
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
Else
'otherwise, just fill in the blank
rsoutput.Edit
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
End If
End With
'and move to the next record
rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub

You could obviously apply some kind of date criteria to this process as you
require.

You can now create a report based on this new table... but...

The need to keep the flexibility to print out the report for 52 weeks is the
last complication, and the one that I haven't dug into in detail as yet.
Obviously, there's no way in the world you're going to be able to fit 52
weeks across a single piece of paper so...

My idea would be to create the report with the maximum number of fields
across the page... say five. Given an outside parameter - i.e. the first
week number required in the report - then you can do something like this in
the Open event of the report...

Me.[1_label].Caption = 'look up the outside number in the weeks table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by default, using my
methodology) is called 1 but you can remap it to something different by
doing this.

I think you should, therefore, be able to loop through values in a form..

For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next

....and each time the report opens it references forms!formname!whatever to
see the first week it should be working with.

Simple, isn't it?

We do seem to have got a little way away from where this thread started out,
but I think it's going to be tricky to come up with an automated solution no
matter how you try and do it (if you use the pivottable approach, then
that's going to be equally useless when you have 52 weeks to cope with).
The simple approach would be to just push the Excel data out into a
spreadsheet, or just create a new spreadsheet linked in to the database, but
that's always going to require the user to be able to manipulate the
spreadsheet in order to get the required output.

I'm going for a nice lie down now anyway....

The selection of the weeks needs to be flexible. Sometimes the user will
only want to print 2 weeks, another time three weeks, another time four weeks
and occasionally the scheduling committee may want to print 52 weeks in
advance.

 

[Rob Oldfield]

You need a method of running the code. The best way to do this will be to
create a form (doesn't need to be based on any data) and add a button to it
(..use View, Toolbox and then drag a button to the form..) Then click on
the button and get the properties of it by View, Properties. On the Event
tab click in the On Click event and click the three buttons on the right.
You want the Code Builder option. That gives you an event that will run
when you click the button.

Edit that so it says...

Private Sub Command0_Click()
DoCmd.SetWarnings False 'stops you getting warnings about queries running
DoCmd.OpenQuery "StoreSchedData"
Call SetupData 'calls the subroutine to set the data up
DoCmd.SetWarnings True 'turns warning back on
End Sub

....below that add...

Private Sub SetupData()
....all the other stuff....
End Sub

And then you can just view the form and keep your fingers crossed.

Notes... you're right that you'll need to change a couple of things because
the StaffPK is text...

DatesCT will now need to be...

TRANSFORM Max('x') AS Expr1
SELECT 'x' AS Staff
FROM Weeks
GROUP BY 'x'
PIVOT [Weeks].[WeekID];

StoreSchedDate...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>"x"));

Update a line in the SetupData routine to be...

..FindFirst ("[Staff]='" + CStr(rs!staffid) + _
"' and isnull([" + CStr(rs!WeekID) + "])")

Query DatesCT; query 2 which I have name StoreSchedData and query ReportData
appear to function as expected.

ReportData query generates a table showing the WeekID; StaffID and
JIPAssignment.

However; I am confused about the code used to "take this dta and push it
into the ReportBasis table". Not sure if this code is supposed to reside in
yet another query or added to ReportData query. Attempted copying the code
into a new query and running it with the Access error "SQL Error - expecting
INPUT, DELETE ...."
Guess you can tell by now I am far from an Access programmer.

Incidentally, I have noted an error in my original comments about the
structure of the Staff table. The StaffID PK is not actually an autonumber;
but rather a three letter index. Doesn't appear to affect your suggestions
to date but the reference to the numeric 1 might have to be altered.

Now how did I know you were going to say that?

Right. I've got most of it sorted out I think. Not quite polished off but
let's see what you think so far. Again, this really is quite nasty.

Your problem is, pretty much, that you're attempting to take a normalised
set of data and push it back into a completely non-normalised format.
Nothing wrong with that, but it does make things complicated.

I don't think there's any way of getting at what you're after without some
serious playing around with the data. It might be possible to do it by
using subreports but I think that looks at least as messy.

So...

First thing that I've done is set up a query that I've called DatesCT. SQL
is

TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP BY 1
PIVOT Weeks.WeekID;

....basically using a crosstab query to create a structure with weeks as
column headings.

Next...another query that is a make table in order to permanently create a
structure that I'm going to use to store the data...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));

Note that I want the Staff field (which I really should have called StaffID
again) to be numeric (so that I can put StaffIDs into it) which is where the
"1" in DatesCT comes from - to ensure that it comes out that way.
Similarly, I want the data type of the 1, 2, 3 fields to be text (so that I
can drop the names of jobs into them) - hence the Max('x'). The <>1
criterion is just so that the existing row doesn't get added to the new
table.

OK. Next bit. Take the normalised version of your data and push it into
this new table. To do this I have another query called ReportData...

SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID, Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;

To take this data and push it into the ReportBasis table I've needed to
resort to code...

Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where the
WeekID field
'hasn't been completed yet
.FindFirst ("[Staff]=" + CStr(rs!staffid) + _
" and isnull([" + CStr(rs!WeekID) + "])")
'If there isn't one, then we need a new row
If .NoMatch Then
'and complete the information
rsoutput.AddNew
rsoutput!Staff = rs!staffid
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
Else
'otherwise, just fill in the blank
rsoutput.Edit
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
End If
End With
'and move to the next record
rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub

You could obviously apply some kind of date criteria to this process as you
require.

You can now create a report based on this new table... but...

The need to keep the flexibility to print out the report for 52 weeks is the
last complication, and the one that I haven't dug into in detail as yet.
Obviously, there's no way in the world you're going to be able to fit 52
weeks across a single piece of paper so...

My idea would be to create the report with the maximum number of fields
across the page... say five. Given an outside parameter - i.e. the first
week number required in the report - then you can do something like this in
the Open event of the report...

Me.[1_label].Caption = 'look up the outside number in the weeks table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by default, using my
methodology) is called 1 but you can remap it to something different by
doing this.

I think you should, therefore, be able to loop through values in a form..

For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next

....and each time the report opens it references forms!formname!whatever to
see the first week it should be working with.

Simple, isn't it?

We do seem to have got a little way away from where this thread started out,
but I think it's going to be tricky to come up with an automated solution no
matter how you try and do it (if you use the pivottable approach, then
that's going to be equally useless when you have 52 weeks to cope with).
The simple approach would be to just push the Excel data out into a
spreadsheet, or just create a new spreadsheet linked in to the database, but
that's always going to require the user to be able to manipulate the
spreadsheet in order to get the required output.

I'm going for a nice lie down now anyway....

The selection of the weeks needs to be flexible. Sometimes the user will
only want to print 2 weeks, another time three weeks, another time

four
weeks

and occasionally the scheduling committee may want to print 52 weeks in
advance.

 

[Rick]

Code runs to the SetupData query line WeekId Field and returns a compile
error "Invalid Use of Property" :

Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where the
WeekID Field
'hasn't been completed yet
.FindFirst ("[Staff]='" + CStr(rs!StaffID) + _
"' and isnull([" + CStr(rs!WeekID) + "])")

You need a method of running the code. The best way to do this will be to
create a form (doesn't need to be based on any data) and add a button to it
(..use View, Toolbox and then drag a button to the form..) Then click on
the button and get the properties of it by View, Properties. On the Event
tab click in the On Click event and click the three buttons on the right.
You want the Code Builder option. That gives you an event that will run
when you click the button.

Edit that so it says...

Private Sub Command0_Click()
DoCmd.SetWarnings False 'stops you getting warnings about queries running
DoCmd.OpenQuery "StoreSchedData"
Call SetupData 'calls the subroutine to set the data up
DoCmd.SetWarnings True 'turns warning back on
End Sub

....below that add...

Private Sub SetupData()
....all the other stuff....
End Sub

And then you can just view the form and keep your fingers crossed.

Notes... you're right that you'll need to change a couple of things because
the StaffPK is text...

DatesCT will now need to be...

TRANSFORM Max('x') AS Expr1
SELECT 'x' AS Staff
FROM Weeks
GROUP BY 'x'
PIVOT [Weeks].[WeekID];

StoreSchedDate...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>"x"));

Update a line in the SetupData routine to be...

..FindFirst ("[Staff]='" + CStr(rs!staffid) + _
"' and isnull([" + CStr(rs!WeekID) + "])")

Query DatesCT; query 2 which I have name StoreSchedData and query ReportData
appear to function as expected.

ReportData query generates a table showing the WeekID; StaffID and
JIPAssignment.

However; I am confused about the code used to "take this dta and push it
into the ReportBasis table". Not sure if this code is supposed to reside in
yet another query or added to ReportData query. Attempted copying the code
into a new query and running it with the Access error "SQL Error - expecting
INPUT, DELETE ...."
Guess you can tell by now I am far from an Access programmer.

Incidentally, I have noted an error in my original comments about the
structure of the Staff table. The StaffID PK is not actually an autonumber;
but rather a three letter index. Doesn't appear to affect your suggestions
to date but the reference to the numeric 1 might have to be altered.

Now how did I know you were going to say that?

Right. I've got most of it sorted out I think. Not quite polished off but
let's see what you think so far. Again, this really is quite nasty.

Your problem is, pretty much, that you're attempting to take a normalised
set of data and push it back into a completely non-normalised format.
Nothing wrong with that, but it does make things complicated.

I don't think there's any way of getting at what you're after without some
serious playing around with the data. It might be possible to do it by
using subreports but I think that looks at least as messy.

So...

First thing that I've done is set up a query that I've called DatesCT. SQL
is

TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP BY 1
PIVOT Weeks.WeekID;

....basically using a crosstab query to create a structure with weeks as
column headings.

Next...another query that is a make table in order to permanently create a
structure that I'm going to use to store the data...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));

Note that I want the Staff field (which I really should have called StaffID
again) to be numeric (so that I can put StaffIDs into it) which is where the
"1" in DatesCT comes from - to ensure that it comes out that way.
Similarly, I want the data type of the 1, 2, 3 fields to be text (so that I
can drop the names of jobs into them) - hence the Max('x'). The <>1
criterion is just so that the existing row doesn't get added to the new
table.

OK. Next bit. Take the normalised version of your data and push it into
this new table. To do this I have another query called ReportData...

SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID, Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;

To take this data and push it into the ReportBasis table I've needed to
resort to code...

Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where the
WeekID field
'hasn't been completed yet
.FindFirst ("[Staff]=" + CStr(rs!staffid) + _
" and isnull([" + CStr(rs!WeekID) + "])")
'If there isn't one, then we need a new row
If .NoMatch Then
'and complete the information
rsoutput.AddNew
rsoutput!Staff = rs!staffid
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
Else
'otherwise, just fill in the blank
rsoutput.Edit
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
End If
End With
'and move to the next record
rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub

You could obviously apply some kind of date criteria to this process as you
require.

You can now create a report based on this new table... but...

The need to keep the flexibility to print out the report for 52 weeks is the
last complication, and the one that I haven't dug into in detail as yet.
Obviously, there's no way in the world you're going to be able to fit 52
weeks across a single piece of paper so...

My idea would be to create the report with the maximum number of fields
across the page... say five. Given an outside parameter - i.e. the first
week number required in the report - then you can do something like this in
the Open event of the report...

Me.[1_label].Caption = 'look up the outside number in the weeks table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by default, using my
methodology) is called 1 but you can remap it to something different by
doing this.

I think you should, therefore, be able to loop through values in a form..

For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next

....and each time the report opens it references forms!formname!whatever to
see the first week it should be working with.

Simple, isn't it?

We do seem to have got a little way away from where this thread started out,
but I think it's going to be tricky to come up with an automated solution no
matter how you try and do it (if you use the pivottable approach, then
that's going to be equally useless when you have 52 weeks to cope with).
The simple approach would be to just push the Excel data out into a
spreadsheet, or just create a new spreadsheet linked in to the database, but
that's always going to require the user to be able to manipulate the
spreadsheet in order to get the required output.

I'm going for a nice lie down now anyway....



The selection of the weeks needs to be flexible. Sometimes the user will
only want to print 2 weeks, another time three weeks, another time four
weeks
and occasionally the scheduling committee may want to print 52 weeks in
advance.

 

[Rob Oldfield]

Which text does it highlight?

(If it's any of the text 'WeekID field' then that is supposed to be part of
the comment on the previous line. You can just add a ' at the start of the
line to comment it out.)

Code runs to the SetupData query line WeekId Field and returns a compile
error "Invalid Use of Property" :

Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where the
WeekID Field
'hasn't been completed yet
.FindFirst ("[Staff]='" + CStr(rs!StaffID) + _
"' and isnull([" + CStr(rs!WeekID) + "])")

You need a method of running the code. The best way to do this will be to
create a form (doesn't need to be based on any data) and add a button to it
(..use View, Toolbox and then drag a button to the form..) Then click on
the button and get the properties of it by View, Properties. On the Event
tab click in the On Click event and click the three buttons on the right.
You want the Code Builder option. That gives you an event that will run
when you click the button.

Edit that so it says...

Private Sub Command0_Click()
DoCmd.SetWarnings False 'stops you getting warnings about queries running
DoCmd.OpenQuery "StoreSchedData"
Call SetupData 'calls the subroutine to set the data up
DoCmd.SetWarnings True 'turns warning back on
End Sub

....below that add...

Private Sub SetupData()
....all the other stuff....
End Sub

And then you can just view the form and keep your fingers crossed.

Notes... you're right that you'll need to change a couple of things because
the StaffPK is text...

DatesCT will now need to be...

TRANSFORM Max('x') AS Expr1
SELECT 'x' AS Staff
FROM Weeks
GROUP BY 'x'
PIVOT [Weeks].[WeekID];

StoreSchedDate...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>"x"));

Update a line in the SetupData routine to be...

..FindFirst ("[Staff]='" + CStr(rs!staffid) + _
"' and isnull([" + CStr(rs!WeekID) + "])")

Query DatesCT; query 2 which I have name StoreSchedData and query ReportData
appear to function as expected.

ReportData query generates a table showing the WeekID; StaffID and
JIPAssignment.

However; I am confused about the code used to "take this dta and push it
into the ReportBasis table". Not sure if this code is supposed to

reside
in

yet another query or added to ReportData query. Attempted copying the code
into a new query and running it with the Access error "SQL Error - expecting
INPUT, DELETE ...."
Guess you can tell by now I am far from an Access programmer.

Incidentally, I have noted an error in my original comments about the
structure of the Staff table. The StaffID PK is not actually an autonumber;
but rather a three letter index. Doesn't appear to affect your suggestions
to date but the reference to the numeric 1 might have to be altered.



:

Now how did I know you were going to say that?

Right. I've got most of it sorted out I think. Not quite polished

off
but

let's see what you think so far. Again, this really is quite nasty.

Your problem is, pretty much, that you're attempting to take a normalised
set of data and push it back into a completely non-normalised format.
Nothing wrong with that, but it does make things complicated.

I don't think there's any way of getting at what you're after

without
some

serious playing around with the data. It might be possible to do it by
using subreports but I think that looks at least as messy.

So...

First thing that I've done is set up a query that I've called

DatesCT.
SQL

is

TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP BY 1
PIVOT Weeks.WeekID;

....basically using a crosstab query to create a structure with weeks as
column headings.

Next...another query that is a make table in order to permanently

create
a

structure that I'm going to use to store the data...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));

Note that I want the Staff field (which I really should have called StaffID
again) to be numeric (so that I can put StaffIDs into it) which is

where
the

"1" in DatesCT comes from - to ensure that it comes out that way.
Similarly, I want the data type of the 1, 2, 3 fields to be text (so that I
can drop the names of jobs into them) - hence the Max('x'). The <>1
criterion is just so that the existing row doesn't get added to the new
table.

OK. Next bit. Take the normalised version of your data and push it into
this new table. To do this I have another query called ReportData...

SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID, Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;

To take this data and push it into the ReportBasis table I've needed to
resort to code...

Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where the
WeekID field
'hasn't been completed yet
.FindFirst ("[Staff]=" + CStr(rs!staffid) + _
" and isnull([" + CStr(rs!WeekID) + "])")
'If there isn't one, then we need a new row
If .NoMatch Then
'and complete the information
rsoutput.AddNew
rsoutput!Staff = rs!staffid
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
Else
'otherwise, just fill in the blank
rsoutput.Edit
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
End If
End With
'and move to the next record
rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub

You could obviously apply some kind of date criteria to this process

as
you

require.

You can now create a report based on this new table... but...

The need to keep the flexibility to print out the report for 52

weeks is
the

last complication, and the one that I haven't dug into in detail as yet.
Obviously, there's no way in the world you're going to be able to fit 52
weeks across a single piece of paper so...

My idea would be to create the report with the maximum number of fields
across the page... say five. Given an outside parameter - i.e. the

first

week number required in the report - then you can do something like

this
in

the Open event of the report...

Me.[1_label].Caption = 'look up the outside number in the weeks table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by default,

using
my

methodology) is called 1 but you can remap it to something different by
doing this.

I think you should, therefore, be able to loop through values in a form..

For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next

....and each time the report opens it references

forms!formname!whatever
to

see the first week it should be working with.

Simple, isn't it?

We do seem to have got a little way away from where this thread

started
out,

but I think it's going to be tricky to come up with an automated solution no
matter how you try and do it (if you use the pivottable approach, then
that's going to be equally useless when you have 52 weeks to cope with).
The simple approach would be to just push the Excel data out into a
spreadsheet, or just create a new spreadsheet linked in to the

database,
but

that's always going to require the user to be able to manipulate the
spreadsheet in order to get the required output.

I'm going for a nice lie down now anyway....



The selection of the weeks needs to be flexible. Sometimes the

user
will

only want to print 2 weeks, another time three weeks, another time four
weeks
and occasionally the scheduling committee may want to print 52

weeks
in

advance.

 

[Rick]

Sorry, yes, the comment marker was dropped when pasting the code.

Ran code again with the following error in Private Sub SetupData() :

Run-time error 3265 Item not found in collection
Debug highlites line "rsoutput.Fields(SCtr(rs!WeekID) = rsjipassignment"

Tried commenting that line out and the code runs to same line as above
written after Else statement.

Which text does it highlight?

(If it's any of the text 'WeekID field' then that is supposed to be part of
the comment on the previous line. You can just add a ' at the start of the
line to comment it out.)

Code runs to the SetupData query line WeekId Field and returns a compile
error "Invalid Use of Property" :

Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where the
WeekID Field
'hasn't been completed yet
.FindFirst ("[Staff]='" + CStr(rs!StaffID) + _
"' and isnull([" + CStr(rs!WeekID) + "])")

You need a method of running the code. The best way to do this will be to
create a form (doesn't need to be based on any data) and add a button to it
(..use View, Toolbox and then drag a button to the form..) Then click on
the button and get the properties of it by View, Properties. On the Event
tab click in the On Click event and click the three buttons on the right.
You want the Code Builder option. That gives you an event that will run
when you click the button.

Edit that so it says...

Private Sub Command0_Click()
DoCmd.SetWarnings False 'stops you getting warnings about queries running
DoCmd.OpenQuery "StoreSchedData"
Call SetupData 'calls the subroutine to set the data up
DoCmd.SetWarnings True 'turns warning back on
End Sub

....below that add...

Private Sub SetupData()
....all the other stuff....
End Sub

And then you can just view the form and keep your fingers crossed.

Notes... you're right that you'll need to change a couple of things because
the StaffPK is text...

DatesCT will now need to be...

TRANSFORM Max('x') AS Expr1
SELECT 'x' AS Staff
FROM Weeks
GROUP BY 'x'
PIVOT [Weeks].[WeekID];

StoreSchedDate...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>"x"));

Update a line in the SetupData routine to be...

..FindFirst ("[Staff]='" + CStr(rs!staffid) + _
"' and isnull([" + CStr(rs!WeekID) + "])")


Query DatesCT; query 2 which I have name StoreSchedData and query
ReportData
appear to function as expected.

ReportData query generates a table showing the WeekID; StaffID and
JIPAssignment.

However; I am confused about the code used to "take this dta and push it
into the ReportBasis table". Not sure if this code is supposed to reside
in
yet another query or added to ReportData query. Attempted copying the
code
into a new query and running it with the Access error "SQL Error -
expecting
INPUT, DELETE ...."
Guess you can tell by now I am far from an Access programmer.

Incidentally, I have noted an error in my original comments about the
structure of the Staff table. The StaffID PK is not actually an
autonumber;
but rather a three letter index. Doesn't appear to affect your
suggestions
to date but the reference to the numeric 1 might have to be altered.



:

Now how did I know you were going to say that?

Right. I've got most of it sorted out I think. Not quite polished off
but
let's see what you think so far. Again, this really is quite nasty.

Your problem is, pretty much, that you're attempting to take a
normalised
set of data and push it back into a completely non-normalised format.
Nothing wrong with that, but it does make things complicated.

I don't think there's any way of getting at what you're after without
some
serious playing around with the data. It might be possible to do it by
using subreports but I think that looks at least as messy.

So...

First thing that I've done is set up a query that I've called DatesCT.
SQL
is

TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP BY 1
PIVOT Weeks.WeekID;

....basically using a crosstab query to create a structure with weeks as
column headings.

Next...another query that is a make table in order to permanently create
a
structure that I'm going to use to store the data...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));

Note that I want the Staff field (which I really should have called
StaffID
again) to be numeric (so that I can put StaffIDs into it) which is where
the
"1" in DatesCT comes from - to ensure that it comes out that way.
Similarly, I want the data type of the 1, 2, 3 fields to be text (so
that I
can drop the names of jobs into them) - hence the Max('x'). The <>1
criterion is just so that the existing row doesn't get added to the new
table.

OK. Next bit. Take the normalised version of your data and push it
into
this new table. To do this I have another query called ReportData...

SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID, Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;

To take this data and push it into the ReportBasis table I've needed to
resort to code...

Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where the
WeekID field
'hasn't been completed yet
.FindFirst ("[Staff]=" + CStr(rs!staffid) + _
" and isnull([" + CStr(rs!WeekID) + "])")
'If there isn't one, then we need a new row
If .NoMatch Then
'and complete the information
rsoutput.AddNew
rsoutput!Staff = rs!staffid
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
Else
'otherwise, just fill in the blank
rsoutput.Edit
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
End If
End With
'and move to the next record
rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub

You could obviously apply some kind of date criteria to this process as
you
require.

You can now create a report based on this new table... but...

The need to keep the flexibility to print out the report for 52 weeks is
the
last complication, and the one that I haven't dug into in detail as yet.
Obviously, there's no way in the world you're going to be able to fit 52
weeks across a single piece of paper so...

My idea would be to create the report with the maximum number of fields
across the page... say five. Given an outside parameter - i.e. the
first
week number required in the report - then you can do something like this
in
the Open event of the report...

Me.[1_label].Caption = 'look up the outside number in the weeks table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by default, using
my
methodology) is called 1 but you can remap it to something different by
doing this.

I think you should, therefore, be able to loop through values in a
form..

For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next

....and each time the report opens it references forms!formname!whatever
to
see the first week it should be working with.

Simple, isn't it?

We do seem to have got a little way away from where this thread started
out,
but I think it's going to be tricky to come up with an automated
solution no
matter how you try and do it (if you use the pivottable approach, then
that's going to be equally useless when you have 52 weeks to cope with).
The simple approach would be to just push the Excel data out into a
spreadsheet, or just create a new spreadsheet linked in to the database,
but
that's always going to require the user to be able to manipulate the
spreadsheet in order to get the required output.

I'm going for a nice lie down now anyway....



The selection of the weeks needs to be flexible. Sometimes the user
will
only want to print 2 weeks, another time three weeks, another time
four
weeks
and occasionally the scheduling committee may want to print 52 weeks
in
advance.

 

[Rob Oldfield]

That's...

rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment

....correct?

Sorry, yes, the comment marker was dropped when pasting the code.

Ran code again with the following error in Private Sub SetupData() :

Run-time error 3265 Item not found in collection
Debug highlites line "rsoutput.Fields(SCtr(rs!WeekID) = rsjipassignment"

Tried commenting that line out and the code runs to same line as above
written after Else statement.

Which text does it highlight?

(If it's any of the text 'WeekID field' then that is supposed to be part of
the comment on the previous line. You can just add a ' at the start of the
line to comment it out.)

Code runs to the SetupData query line WeekId Field and returns a compile
error "Invalid Use of Property" :

Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where the
WeekID Field
'hasn't been completed yet
.FindFirst ("[Staff]='" + CStr(rs!StaffID) + _
"' and isnull([" + CStr(rs!WeekID) + "])")



:

You need a method of running the code. The best way to do this will

be
to

create a form (doesn't need to be based on any data) and add a

button to
it

(..use View, Toolbox and then drag a button to the form..) Then

click
on

the button and get the properties of it by View, Properties. On the Event
tab click in the On Click event and click the three buttons on the right.
You want the Code Builder option. That gives you an event that will run
when you click the button.

Edit that so it says...

Private Sub Command0_Click()
DoCmd.SetWarnings False 'stops you getting warnings about queries running
DoCmd.OpenQuery "StoreSchedData"
Call SetupData 'calls the subroutine to set the data up
DoCmd.SetWarnings True 'turns warning back on
End Sub

....below that add...

Private Sub SetupData()
....all the other stuff....
End Sub

And then you can just view the form and keep your fingers crossed.

Notes... you're right that you'll need to change a couple of things because
the StaffPK is text...

DatesCT will now need to be...

TRANSFORM Max('x') AS Expr1
SELECT 'x' AS Staff
FROM Weeks
GROUP BY 'x'
PIVOT [Weeks].[WeekID];

StoreSchedDate...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>"x"));

Update a line in the SetupData routine to be...

..FindFirst ("[Staff]='" + CStr(rs!staffid) + _
"' and isnull([" + CStr(rs!WeekID) + "])")


Query DatesCT; query 2 which I have name StoreSchedData and query
ReportData
appear to function as expected.

ReportData query generates a table showing the WeekID; StaffID and
JIPAssignment.

However; I am confused about the code used to "take this dta and

push
it

into the ReportBasis table". Not sure if this code is supposed to reside
in
yet another query or added to ReportData query. Attempted copying the
code
into a new query and running it with the Access error "SQL Error -
expecting
INPUT, DELETE ...."
Guess you can tell by now I am far from an Access programmer.

Incidentally, I have noted an error in my original comments about the
structure of the Staff table. The StaffID PK is not actually an
autonumber;
but rather a three letter index. Doesn't appear to affect your
suggestions
to date but the reference to the numeric 1 might have to be altered.



:

Now how did I know you were going to say that?

Right. I've got most of it sorted out I think. Not quite

polished
off

but
let's see what you think so far. Again, this really is quite nasty.

Your problem is, pretty much, that you're attempting to take a
normalised
set of data and push it back into a completely non-normalised format.
Nothing wrong with that, but it does make things complicated.

I don't think there's any way of getting at what you're after without
some
serious playing around with the data. It might be possible to

do it
by

using subreports but I think that looks at least as messy.

So...

First thing that I've done is set up a query that I've called DatesCT.
SQL
is

TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP BY 1
PIVOT Weeks.WeekID;

....basically using a crosstab query to create a structure with weeks as
column headings.

Next...another query that is a make table in order to

permanently
create

a
structure that I'm going to use to store the data...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));

Note that I want the Staff field (which I really should have called
StaffID
again) to be numeric (so that I can put StaffIDs into it) which

is
where

the
"1" in DatesCT comes from - to ensure that it comes out that way.
Similarly, I want the data type of the 1, 2, 3 fields to be text (so
that I
can drop the names of jobs into them) - hence the Max('x'). The

criterion is just so that the existing row doesn't get added to

the
new

table.

OK. Next bit. Take the normalised version of your data and push it
into
this new table. To do this I have another query called ReportData...

SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID, Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;

To take this data and push it into the ReportBasis table I've

needed
to

resort to code...

Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but

where
the

WeekID field
'hasn't been completed yet
.FindFirst ("[Staff]=" + CStr(rs!staffid) + _
" and isnull([" + CStr(rs!WeekID) + "])")
'If there isn't one, then we need a new row
If .NoMatch Then
'and complete the information
rsoutput.AddNew
rsoutput!Staff = rs!staffid
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
Else
'otherwise, just fill in the blank
rsoutput.Edit
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
End If
End With
'and move to the next record
rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub

You could obviously apply some kind of date criteria to this

process
as

you
require.

You can now create a report based on this new table... but...

The need to keep the flexibility to print out the report for 52 weeks is
the
last complication, and the one that I haven't dug into in detail

as
yet.

Obviously, there's no way in the world you're going to be able

to
fit 52

weeks across a single piece of paper so...

My idea would be to create the report with the maximum number of fields
across the page... say five. Given an outside parameter - i.e.

the

first
week number required in the report - then you can do something

like
this

in
the Open event of the report...

Me.[1_label].Caption = 'look up the outside number in the weeks table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by

default,
using

my
methodology) is called 1 but you can remap it to something

different
by

doing this.

I think you should, therefore, be able to loop through values in a
form..

For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next

....and each time the report opens it references forms!formname!whatever
to
see the first week it should be working with.

Simple, isn't it?

We do seem to have got a little way away from where this thread started
out,
but I think it's going to be tricky to come up with an automated
solution no
matter how you try and do it (if you use the pivottable

approach,
then

that's going to be equally useless when you have 52 weeks to

cope
with).

The simple approach would be to just push the Excel data out into a
spreadsheet, or just create a new spreadsheet linked in to the database,
but
that's always going to require the user to be able to manipulate the
spreadsheet in order to get the required output.

I'm going for a nice lie down now anyway....



The selection of the weeks needs to be flexible. Sometimes

the
user

will
only want to print 2 weeks, another time three weeks, another time
four
weeks
and occasionally the scheduling committee may want to print 52 weeks
in
advance.

 

[Rick]

Yes, exactly as copied from code window:

rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment

That's...

rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment

....correct?

Sorry, yes, the comment marker was dropped when pasting the code.

Ran code again with the following error in Private Sub SetupData() :

Run-time error 3265 Item not found in collection
Debug highlites line "rsoutput.Fields(SCtr(rs!WeekID) = rsjipassignment"

Tried commenting that line out and the code runs to same line as above
written after Else statement.

Which text does it highlight?

(If it's any of the text 'WeekID field' then that is supposed to be part of
the comment on the previous line. You can just add a ' at the start of the
line to comment it out.)


Code runs to the SetupData query line WeekId Field and returns a compile
error "Invalid Use of Property" :

Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where the
WeekID Field
'hasn't been completed yet
.FindFirst ("[Staff]='" + CStr(rs!StaffID) + _
"' and isnull([" + CStr(rs!WeekID) + "])")



:

You need a method of running the code. The best way to do this will be
to
create a form (doesn't need to be based on any data) and add a button to
it
(..use View, Toolbox and then drag a button to the form..) Then click
on
the button and get the properties of it by View, Properties. On the
Event
tab click in the On Click event and click the three buttons on the
right.
You want the Code Builder option. That gives you an event that will run
when you click the button.

Edit that so it says...

Private Sub Command0_Click()
DoCmd.SetWarnings False 'stops you getting warnings about queries
running
DoCmd.OpenQuery "StoreSchedData"
Call SetupData 'calls the subroutine to set the data up
DoCmd.SetWarnings True 'turns warning back on
End Sub

....below that add...

Private Sub SetupData()
....all the other stuff....
End Sub

And then you can just view the form and keep your fingers crossed.

Notes... you're right that you'll need to change a couple of things
because
the StaffPK is text...

DatesCT will now need to be...

TRANSFORM Max('x') AS Expr1
SELECT 'x' AS Staff
FROM Weeks
GROUP BY 'x'
PIVOT [Weeks].[WeekID];

StoreSchedDate...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>"x"));

Update a line in the SetupData routine to be...

..FindFirst ("[Staff]='" + CStr(rs!staffid) + _
"' and isnull([" + CStr(rs!WeekID) + "])")


Query DatesCT; query 2 which I have name StoreSchedData and query
ReportData
appear to function as expected.

ReportData query generates a table showing the WeekID; StaffID and
JIPAssignment.

However; I am confused about the code used to "take this dta and push
it
into the ReportBasis table". Not sure if this code is supposed to
reside
in
yet another query or added to ReportData query. Attempted copying the
code
into a new query and running it with the Access error "SQL Error -
expecting
INPUT, DELETE ...."
Guess you can tell by now I am far from an Access programmer.

Incidentally, I have noted an error in my original comments about the
structure of the Staff table. The StaffID PK is not actually an
autonumber;
but rather a three letter index. Doesn't appear to affect your
suggestions
to date but the reference to the numeric 1 might have to be altered.



:

Now how did I know you were going to say that?

Right. I've got most of it sorted out I think. Not quite polished
off
but
let's see what you think so far. Again, this really is quite nasty.

Your problem is, pretty much, that you're attempting to take a
normalised
set of data and push it back into a completely non-normalised
format.
Nothing wrong with that, but it does make things complicated.

I don't think there's any way of getting at what you're after
without
some
serious playing around with the data. It might be possible to do it
by
using subreports but I think that looks at least as messy.

So...

First thing that I've done is set up a query that I've called
DatesCT.
SQL
is

TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP BY 1
PIVOT Weeks.WeekID;

....basically using a crosstab query to create a structure with
weeks as
column headings.

Next...another query that is a make table in order to permanently
create
a
structure that I'm going to use to store the data...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));

Note that I want the Staff field (which I really should have called
StaffID
again) to be numeric (so that I can put StaffIDs into it) which is
where
the
"1" in DatesCT comes from - to ensure that it comes out that way.
Similarly, I want the data type of the 1, 2, 3 fields to be text (so
that I
can drop the names of jobs into them) - hence the Max('x'). The

criterion is just so that the existing row doesn't get added to the
new
table.

OK. Next bit. Take the normalised version of your data and push it
into
this new table. To do this I have another query called
ReportData...

SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID,
Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;

To take this data and push it into the ReportBasis table I've needed
to
resort to code...

Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where
the
WeekID field
'hasn't been completed yet
.FindFirst ("[Staff]=" + CStr(rs!staffid) + _
" and isnull([" + CStr(rs!WeekID) + "])")
'If there isn't one, then we need a new row
If .NoMatch Then
'and complete the information
rsoutput.AddNew
rsoutput!Staff = rs!staffid
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
Else
'otherwise, just fill in the blank
rsoutput.Edit
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
End If
End With
'and move to the next record
rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub

You could obviously apply some kind of date criteria to this process
as
you
require.

You can now create a report based on this new table... but...

The need to keep the flexibility to print out the report for 52
weeks is
the
last complication, and the one that I haven't dug into in detail as
yet.
Obviously, there's no way in the world you're going to be able to
fit 52
weeks across a single piece of paper so...

My idea would be to create the report with the maximum number of
fields
across the page... say five. Given an outside parameter - i.e. the

first
week number required in the report - then you can do something like
this
in
the Open event of the report...

Me.[1_label].Caption = 'look up the outside number in the weeks
table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by default,
using
my
methodology) is called 1 but you can remap it to something different
by
doing this.

I think you should, therefore, be able to loop through values in a
form..

For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next

....and each time the report opens it references
forms!formname!whatever
to
see the first week it should be working with.

Simple, isn't it?

We do seem to have got a little way away from where this thread
started
out,
but I think it's going to be tricky to come up with an automated
solution no
matter how you try and do it (if you use the pivottable approach,
then
that's going to be equally useless when you have 52 weeks to cope
with).
The simple approach would be to just push the Excel data out into a
spreadsheet, or just create a new spreadsheet linked in to the
database,
but
that's always going to require the user to be able to manipulate the
spreadsheet in order to get the required output.

I'm going for a nice lie down now anyway....



The selection of the weeks needs to be flexible. Sometimes the
user
will
only want to print 2 weeks, another time three weeks, another time
four
weeks
and occasionally the scheduling committee may want to print 52
weeks
in
advance.

 

[Rick]

Opps! Think I might have discovered the problem. Please stand by.

Yes, exactly as copied from code window:

rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment

That's...

rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment

....correct?

Sorry, yes, the comment marker was dropped when pasting the code.

Ran code again with the following error in Private Sub SetupData() :

Run-time error 3265 Item not found in collection
Debug highlites line "rsoutput.Fields(SCtr(rs!WeekID) = rsjipassignment"

Tried commenting that line out and the code runs to same line as above
written after Else statement.

:

Which text does it highlight?

(If it's any of the text 'WeekID field' then that is supposed to be part of
the comment on the previous line. You can just add a ' at the start of the
line to comment it out.)


Code runs to the SetupData query line WeekId Field and returns a compile
error "Invalid Use of Property" :

Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where the
WeekID Field
'hasn't been completed yet
.FindFirst ("[Staff]='" + CStr(rs!StaffID) + _
"' and isnull([" + CStr(rs!WeekID) + "])")



:

You need a method of running the code. The best way to do this will be
to
create a form (doesn't need to be based on any data) and add a button to
it
(..use View, Toolbox and then drag a button to the form..) Then click
on
the button and get the properties of it by View, Properties. On the
Event
tab click in the On Click event and click the three buttons on the
right.
You want the Code Builder option. That gives you an event that will run
when you click the button.

Edit that so it says...

Private Sub Command0_Click()
DoCmd.SetWarnings False 'stops you getting warnings about queries
running
DoCmd.OpenQuery "StoreSchedData"
Call SetupData 'calls the subroutine to set the data up
DoCmd.SetWarnings True 'turns warning back on
End Sub

....below that add...

Private Sub SetupData()
....all the other stuff....
End Sub

And then you can just view the form and keep your fingers crossed.

Notes... you're right that you'll need to change a couple of things
because
the StaffPK is text...

DatesCT will now need to be...

TRANSFORM Max('x') AS Expr1
SELECT 'x' AS Staff
FROM Weeks
GROUP BY 'x'
PIVOT [Weeks].[WeekID];

StoreSchedDate...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>"x"));

Update a line in the SetupData routine to be...

..FindFirst ("[Staff]='" + CStr(rs!staffid) + _
"' and isnull([" + CStr(rs!WeekID) + "])")


Query DatesCT; query 2 which I have name StoreSchedData and query
ReportData
appear to function as expected.

ReportData query generates a table showing the WeekID; StaffID and
JIPAssignment.

However; I am confused about the code used to "take this dta and push
it
into the ReportBasis table". Not sure if this code is supposed to
reside
in
yet another query or added to ReportData query. Attempted copying the
code
into a new query and running it with the Access error "SQL Error -
expecting
INPUT, DELETE ...."
Guess you can tell by now I am far from an Access programmer.

Incidentally, I have noted an error in my original comments about the
structure of the Staff table. The StaffID PK is not actually an
autonumber;
but rather a three letter index. Doesn't appear to affect your
suggestions
to date but the reference to the numeric 1 might have to be altered.



:

Now how did I know you were going to say that?

Right. I've got most of it sorted out I think. Not quite polished
off
but
let's see what you think so far. Again, this really is quite nasty.

Your problem is, pretty much, that you're attempting to take a
normalised
set of data and push it back into a completely non-normalised
format.
Nothing wrong with that, but it does make things complicated.

I don't think there's any way of getting at what you're after
without
some
serious playing around with the data. It might be possible to do it
by
using subreports but I think that looks at least as messy.

So...

First thing that I've done is set up a query that I've called
DatesCT.
SQL
is

TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP BY 1
PIVOT Weeks.WeekID;

....basically using a crosstab query to create a structure with
weeks as
column headings.

Next...another query that is a make table in order to permanently
create
a
structure that I'm going to use to store the data...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));

Note that I want the Staff field (which I really should have called
StaffID
again) to be numeric (so that I can put StaffIDs into it) which is
where
the
"1" in DatesCT comes from - to ensure that it comes out that way.
Similarly, I want the data type of the 1, 2, 3 fields to be text (so
that I
can drop the names of jobs into them) - hence the Max('x'). The

criterion is just so that the existing row doesn't get added to the
new
table.

OK. Next bit. Take the normalised version of your data and push it
into
this new table. To do this I have another query called
ReportData...

SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID,
Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;

To take this data and push it into the ReportBasis table I've needed
to
resort to code...

Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where
the
WeekID field
'hasn't been completed yet
.FindFirst ("[Staff]=" + CStr(rs!staffid) + _
" and isnull([" + CStr(rs!WeekID) + "])")
'If there isn't one, then we need a new row
If .NoMatch Then
'and complete the information
rsoutput.AddNew
rsoutput!Staff = rs!staffid
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
Else
'otherwise, just fill in the blank
rsoutput.Edit
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
End If
End With
'and move to the next record
rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub

You could obviously apply some kind of date criteria to this process
as
you
require.

You can now create a report based on this new table... but...

The need to keep the flexibility to print out the report for 52
weeks is
the
last complication, and the one that I haven't dug into in detail as
yet.
Obviously, there's no way in the world you're going to be able to
fit 52
weeks across a single piece of paper so...

My idea would be to create the report with the maximum number of
fields
across the page... say five. Given an outside parameter - i.e. the

first
week number required in the report - then you can do something like
this
in
the Open event of the report...

Me.[1_label].Caption = 'look up the outside number in the weeks
table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by default,
using
my
methodology) is called 1 but you can remap it to something different
by
doing this.

I think you should, therefore, be able to loop through values in a
form..

For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next

....and each time the report opens it references
forms!formname!whatever
to
see the first week it should be working with.

Simple, isn't it?

We do seem to have got a little way away from where this thread
started
out,
but I think it's going to be tricky to come up with an automated
solution no
matter how you try and do it (if you use the pivottable approach,
then
that's going to be equally useless when you have 52 weeks to cope
with).
The simple approach would be to just push the Excel data out into a
spreadsheet, or just create a new spreadsheet linked in to the
database,
but
that's always going to require the user to be able to manipulate the
spreadsheet in order to get the required output.

I'm going for a nice lie down now anyway....



The selection of the weeks needs to be flexible. Sometimes the
user
will
only want to print 2 weeks, another time three weeks, another time
four
weeks
and occasionally the scheduling committee may want to print 52
weeks
in
advance.

 

[Rick]

Corrected my naming convention and the code generates the ReportBasis table
as follows:
Staff 34 35 36
AJB XYZ Company Brown's Oil Co. Pictures Framed Ltd.
KAR All About Sails Willow Candies
MAJ Hardcastle Const Super Stores Fine Furniture Refinishing
SST Barns Pet Store Ace Towing Co.

Appear to be getting there.

However; require two other pieces of data in addition to the JIPAssignment
to be in the ReportBasis table for the final report. Require Hours left and
the Date In.
Have added the fields to the ReportData query and confirmed their presence
when running the query. How do I get them into the ReportBasis table?

Opps! Think I might have discovered the problem. Please stand by.

Yes, exactly as copied from code window:

rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment

That's...

rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment

....correct?


Sorry, yes, the comment marker was dropped when pasting the code.

Ran code again with the following error in Private Sub SetupData() :

Run-time error 3265 Item not found in collection
Debug highlites line "rsoutput.Fields(SCtr(rs!WeekID) = rsjipassignment"

Tried commenting that line out and the code runs to same line as above
written after Else statement.

:

Which text does it highlight?

(If it's any of the text 'WeekID field' then that is supposed to be
part of
the comment on the previous line. You can just add a ' at the start of
the
line to comment it out.)


Code runs to the SetupData query line WeekId Field and returns a
compile
error "Invalid Use of Property" :

Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where
the
WeekID Field
'hasn't been completed yet
.FindFirst ("[Staff]='" + CStr(rs!StaffID) + _
"' and isnull([" + CStr(rs!WeekID) + "])")



:

You need a method of running the code. The best way to do this will
be
to
create a form (doesn't need to be based on any data) and add a
button to
it
(..use View, Toolbox and then drag a button to the form..) Then
click
on
the button and get the properties of it by View, Properties. On the
Event
tab click in the On Click event and click the three buttons on the
right.
You want the Code Builder option. That gives you an event that will
run
when you click the button.

Edit that so it says...

Private Sub Command0_Click()
DoCmd.SetWarnings False 'stops you getting warnings about queries
running
DoCmd.OpenQuery "StoreSchedData"
Call SetupData 'calls the subroutine to set the data up
DoCmd.SetWarnings True 'turns warning back on
End Sub

....below that add...

Private Sub SetupData()
....all the other stuff....
End Sub

And then you can just view the form and keep your fingers crossed.

Notes... you're right that you'll need to change a couple of things
because
the StaffPK is text...

DatesCT will now need to be...

TRANSFORM Max('x') AS Expr1
SELECT 'x' AS Staff
FROM Weeks
GROUP BY 'x'
PIVOT [Weeks].[WeekID];

StoreSchedDate...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>"x"));

Update a line in the SetupData routine to be...

..FindFirst ("[Staff]='" + CStr(rs!staffid) + _
"' and isnull([" + CStr(rs!WeekID) + "])")


Query DatesCT; query 2 which I have name StoreSchedData and query
ReportData
appear to function as expected.

ReportData query generates a table showing the WeekID; StaffID and
JIPAssignment.

However; I am confused about the code used to "take this dta and
push
it
into the ReportBasis table". Not sure if this code is supposed to
reside
in
yet another query or added to ReportData query. Attempted copying
the
code
into a new query and running it with the Access error "SQL Error -
expecting
INPUT, DELETE ...."
Guess you can tell by now I am far from an Access programmer.

Incidentally, I have noted an error in my original comments about
the
structure of the Staff table. The StaffID PK is not actually an
autonumber;
but rather a three letter index. Doesn't appear to affect your
suggestions
to date but the reference to the numeric 1 might have to be
altered.



:

Now how did I know you were going to say that?

Right. I've got most of it sorted out I think. Not quite
polished
off
but
let's see what you think so far. Again, this really is quite
nasty.

Your problem is, pretty much, that you're attempting to take a
normalised
set of data and push it back into a completely non-normalised
format.
Nothing wrong with that, but it does make things complicated.

I don't think there's any way of getting at what you're after
without
some
serious playing around with the data. It might be possible to
do it
by
using subreports but I think that looks at least as messy.

So...

First thing that I've done is set up a query that I've called
DatesCT.
SQL
is

TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP
BY 1
PIVOT Weeks.WeekID;

....basically using a crosstab query to create a structure with
weeks as
column headings.

Next...another query that is a make table in order to
permanently
create
a
structure that I'm going to use to store the data...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));

Note that I want the Staff field (which I really should have
called
StaffID
again) to be numeric (so that I can put StaffIDs into it) which
is
where
the
"1" in DatesCT comes from - to ensure that it comes out that
way.
Similarly, I want the data type of the 1, 2, 3 fields to be text
(so
that I
can drop the names of jobs into them) - hence the Max('x'). The
<>1
criterion is just so that the existing row doesn't get added to
the
new
table.

OK. Next bit. Take the normalised version of your data and
push it
into
this new table. To do this I have another query called
ReportData...

SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID,
Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned
ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON
Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;

To take this data and push it into the ReportBasis table I've
needed
to
resort to code...

Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but
where
the
WeekID field
'hasn't been completed yet
.FindFirst ("[Staff]=" + CStr(rs!staffid) + _
" and isnull([" + CStr(rs!WeekID) + "])")
'If there isn't one, then we need a new row
If .NoMatch Then
'and complete the information
rsoutput.AddNew
rsoutput!Staff = rs!staffid
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
Else
'otherwise, just fill in the blank
rsoutput.Edit
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
End If
End With
'and move to the next record
rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub

You could obviously apply some kind of date criteria to this
process
as
you
require.

You can now create a report based on this new table... but...

The need to keep the flexibility to print out the report for 52
weeks is
the
last complication, and the one that I haven't dug into in detail
as
yet.
Obviously, there's no way in the world you're going to be able
to
fit 52
weeks across a single piece of paper so...

My idea would be to create the report with the maximum number of
fields
across the page... say five. Given an outside parameter - i.e.
the

first
week number required in the report - then you can do something
like
this
in
the Open event of the report...

Me.[1_label].Caption = 'look up the outside number in the weeks
table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by
default,
using
my
methodology) is called 1 but you can remap it to something
different
by
doing this.

I think you should, therefore, be able to loop through values in
a
form..

For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next

....and each time the report opens it references
forms!formname!whatever
to
see the first week it should be working with.

Simple, isn't it?

We do seem to have got a little way away from where this thread
started
out,
but I think it's going to be tricky to come up with an automated
solution no
matter how you try and do it (if you use the pivottable
approach,
then
that's going to be equally useless when you have 52 weeks to
cope
with).
The simple approach would be to just push the Excel data out
into a
spreadsheet, or just create a new spreadsheet linked in to the
database,
but
that's always going to require the user to be able to manipulate
the
spreadsheet in order to get the required output.

I'm going for a nice lie down now anyway....



The selection of the weeks needs to be flexible. Sometimes
the
user
will
only want to print 2 weeks, another time three weeks, another
time
four
weeks
and occasionally the scheduling committee may want to print 52
weeks
in
advance.

 

[Rob Oldfield]

Good.

Which table(s) do 'Hours left' and 'Date in' come from?

Corrected my naming convention and the code generates the ReportBasis table
as follows:
Staff 34 35 36
AJB XYZ Company Brown's Oil Co. Pictures Framed Ltd.
KAR All About Sails Willow Candies
MAJ Hardcastle Const Super Stores Fine Furniture Refinishing
SST Barns Pet Store Ace Towing Co.

Appear to be getting there.

However; require two other pieces of data in addition to the JIPAssignment
to be in the ReportBasis table for the final report. Require Hours left and
the Date In.
Have added the fields to the ReportData query and confirmed their presence
when running the query. How do I get them into the ReportBasis table?

Opps! Think I might have discovered the problem. Please stand by.

Yes, exactly as copied from code window:

rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment


:

That's...

rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment

....correct?


Sorry, yes, the comment marker was dropped when pasting the code.

Ran code again with the following error in Private Sub SetupData() :

Run-time error 3265 Item not found in collection
Debug highlites line "rsoutput.Fields(SCtr(rs!WeekID) = rsjipassignment"

Tried commenting that line out and the code runs to same line as above
written after Else statement.

:

Which text does it highlight?

(If it's any of the text 'WeekID field' then that is supposed to be
part of
the comment on the previous line. You can just add a ' at the start of
the
line to comment it out.)


Code runs to the SetupData query line WeekId Field and returns a
compile
error "Invalid Use of Property" :

Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where
the
WeekID Field
'hasn't been completed yet
.FindFirst ("[Staff]='" + CStr(rs!StaffID) + _
"' and isnull([" + CStr(rs!WeekID) + "])")



:

You need a method of running the code. The best way to do this will
be
to
create a form (doesn't need to be based on any data) and add a
button to
it
(..use View, Toolbox and then drag a button to the form..) Then
click
on
the button and get the properties of it by View, Properties. On the
Event
tab click in the On Click event and click the three buttons on the
right.
You want the Code Builder option. That gives you an event that will
run
when you click the button.

Edit that so it says...

Private Sub Command0_Click()
DoCmd.SetWarnings False 'stops you getting warnings about queries
running
DoCmd.OpenQuery "StoreSchedData"
Call SetupData 'calls the subroutine to set the data up
DoCmd.SetWarnings True 'turns warning back on
End Sub

....below that add...

Private Sub SetupData()
....all the other stuff....
End Sub

And then you can just view the form and keep your fingers crossed.

Notes... you're right that you'll need to change a couple of things
because
the StaffPK is text...

DatesCT will now need to be...

TRANSFORM Max('x') AS Expr1
SELECT 'x' AS Staff
FROM Weeks
GROUP BY 'x'
PIVOT [Weeks].[WeekID];

StoreSchedDate...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>"x"));

Update a line in the SetupData routine to be...

..FindFirst ("[Staff]='" + CStr(rs!staffid) + _
"' and isnull([" + CStr(rs!WeekID) + "])")


Query DatesCT; query 2 which I have name StoreSchedData and query
ReportData
appear to function as expected.

ReportData query generates a table showing the WeekID; StaffID and
JIPAssignment.

However; I am confused about the code used to "take this dta and
push
it
into the ReportBasis table". Not sure if this code is supposed to
reside
in
yet another query or added to ReportData query. Attempted copying
the
code
into a new query and running it with the Access error "SQL Error -
expecting
INPUT, DELETE ...."
Guess you can tell by now I am far from an Access programmer.

Incidentally, I have noted an error in my original comments about
the
structure of the Staff table. The StaffID PK is not actually an
autonumber;
but rather a three letter index. Doesn't appear to affect your
suggestions
to date but the reference to the numeric 1 might have to be
altered.



:

Now how did I know you were going to say that?

Right. I've got most of it sorted out I think. Not quite
polished
off
but
let's see what you think so far. Again, this really is quite
nasty.

Your problem is, pretty much, that you're attempting to take a
normalised
set of data and push it back into a completely non-normalised
format.
Nothing wrong with that, but it does make things complicated.

I don't think there's any way of getting at what you're after
without
some
serious playing around with the data. It might be possible to
do it
by
using subreports but I think that looks at least as messy.

So...

First thing that I've done is set up a query that I've called
DatesCT.
SQL
is

TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP
BY 1
PIVOT Weeks.WeekID;

....basically using a crosstab query to create a structure with
weeks as
column headings.

Next...another query that is a make table in order to
permanently
create
a
structure that I'm going to use to store the data...

SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));

Note that I want the Staff field (which I really should have
called
StaffID
again) to be numeric (so that I can put StaffIDs into it) which
is
where
the
"1" in DatesCT comes from - to ensure that it comes out that
way.
Similarly, I want the data type of the 1, 2, 3 fields to be text
(so
that I
can drop the names of jobs into them) - hence the Max('x'). The
<>1
criterion is just so that the existing row doesn't get added to
the
new
table.

OK. Next bit. Take the normalised version of your data and
push it
into
this new table. To do this I have another query called
ReportData...

SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID,
Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned
ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON
Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;

To take this data and push it into the ReportBasis table I've
needed
to
resort to code...

Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but
where
the
WeekID field
'hasn't been completed yet
.FindFirst ("[Staff]=" + CStr(rs!staffid) + _
" and isnull([" + CStr(rs!WeekID) + "])")
'If there isn't one, then we need a new row
If .NoMatch Then
'and complete the information
rsoutput.AddNew
rsoutput!Staff = rs!staffid
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
Else
'otherwise, just fill in the blank
rsoutput.Edit
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
End If
End With
'and move to the next record
rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub

You could obviously apply some kind of date criteria to this
process
as
you
require.

You can now create a report based on this new table... but...

The need to keep the flexibility to print out the report for 52
weeks is
the
last complication, and the one that I haven't dug into in detail
as
yet.
Obviously, there's no way in the world you're going to be able
to
fit 52
weeks across a single piece of paper so...

My idea would be to create the report with the maximum number of
fields
across the page... say five. Given an outside parameter - i.e.
the

first
week number required in the report - then you can do something
like
this
in
the Open event of the report...

Me.[1_label].Caption = 'look up the outside number in the weeks
table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by
default,
using
my
methodology) is called 1 but you can remap it to something
different
by
doing this.

I think you should, therefore, be able to loop through values in
a
form..

For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next

....and each time the report opens it references
forms!formname!whatever
to
see the first week it should be working with.

Simple, isn't it?

We do seem to have got a little way away from where this thread
started
out,
but I think it's going to be tricky to come up with an automated
solution no
matter how you try and do it (if you use the pivottable
approach,
then
that's going to be equally useless when you have 52 weeks to
cope
with).
The simple approach would be to just push the Excel data out
into a
spreadsheet, or just create a new spreadsheet linked in to the
database,
but
that's always going to require the user to be able to manipulate
the
spreadsheet in order to get the required output.

I'm going for a nice lie down now anyway....



The selection of the weeks needs to be flexible. Sometimes
the
user
will
only want to print 2 weeks, another time three weeks, another
time
four
weeks
and occasionally the scheduling committee may want to print 52
weeks
in
advance.