Reporting from a query using a counter

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)

--
Duane Hookom
MS Access MVP
--

"Jon Rowlan" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

Search the queries news group on "rank".

--
Duane Hookom
MS Access MVP
--

"Jon Rowlan" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
Search the queries news group on "rank".

--
Duane Hookom
MS Access MVP
--

"Jon Rowlan" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
Search the queries news group on "rank".

--
Duane Hookom
MS Access MVP
--

"Jon Rowlan" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

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

cheers Duane.

jON

"Duane Hookom" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Search the queries news group on "rank".

--
Duane Hookom
MS Access MVP
--

"Jon Rowlan" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Search the queries news group on "rank".

--
Duane Hookom
MS Access MVP
--

"Jon Rowlan" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Search the queries news group on "rank".

--
Duane Hookom
MS Access MVP
--

"Jon Rowlan" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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