Reporting from a query using a counter

  • Thread starter Thread starter Jon Rowlan
  • Start date Start date
J

Jon Rowlan

I have to write a report that will add on 10% to each line for the first 250
records and then 5% for the remainder of the report.

I have managed to cobble something together using VB code against the report
but wondered whether there was a better way.

It is possible in a query to have a record number against each row?

So that as the query displays its records the record count starts at 1 and
increments by one for each record displayed

thanks all

jON
 
Since you are reporting your results, you can add a text box to your detail
section:
Name: txtRunSum
Control Source: =1
Running Sum: Over All
Then to add 10% to a field name [Quantity], add a text box:
Name: txtHuh
Control Source: =[Quantity] * IIf(txtRunSum<=250,1.1,1.05)
 
perfect thanks Duane, just out of interest, for other reasons I may want to
have a query with a record number ...

is it possible to do that?

thanks,

jON

Duane Hookom said:
Since you are reporting your results, you can add a text box to your
detail section:
Name: txtRunSum
Control Source: =1
Running Sum: Over All
Then to add 10% to a field name [Quantity], add a text box:
Name: txtHuh
Control Source: =[Quantity] * IIf(txtRunSum<=250,1.1,1.05)

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
I have to write a report that will add on 10% to each line for the first
250 records and then 5% for the remainder of the report.

I have managed to cobble something together using VB code against the
report but wondered whether there was a better way.

It is possible in a query to have a record number against each row?

So that as the query displays its records the record count starts at 1
and increments by one for each record displayed

thanks all

jON
Click to expand...
Click to expand...
 
Search the queries news group on "rank".

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
perfect thanks Duane, just out of interest, for other reasons I may want
to have a query with a record number ...

is it possible to do that?

thanks,

jON

Duane Hookom said:
Since you are reporting your results, you can add a text box to your
detail section:
Name: txtRunSum
Control Source: =1
Running Sum: Over All
Then to add 10% to a field name [Quantity], add a text box:
Name: txtHuh
Control Source: =[Quantity] * IIf(txtRunSum<=250,1.1,1.05)

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
I have to write a report that will add on 10% to each line for the first
250 records and then 5% for the remainder of the report.

I have managed to cobble something together using VB code against the
report but wondered whether there was a better way.

It is possible in a query to have a record number against each row?

So that as the query displays its records the record count starts at 1
and increments by one for each record displayed

thanks all

jON
Click to expand...
Click to expand...
Click to expand...
 
Because my data set is in natural order, I don't think that I am not going
to be able to use a RANK

I have incorporated your running source which works fine.

Tell me though Duane, how do I sum txtHuh as in your example below?

If i have a total with a source of :

=sum([Quantity] * IIf(txtRunSum<=250,1.1,1.05))

I am prompted for a value for txtRunSum

if I use

=sum(txtHuh)

Access changes it to


=sum([txtHuh])

And as that is not a field on my query this refuses to work???

jON


Duane Hookom said:
Search the queries news group on "rank".

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
perfect thanks Duane, just out of interest, for other reasons I may want
to have a query with a record number ...

is it possible to do that?

thanks,

jON

Duane Hookom said:
Since you are reporting your results, you can add a text box to your
detail section:
Name: txtRunSum
Control Source: =1
Running Sum: Over All
Then to add 10% to a field name [Quantity], add a text box:
Name: txtHuh
Control Source: =[Quantity] * IIf(txtRunSum<=250,1.1,1.05)

--
Duane Hookom
MS Access MVP
--

I have to write a report that will add on 10% to each line for the first
250 records and then 5% for the remainder of the report.

I have managed to cobble something together using VB code against the
report but wondered whether there was a better way.

It is possible in a query to have a record number against each row?

So that as the query displays its records the record count starts at 1
and increments by one for each record displayed

thanks all

jON
Click to expand...
Click to expand...
Click to expand...
 
To Sum() txtHuh, you must copy the text box and set a Running Sum of it Over
All. Then reference the new text box in the report footer:

=txtHuhRunSum

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
Because my data set is in natural order, I don't think that I am not going
to be able to use a RANK

I have incorporated your running source which works fine.

Tell me though Duane, how do I sum txtHuh as in your example below?

If i have a total with a source of :

=sum([Quantity] * IIf(txtRunSum<=250,1.1,1.05))

I am prompted for a value for txtRunSum

if I use

=sum(txtHuh)

Access changes it to


=sum([txtHuh])

And as that is not a field on my query this refuses to work???

jON


Duane Hookom said:
Search the queries news group on "rank".

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
perfect thanks Duane, just out of interest, for other reasons I may want
to have a query with a record number ...

is it possible to do that?

thanks,

jON

Since you are reporting your results, you can add a text box to your
detail section:
Name: txtRunSum
Control Source: =1
Running Sum: Over All
Then to add 10% to a field name [Quantity], add a text box:
Name: txtHuh
Control Source: =[Quantity] * IIf(txtRunSum<=250,1.1,1.05)

--
Duane Hookom
MS Access MVP
--

I have to write a report that will add on 10% to each line for the
first 250 records and then 5% for the remainder of the report.

I have managed to cobble something together using VB code against the
report but wondered whether there was a better way.

It is possible in a query to have a record number against each row?

So that as the query displays its records the record count starts at 1
and increments by one for each record displayed

thanks all

jON
Click to expand...
Click to expand...
Click to expand...
 
so is there a way to make the running sum text box invisible? Width of zero
I guess?

cheers Duane.

jON

Duane Hookom said:
To Sum() txtHuh, you must copy the text box and set a Running Sum of it
Over All. Then reference the new text box in the report footer:

