Report With Sub Reports VERY SLOW

[James Stephens]

I have a schedule report that I have built that runs too slow (usually takes
up to 20 minutes or longer to run). I am trying to find a way to make this
faster but I can't really figure it out.

The data in the table is layed out as follows: Employee SSN, Date, Work
Area, Start Time, End Time

The trick is an employee (about 300 total employees) may have multiple
shifts on a single day, and I need the report to reflect this. The report I
have is a two week schedule. It is one main report with 14 subreport (one
for each day). This report has sorting by Employee SSN with a group footer so
that each employee is a different group (this is so each of the 14 sub
reports can expand as needed and for formatting).

Basically each subreport is set for showing only one shift but can expand to
show multiple.

There is one main query for the report that pulls all the data for a two
week section and filters it by job position. Then there are 14 queries based
off of that one, one for each of the 14 days on the report.

So the main reports record source is a query of all the Employees SSN and
each of the sub reports is linked by this field. Then the main report is
grouped by employee so that the sub reports which by default are only tall
enough to show one record can expand to show more for a given day and the
formatting expands to keep all of these showing with the same person so the
whole line for the two weeks expands to the largest record for an individual.

I have played with cross-tab queries but cant seem to find anyway to make it
work any faster (I preface that with I don’t have much experience with these
type of queries so I may be doing something wrong)

Any ideas on how to cut down that time from 20 minutes plus would be great.
I know it's not the network because no other report takes anywhere near this
long. I have another report that runs on this table that shows the total
number of people working each hour for each work area (no names just a
number) and this report only takes about 15 seconds.

Any help would be great,

Jim Stephens

 

[SA]

Jim:

Your post doesn't give a lot of guidance as to what might be the problem.
do the queries for the subs run fine themselves?

There are some general tips on making sure reports and queries run faster
at:

http://www.granite.ab.ca/access/performancefaq.htm

Also, if you have any VB code that runs in the On Format event of your
reports or subs wrap your code with this:

If FormatCount = 1 Then
'Your code here
End if

Hope that helps

 

[James Stephens]

I can try to explain it a little better. I am trying to take employee
schedule information and lay it our like so

Location 1
Name 2/1/2006 2/2/2006 2/3/2006
John Doe 12:00-14:00 12:00-18:00 12:00-14:00
16:00-18:00 16:00-18:00

19:00-20:00

Jane Smith 12:00-17:00 12:00-17:00

etc....


The way I do this right now is with 14 subreports, one for each day and they
are linked by the employees name. This way the report can expand when there
is more than one scheduled shift on a single day. My quess is that is is
slow because I use 14 subreports (over 20 minutes for this report to run). I
have look at trying to use a crosstab query but can't seem to make it work.
I am looking for something that will be quicker but I have no idea what to
try.

Thanks,

Jim

 

[Duane Hookom]

It's a bit difficult to determine your exact table structure and
specifications however try search Google Groups for:
relative dates mth0 group:*Access.Reports* author:hookom

Rather than columns of months, you could use dates. It also looks like you
are returning multiple values per date per employee. This can be
accomplished using the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[James Stephens]

The structure of the data is as follows:

Emp_Name,Work_Location,Work_Date,Work_Time_Frame

And yes there can and frequently will be more than one record for an
employee on one day. If that changes your thoughts on anything let me know.
I am going to look into the ideas you gave me.

 

[Duane Hookom]

The solution I suggested should work with your structure.

 

[James Stephens]

From the link you suggested I think the cross tab model of yours will work, I
am trying to implement it but have ran into a snag. It is tripping on the
SQL in the function. Here is what I have

