Report Design Question-obtaining total quantity of each quote number.

[dan dungan]

Hi microsoft.public.access.reports,

In access 2000 on window 2000 pro, I have a table including the
following fields:
Field Name: Data Type
partnumber Text
Quote Number Long integer
Rep Text
Quantity Long Integer
UnitPrice Currency

The report needs to show:
Rep 1 Rep 2
# of # of Extended # of # of
Extended
Date quotes items Amount quotes items Amount

Total

I'm not sure how to show the number of quotes. Using the count
function returns the # of items.

I added a quote number header and footer in the report and count the
quote numbers for each. Now I want to sum those, but I can't sum the
value of a text box.

How can I display the number of quotes in the report?

Thanks,

Dan Dungan

 

[Duane Hookom]

You can add a text box in your Quote Number header:
Name: txtCountQuote
Control Source: =1
Running Sum: Over Group or Over All
Visible: No
Then add a text box to either the Report Footer or a broader Group Footer:
Control Source: =txtCountQuote

 

[dan dungan]

Thank you Duane. That works great.

I don't understand why, though. I thought one couldn't use a text box
as a control source for another text box.

Dan

 

[Duane Hookom]

You can't aggregate a text box from another report section using Sum() or
Count() etc. You can however reference the control to display the most recent
value of the control.

 

[dan dungan]

Hi,

So, now I have two questions to finish this report:

1. How do I show totals for # of quotes since I can't aggregate the
value from the text box?

2. This report is based on a Crosstab query. In order for the report
to show data from a date range, I had to add two parameters--begin
date and end date. In the query design tool, criteria field, I had to
add the statement, "Between [begin date] And [end date]". Now the
begin and end date dialog boxes present twice each when a launch the
report.

Any suggestions?

Thanks,

Dan

 

[Duane Hookom]

1. What do you want to total? You might need to use a running sum or a simple
=Sum([your expression here])

2. parameter prompts are never a good interface.
http://www.tek-tips.com/faqs.cfm?fid=6763.
Any parameter used in a crosstab requires that you enter the data type
of the parameter.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Hi,

So, now I have two questions to finish this report:

1. How do I show totals for # of quotes since I can't aggregate the
value from the text box?

2. This report is based on a Crosstab query. In order for the report
to show data from a date range, I had to add two parameters--begin
date and end date. In the query design tool, criteria field, I had to
add the statement, "Between [begin date] And [end date]". Now the
begin and end date dialog boxes present twice each when a launch the
report.

Any suggestions?

Thanks,

Dan

You can't aggregate a text box from another report section using Sum() or
Count() etc. You can however reference the control to display the most recent
value of the control.