|
 |
access thread: looping thru records to print report...
Message #1 by "Darin Wray" <darin@r...> on Thu, 20 Dec 2001 21:35:06
|
|
Hey guys...need some direction on how to code this scenario.
I have an unbound report that has 7 subreports in it. Each subreport's
datasource is a stored proc with date and salesrep parameters. These
parameters are passed based on fields that are entered in a form. The
report works great for one rep at a time...I couldn't get it to sort/group
correctly on a report for ALL sales reps, tho. Perhaps there is something
I'm missing...anyways, I thought another alternative would be to program
some kind of For Next statement to loop thru each employee, and print
their very own sales report.
Here's what I'm thinking...in english
Probably have to pull a recordset down that has the salesreps names...
for each salesrep where salesrep is value for a stored proc parameter
print salesreport where begin date and end date are stored proc parameter.
Can someone give me a rough example on how to code this in a command
button?
Thanks,
Darin
Message #2 by "John Pace" <jpace@h...> on Thu, 20 Dec 2001 15:37:14 -0600
|
|
I don't know what other people's experience has been, but I have had nothing
but trouble when I introduced multiple subreports into a report. Usually
after hours of unproductive work trying to work between the subreports and
the main report, I have stopped and looked at my database structure.
Typically, I can improve my db structure and not have to use more than 1
subreport. There seem to be some inherent problems in too many subreports.
If I was you, I'd check how you have your tables built and see if you can't
reorganize them. You may save yourself significant headaches down the road.
John
-----Original Message-----
From: Darin Wray [mailto:darin@r...]
Sent: Thursday, December 20, 2001 9:35 PM
To: Access
Subject: [access] looping thru records to print report...
Hey guys...need some direction on how to code this scenario.
I have an unbound report that has 7 subreports in it. Each subreport's
datasource is a stored proc with date and salesrep parameters. These
parameters are passed based on fields that are entered in a form. The
report works great for one rep at a time...I couldn't get it to sort/group
correctly on a report for ALL sales reps, tho. Perhaps there is something
I'm missing...anyways, I thought another alternative would be to program
some kind of For Next statement to loop thru each employee, and print
their very own sales report.
Here's what I'm thinking...in english
Probably have to pull a recordset down that has the salesreps names...
for each salesrep where salesrep is value for a stored proc parameter
print salesreport where begin date and end date are stored proc parameter.
Can someone give me a rough example on how to code this in a command
button?
Thanks,
Darin
Message #3 by "Darin Wray" <darin@r...> on Thu, 20 Dec 2001 15:44:29 -0600
|
|
Good point...and, maybe my structure does need work. Here's the deal. The
subreports are each different in the fact they are different products we
sell. Each product has their own unique characteristics that differ from
all the others....I can't really have just one "Products/Services Sold
Table" because the necessary fields would be a high number, as well as the
null values that would be floating around. Thus, the reason I have 7 tables
of "Sold stuff" or "Services Sold"
Each subreport is calculating a salesreps total sales for that specific area
during any given dates.
Darin
----- Original Message -----
From: "John Pace" <jpace@h...>
To: "Access" <access@p...>
Sent: Thursday, December 20, 2001 3:37 PM
Subject: [access] RE: looping thru records to print report...
> I don't know what other people's experience has been, but I have had
nothing
> but trouble when I introduced multiple subreports into a report. Usually
> after hours of unproductive work trying to work between the subreports and
> the main report, I have stopped and looked at my database structure.
> Typically, I can improve my db structure and not have to use more than 1
> subreport. There seem to be some inherent problems in too many
subreports.
> If I was you, I'd check how you have your tables built and see if you
can't
> reorganize them. You may save yourself significant headaches down the
road.
>
> John
>
> -----Original Message-----
> From: Darin Wray [mailto:darin@r...]
> Sent: Thursday, December 20, 2001 9:35 PM
> To: Access
> Subject: [access] looping thru records to print report...
>
>
> Hey guys...need some direction on how to code this scenario.
>
> I have an unbound report that has 7 subreports in it. Each subreport's
> datasource is a stored proc with date and salesrep parameters. These
> parameters are passed based on fields that are entered in a form. The
> report works great for one rep at a time...I couldn't get it to sort/group
> correctly on a report for ALL sales reps, tho. Perhaps there is something
> I'm missing...anyways, I thought another alternative would be to program
> some kind of For Next statement to loop thru each employee, and print
> their very own sales report.
>
> Here's what I'm thinking...in english
>
> Probably have to pull a recordset down that has the salesreps names...
> for each salesrep where salesrep is value for a stored proc parameter
> print salesreport where begin date and end date are stored proc parameter.
>
> Can someone give me a rough example on how to code this in a command
> button?
>
> Thanks,
> Darin
>
>
>
Message #4 by "Paul McLaren" <paulmcl@t...> on Thu, 20 Dec 2001 23:26:15 -0000
|
|
The way I found to solve this problem was to use functions to populate the
text boxes in the report.
Write a function and pass any parameters to it in the report, the parameters
could be for example date ranges on a form.
This way you can write a report that is not connected to any underlying
recordset and can get data from anywhere the function cares to get it from
and do whatever calculation is necessary.
Makes formating a lot easier than trying to line up subreports and get all
the references correct.
With some careful work the functions can be reused on various reports.
I have never had a problem report creating since (or barely!)
The problem by using a underlying recordset is there has to be some sort of
common denominator across the displayed data and if there isn't then you
have to find a complicated workaround.
(I did mention solution previously but for a different problem - sorry for
repeating myself but its a fairly versatile solution!)
Regards
Paul
-----Original Message-----
From: John Pace [mailto:jpace@h...]
Sent: 20 December 2001 21:37
To: Access
Subject: [access] RE: looping thru records to print report...
I don't know what other people's experience has been, but I have had nothing
but trouble when I introduced multiple subreports into a report. Usually
after hours of unproductive work trying to work between the subreports and
the main report, I have stopped and looked at my database structure.
Typically, I can improve my db structure and not have to use more than 1
subreport. There seem to be some inherent problems in too many subreports.
If I was you, I'd check how you have your tables built and see if you can't
reorganize them. You may save yourself significant headaches down the road.
John
-----Original Message-----
From: Darin Wray [mailto:darin@r...]
Sent: Thursday, December 20, 2001 9:35 PM
To: Access
Subject: [access] looping thru records to print report...
Hey guys...need some direction on how to code this scenario.
I have an unbound report that has 7 subreports in it. Each subreport's
datasource is a stored proc with date and salesrep parameters. These
parameters are passed based on fields that are entered in a form. The
report works great for one rep at a time...I couldn't get it to sort/group
correctly on a report for ALL sales reps, tho. Perhaps there is something
I'm missing...anyways, I thought another alternative would be to program
some kind of For Next statement to loop thru each employee, and print
their very own sales report.
Here's what I'm thinking...in english
Probably have to pull a recordset down that has the salesreps names...
for each salesrep where salesrep is value for a stored proc parameter
print salesreport where begin date and end date are stored proc parameter.
Can someone give me a rough example on how to code this in a command
button?
Thanks,
Darin
Message #5 by joe.dunn@c... on Fri, 21 Dec 2001 15:16:23 +0000
|
|
You could:
empty a table (PROCESS_TABLE) with 2 columns (SALES_REP, Text, ?characters)
and DONE(Yes/No, default set to No)
populate a table with all the reps
run through process doing first rep with DONE = No and then DONE to Yes
keep going through process if number of reps with DONE = No > 0 (i.e. as
long as there are reps to do)
Keep your existing reports and copy - run the new copy in a cycle doing
above whenever you need to do ALL
The key piece of code could be
DIM SELECTED_REP as string
SELECTED_REP = DFIRST("SALES_REP", "PROCESS_TABLE", "[DONE] = No")
' or something similar to pick the next rep to do
then use SELECTED_REP in any recordset criteria or in any underlying query
I am away until 2/01/2001 but can elaborate on this afterwards if required.
"Darin Wray"
<darin@r... To: "Access" <access@p...>
lular.net> cc:
Subject: [access] looping thru records to
20/12/2001 21:35 print report...
Please respond to
"Access"
Hey guys...need some direction on how to code this scenario.
I have an unbound report that has 7 subreports in it. Each subreport's
datasource is a stored proc with date and salesrep parameters. These
parameters are passed based on fields that are entered in a form. The
report works great for one rep at a time...I couldn't get it to sort/group
correctly on a report for ALL sales reps, tho. Perhaps there is something
I'm missing...anyways, I thought another alternative would be to program
some kind of For Next statement to loop thru each employee, and print
their very own sales report.
Here's what I'm thinking...in english
Probably have to pull a recordset down that has the salesreps names...
for each salesrep where salesrep is value for a stored proc parameter
print salesreport where begin date and end date are stored proc parameter.
Can someone give me a rough example on how to code this in a command
button?
Thanks,
Darin
*************************************************************************
This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.
The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965 (also regulated by IMRO) - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL Telephone 0161-832-8686 Internet http://www.cis.co.uk E-mail
cis@c...
CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.
CIS is a member of the General Insurance Standards Council
CIS & the CIS logo (R) Co-operative Insurance Society Limited
********************************************************************************
|
|
|
View Cart