strSQL = "SELECT SC_Schedule_Print_2.T_Time From SC_Schedule_Print_2
WHERE (((SC_Schedule_Print_2.SSN) = """ & pstrEmployee & """) AND
((SC_Schedule_Print_2.Location_Schedule_Formal)= """ & pstrLocation & """)
AND ((SC_Schedule_Print_2.Work_Date)= #" & pstrDate & "#));"

I have three values to test for equal: Employee, Work Location, and Date.
When I run this I get: Too few parameters. Expect 2.

Any ideas,

Thanks

Jim

 

[James Stephens]

The only thing I can think of is the Cross Tab Query I have has to have two
parameters listed for it to run:

[Forms]![Autpen subform]![Location_View] Text
[Forms]![SC_Schedule_Print frm]![TW_Select] Long Interger

Is this where my issue is occuring ?

 

[Duane Hookom]

With crosstabs, you almost always have to set the data types of your
parameters. Are you creating the SQL on the fly (in code) or is this a saved
query? Can you share your SQL/code?

--
Duane Hookom
MS Access MVP
--

The only thing I can think of is the Cross Tab Query I have has to have
two
parameters listed for it to run:

[Forms]![Autpen subform]![Location_View] Text
[Forms]![SC_Schedule_Print frm]![TW_Select] Long Interger

Is this where my issue is occuring ?

The solution I suggested should work with your structure.

 

[James Stephens]

Here is the SQL of my cross tab query:

PARAMETERS [Forms]![Autpen subform]![Airport_View] Text ( 255 ),
[Forms]![SC_Schedule_Print frm]![PP_Select] Long;
TRANSFORM
First(GetScheduleTimes([SC_Schedule_Print_2]![SSN],[SC_Schedule_Print_2]![Location_Schedule_Formal],[SC_Schedule_Print_2]![Work_Date])) AS Time_Frames
SELECT SC_Schedule_Print_2.SSN, SC_Schedule_Print_2.Location_Schedule_Formal
FROM SC_Schedule_Print_2 INNER JOIN [Gen_Calendar tbl] ON
SC_Schedule_Print_2.Work_Date = [Gen_Calendar tbl].Gen_Date
GROUP BY SC_Schedule_Print_2.SSN, SC_Schedule_Print_2.Location_Schedule_Formal
PIVOT [Gen_Calendar tbl].Date_Schedule_Code;

The query in the function like in your example is this:

SqlStr = "SELECT [SC_Schedule_Print_2].[T_Time] From [SC_Schedule_Print_2]
WHERE ([SC_Schedule_Print_2].[SSN] = '" & pstrEmployee & "' AND
[SC_Schedule_Print_2].[Location_Schedule_Formal]= '" & pstrLocation & "' AND
[SC_Schedule_Print_2].[Work_Date]= #" & pstrDate & "#);"

Thanks for the help on this;

Jim

With crosstabs, you almost always have to set the data types of your
parameters. Are you creating the SQL on the fly (in code) or is this a saved
query? Can you share your SQL/code?

--
Duane Hookom
MS Access MVP
--

The only thing I can think of is the Cross Tab Query I have has to have
two
parameters listed for it to run:

[Forms]![Autpen subform]![Location_View] Text
[Forms]![SC_Schedule_Print frm]![TW_Select] Long Interger

Is this where my issue is occuring ?

The solution I suggested should work with your structure.

--
Duane Hookom
MS Access MVP
--

message
The structure of the data is as follows:

Emp_Name,Work_Location,Work_Date,Work_Time_Frame

And yes there can and frequently will be more than one record for an
employee on one day. If that changes your thoughts on anything let me
know.
I am going to look into the ideas you gave me.

:

It's a bit difficult to determine your exact table structure and
specifications however try search Google Groups for:
relative dates mth0 group:*Access.Reports* author:hookom

Rather than columns of months, you could use dates. It also looks like
you
are returning multiple values per date per employee. This can be
accomplished using the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP
--

message
I can try to explain it a little better. I am trying to take
employee
schedule information and lay it our like so

Location 1
Name 2/1/2006 2/2/2006 2/3/2006
John Doe 12:00-14:00 12:00-18:00 12:00-14:00
16:00-18:00
16:00-18:00

19:00-20:00

Jane Smith 12:00-17:00
12:00-17:00

etc....


The way I do this right now is with 14 subreports, one for each day
and
they
are linked by the employees name. This way the report can expand
when
there
is more than one scheduled shift on a single day. My quess is that
is
is
slow because I use 14 subreports (over 20 minutes for this report to
run).
I
have look at trying to use a crosstab query but can't seem to make
it
work.
I am looking for something that will be quicker but I have no idea
what
to
try.

Thanks,

Jim

:

Jim:

Your post doesn't give a lot of guidance as to what might be the
problem.
do the queries for the subs run fine themselves?

There are some general tips on making sure reports and queries run
faster
at:

http://www.granite.ab.ca/access/performancefaq.htm

Also, if you have any VB code that runs in the On Format event of
your
reports or subs wrap your code with this:

If FormatCount = 1 Then
'Your code here
End if

Hope that helps
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

message
I have a schedule report that I have built that runs too slow
(usually
takes
up to 20 minutes or longer to run). I am trying to find a way to
make
this
faster but I can't really figure it out.

The data in the table is layed out as follows: Employee SSN,
Date,
Work
Area, Start Time, End Time

The trick is an employee (about 300 total employees) may have
multiple
shifts on a single day, and I need the report to reflect this.
The
report
I
have is a two week schedule. It is one main report with 14
subreport
(one
for each day). This report has sorting by Employee SSN with a
group
footer
so
that each employee is a different group (this is so each of the
14
sub
reports can expand as needed and for formatting).

Basically each subreport is set for showing only one shift but
can
expand
to
show multiple.

There is one main query for the report that pulls all the data
for a
two
week section and filters it by job position. Then there are 14
queries
based
off of that one, one for each of the 14 days on the report.

So the main reports record source is a query of all the Employees
SSN
and
each of the sub reports is linked by this field. Then the main
report
is
grouped by employee so that the sub reports which by default are
only
tall
enough to show one record can expand to show more for a given day
and
the
formatting expands to keep all of these showing with the same
person
so
the
whole line for the two weeks expands to the largest record for an
individual.

I have played with cross-tab queries but cant seem to find anyway
to
make
it
work any faster (I preface that with I don't have much experience
with
these
type of queries so I may be doing something wrong)

Any ideas on how to cut down that time from 20 minutes plus would
be
great.
I know it's not the network because no other report takes
anywhere
near
this
long. I have another report that runs on this table that shows
the
total
number of people working each hour for each work area (no names
just
a
number) and this report only takes about 15 seconds.

Any help would be great,

Jim Stephens

 

[James Stephens]

Also I forgot to add, I am creating it on the fly. I tried putting it into a
saved query and I was able to get it to run but it was very slow as it has to
save a query many hundreds or for a full schedule thousands of times. I am
wanting to keep it in code created on the fly.

With crosstabs, you almost always have to set the data types of your
parameters. Are you creating the SQL on the fly (in code) or is this a saved
query? Can you share your SQL/code?

--
Duane Hookom
MS Access MVP
--

The only thing I can think of is the Cross Tab Query I have has to have
two
parameters listed for it to run:

[Forms]![Autpen subform]![Location_View] Text
[Forms]![SC_Schedule_Print frm]![TW_Select] Long Interger

Is this where my issue is occuring ?

The solution I suggested should work with your structure.

--
Duane Hookom
MS Access MVP
--

message
The structure of the data is as follows:

Emp_Name,Work_Location,Work_Date,Work_Time_Frame

And yes there can and frequently will be more than one record for an
employee on one day. If that changes your thoughts on anything let me
know.
I am going to look into the ideas you gave me.

:

It's a bit difficult to determine your exact table structure and
specifications however try search Google Groups for:
relative dates mth0 group:*Access.Reports* author:hookom

Rather than columns of months, you could use dates. It also looks like
you
are returning multiple values per date per employee. This can be
accomplished using the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP
--

message
I can try to explain it a little better. I am trying to take
employee
schedule information and lay it our like so

Location 1
Name 2/1/2006 2/2/2006 2/3/2006
John Doe 12:00-14:00 12:00-18:00 12:00-14:00
16:00-18:00
16:00-18:00

19:00-20:00

Jane Smith 12:00-17:00
12:00-17:00

etc....


The way I do this right now is with 14 subreports, one for each day
and
they
are linked by the employees name. This way the report can expand
when
there
is more than one scheduled shift on a single day. My quess is that
is
is
slow because I use 14 subreports (over 20 minutes for this report to
run).
I
have look at trying to use a crosstab query but can't seem to make
it
work.
I am looking for something that will be quicker but I have no idea
what
to
try.

Thanks,

Jim

:

Jim:

Your post doesn't give a lot of guidance as to what might be the
problem.
do the queries for the subs run fine themselves?

There are some general tips on making sure reports and queries run
faster
at:

http://www.granite.ab.ca/access/performancefaq.htm

Also, if you have any VB code that runs in the On Format event of
your
reports or subs wrap your code with this:

If FormatCount = 1 Then
'Your code here
End if

Hope that helps
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

message
I have a schedule report that I have built that runs too slow
(usually
takes
up to 20 minutes or longer to run). I am trying to find a way to
make
this
faster but I can't really figure it out.

The data in the table is layed out as follows: Employee SSN,
Date,
Work
Area, Start Time, End Time

The trick is an employee (about 300 total employees) may have
multiple
shifts on a single day, and I need the report to reflect this.
The
report
I
have is a two week schedule. It is one main report with 14
subreport
(one
for each day). This report has sorting by Employee SSN with a
group
footer
so
that each employee is a different group (this is so each of the
14
sub
reports can expand as needed and for formatting).

Basically each subreport is set for showing only one shift but
can
expand
to
show multiple.

There is one main query for the report that pulls all the data
for a
two
week section and filters it by job position. Then there are 14
queries
based
off of that one, one for each of the 14 days on the report.

So the main reports record source is a query of all the Employees
SSN
and
each of the sub reports is linked by this field. Then the main
report
is
grouped by employee so that the sub reports which by default are
only
tall
enough to show one record can expand to show more for a given day
and
the
formatting expands to keep all of these showing with the same
person
so
the
whole line for the two weeks expands to the largest record for an
individual.

I have played with cross-tab queries but cant seem to find anyway
to
make
it
work any faster (I preface that with I don't have much experience
with
these
type of queries so I may be doing something wrong)

Any ideas on how to cut down that time from 20 minutes plus would
be
great.
I know it's not the network because no other report takes
anywhere
near
this
long. I have another report that runs on this table that shows
the
total
number of people working each hour for each work area (no names
just
a
number) and this report only takes about 15 seconds.

Any help would be great,

Jim Stephens

 

[Duane Hookom]

I don't recall seeing your code or a debug print of your sql statements.

--
Duane Hookom
MS Access MVP
--

Also I forgot to add, I am creating it on the fly. I tried putting it
into a
saved query and I was able to get it to run but it was very slow as it has
to
save a query many hundreds or for a full schedule thousands of times. I
am
wanting to keep it in code created on the fly.

With crosstabs, you almost always have to set the data types of your
parameters. Are you creating the SQL on the fly (in code) or is this a
saved
query? Can you share your SQL/code?

--
Duane Hookom
MS Access MVP
--

message

The only thing I can think of is the Cross Tab Query I have has to have
two
parameters listed for it to run:

[Forms]![Autpen subform]![Location_View] Text
[Forms]![SC_Schedule_Print frm]![TW_Select] Long Interger

Is this where my issue is occuring ?

:

The solution I suggested should work with your structure.

--
Duane Hookom
MS Access MVP
--

message
The structure of the data is as follows:

Emp_Name,Work_Location,Work_Date,Work_Time_Frame

And yes there can and frequently will be more than one record for an
employee on one day. If that changes your thoughts on anything let
me
know.
I am going to look into the ideas you gave me.

:

It's a bit difficult to determine your exact table structure and
specifications however try search Google Groups for:
relative dates mth0 group:*Access.Reports* author:hookom

Rather than columns of months, you could use dates. It also looks
like
you
are returning multiple values per date per employee. This can be
accomplished using the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP
--

message
I can try to explain it a little better. I am trying to take
employee
schedule information and lay it our like so

Location 1
Name 2/1/2006 2/2/2006
2/3/2006
John Doe 12:00-14:00 12:00-18:00 12:00-14:00
16:00-18:00
16:00-18:00

19:00-20:00

Jane Smith 12:00-17:00
12:00-17:00

etc....


The way I do this right now is with 14 subreports, one for each
day
and
they
are linked by the employees name. This way the report can expand
when
there
is more than one scheduled shift on a single day. My quess is
that
is
is
slow because I use 14 subreports (over 20 minutes for this report
to
run).
I
have look at trying to use a crosstab query but can't seem to
make
it
work.
I am looking for something that will be quicker but I have no
idea
what
to
try.

Thanks,

Jim

:

Jim:

Your post doesn't give a lot of guidance as to what might be the
problem.
do the queries for the subs run fine themselves?

There are some general tips on making sure reports and queries
run
faster
at:

http://www.granite.ab.ca/access/performancefaq.htm

Also, if you have any VB code that runs in the On Format event
of
your
reports or subs wrap your code with this:

If FormatCount = 1 Then
'Your code here
End if

Hope that helps
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

in
message
I have a schedule report that I have built that runs too slow
(usually
takes
up to 20 minutes or longer to run). I am trying to find a way
to
make
this
faster but I can't really figure it out.

The data in the table is layed out as follows: Employee SSN,
Date,
Work
Area, Start Time, End Time

The trick is an employee (about 300 total employees) may have
multiple
shifts on a single day, and I need the report to reflect this.
The
report
I
have is a two week schedule. It is one main report with 14
subreport
(one
for each day). This report has sorting by Employee SSN with a
group
footer
so
that each employee is a different group (this is so each of
the
14
sub
reports can expand as needed and for formatting).

Basically each subreport is set for showing only one shift but
can
expand
to
show multiple.

There is one main query for the report that pulls all the data
for a
two
week section and filters it by job position. Then there are
14
queries
based
off of that one, one for each of the 14 days on the report.

So the main reports record source is a query of all the
Employees
SSN
and
each of the sub reports is linked by this field. Then the
main
report
is
grouped by employee so that the sub reports which by default
are
only
tall
enough to show one record can expand to show more for a given
day
and
the
formatting expands to keep all of these showing with the same
person
so
the
whole line for the two weeks expands to the largest record for
an
individual.

I have played with cross-tab queries but cant seem to find
anyway
to
make
it
work any faster (I preface that with I don't have much
experience
with
these
type of queries so I may be doing something wrong)

Any ideas on how to cut down that time from 20 minutes plus
would
be
great.
I know it's not the network because no other report takes
anywhere
near
this
long. I have another report that runs on this table that
shows
the
total
number of people working each hour for each work area (no
names
just
a
number) and this report only takes about 15 seconds.

Any help would be great,

Jim Stephens