=txtHuhRunSum

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
Because my data set is in natural order, I don't think that I am not
going to be able to use a RANK

I have incorporated your running source which works fine.

Tell me though Duane, how do I sum txtHuh as in your example below?

If i have a total with a source of :

=sum([Quantity] * IIf(txtRunSum<=250,1.1,1.05))

I am prompted for a value for txtRunSum

if I use

=sum(txtHuh)

Access changes it to


=sum([txtHuh])

And as that is not a field on my query this refuses to work???

jON


Duane Hookom said:
Search the queries news group on "rank".

--
Duane Hookom
MS Access MVP
--

perfect thanks Duane, just out of interest, for other reasons I may
want to have a query with a record number ...

is it possible to do that?

thanks,

jON

Since you are reporting your results, you can add a text box to your
detail section:
Name: txtRunSum
Control Source: =1
Running Sum: Over All
Then to add 10% to a field name [Quantity], add a text box:
Name: txtHuh
Control Source: =[Quantity] * IIf(txtRunSum<=250,1.1,1.05)

--
Duane Hookom
MS Access MVP
--

I have to write a report that will add on 10% to each line for the
first 250 records and then 5% for the remainder of the report.

I have managed to cobble something together using VB code against the
report but wondered whether there was a better way.

It is possible in a query to have a record number against each row?

So that as the query displays its records the record count starts at
1 and increments by one for each record displayed

thanks all

jON
Click to expand...
Click to expand...
Click to expand...
 
If I copy my text box to another one in the detail section and call it
"TextBoxRunSum" and set running sum thats fine.

But when I try to reference it in my footer

=textboxrunsum

gets converted to

=[textboxrunsum]

There is no fields called with this name and prompts me for avalue

If I just enter

textboxrunsum

it prompts for a value ????

I am now very confused???

jON

Duane Hookom said:
To Sum() txtHuh, you must copy the text box and set a Running Sum of it
Over All. Then reference the new text box in the report footer:

=txtHuhRunSum

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
Because my data set is in natural order, I don't think that I am not
going to be able to use a RANK

I have incorporated your running source which works fine.

Tell me though Duane, how do I sum txtHuh as in your example below?

If i have a total with a source of :

=sum([Quantity] * IIf(txtRunSum<=250,1.1,1.05))

I am prompted for a value for txtRunSum

if I use

=sum(txtHuh)

Access changes it to


=sum([txtHuh])

And as that is not a field on my query this refuses to work???

jON


Duane Hookom said:
Search the queries news group on "rank".

--
Duane Hookom
MS Access MVP
--

perfect thanks Duane, just out of interest, for other reasons I may
want to have a query with a record number ...

is it possible to do that?

thanks,

jON

Since you are reporting your results, you can add a text box to your
detail section:
Name: txtRunSum
Control Source: =1
Running Sum: Over All
Then to add 10% to a field name [Quantity], add a text box:
Name: txtHuh
Control Source: =[Quantity] * IIf(txtRunSum<=250,1.1,1.05)

--
Duane Hookom
MS Access MVP
--

I have to write a report that will add on 10% to each line for the
first 250 records and then 5% for the remainder of the report.

I have managed to cobble something together using VB code against the
report but wondered whether there was a better way.

It is possible in a query to have a record number against each row?

So that as the query displays its records the record count starts at
1 and increments by one for each record displayed

thanks all

jON
Click to expand...
Click to expand...
Click to expand...
 
The addition of the [ ]s is normal. Confirm the correct name of the text box
by copying and paste the value from the Name to the Control Source. Make
sure your "total" text box is in the Report Footer section.

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
If I copy my text box to another one in the detail section and call it
"TextBoxRunSum" and set running sum thats fine.

But when I try to reference it in my footer

=textboxrunsum

gets converted to

=[textboxrunsum]

There is no fields called with this name and prompts me for avalue

If I just enter

textboxrunsum

it prompts for a value ????

I am now very confused???

jON

Duane Hookom said:
To Sum() txtHuh, you must copy the text box and set a Running Sum of it
Over All. Then reference the new text box in the report footer:

=txtHuhRunSum

--
Duane Hookom
MS Access MVP
--

Jon Rowlan said:
Because my data set is in natural order, I don't think that I am not
going to be able to use a RANK

I have incorporated your running source which works fine.

Tell me though Duane, how do I sum txtHuh as in your example below?

If i have a total with a source of :

=sum([Quantity] * IIf(txtRunSum<=250,1.1,1.05))

I am prompted for a value for txtRunSum

if I use

=sum(txtHuh)

Access changes it to


=sum([txtHuh])

And as that is not a field on my query this refuses to work???

jON


Search the queries news group on "rank".

--
Duane Hookom
MS Access MVP
--

perfect thanks Duane, just out of interest, for other reasons I may
want to have a query with a record number ...

is it possible to do that?

thanks,

jON

Since you are reporting your results, you can add a text box to your
detail section:
Name: txtRunSum
Control Source: =1
Running Sum: Over All
Then to add 10% to a field name [Quantity], add a text box:
Name: txtHuh
Control Source: =[Quantity] * IIf(txtRunSum<=250,1.1,1.05)

--
Duane Hookom
MS Access MVP
--

I have to write a report that will add on 10% to each line for the
first 250 records and then 5% for the remainder of the report.

I have managed to cobble something together using VB code against
the report but wondered whether there was a better way.

It is possible in a query to have a record number against each row?

So that as the query displays its records the record count starts at
1 and increments by one for each record displayed

thanks all

jON
Click to expand...
Click to expand...
Click to expand...
 
Back
Top