Calculate percentage and total multiple results

[Guest]

I'm processing rebate checks. I have many payees in a table. In a SubTable is
their invoice information. I enter the total price paid in a PAID field. I
need to calculate a percentage (30%) of the PAID amount which is their
rebate. I want this percentage "ITEM_REBATE" amount to record in a field on
that same table line.

....because Step #2 -- each payee may have subsequent item invoices, so I
also need a REBATE CHECK TOTAL (for each payee) totalling the ITEM_REBATE
amounts for that particular payee.

example for 10% discount:
JOE'S STORE Rebate Check Total 4.15
Invoice #1 Paid $24.00 Item_Rebate $2.40
Invoice #2 Paid $17.50 Item_Rebate $1.75

BROOK'S STORE
Invoice ........etc.

 

[tw]

Item_rebate should not be a field in the table. It should be calculated in
a query. The query shows it the way you want it to be. Use the query for
the report and group it on PayeeName.

select [payee]![PayeeName], [invoice]![InvoiceNumber], [invoice]![paid],
PercentAsAParameterOrFromAField * [invoice]![Paid] as Item_Rebate from
[payee] inner join [invoice] on [Payee]![someKEYfield] =
[invoice]![someKEYfield]

PercentAsAParameterOrFromAField this will be the percent. You didn't
specify whether there was a field that held this percentage or if you would
enter that later, but since you showed examples with different percentages
it will either have to be a parameter or stored in the table either at the
payee level or at the invoice level depending on how you intend to use it.
If you want the percentage to be applied on an invoice level, the percentage
will need to be stored in the invoice table, otherwise it can be a parameter
to the query or stored in the payee table.

 

[Guest]

Thank you. I created query and from a blank field in query, put in a
calculation to get a certain percentage of the Extended Total field.
(Percent is the same for all and will not change.)

However, now in my reports -- when I include the new query field that
contains the calculated percentage -- it shows up fine, but, all my entries
in the table duplicate them selves. Now I have 2 entries forr each actual
one. The table still shows only 1 entry. All totals, etc., are now double.
Any further ideas to correct this would be appreciated. I'm a newbie.
Thanks.
--
Tammy S

Item_rebate should not be a field in the table. It should be calculated in
a query. The query shows it the way you want it to be. Use the query for
the report and group it on PayeeName.

select [payee]![PayeeName], [invoice]![InvoiceNumber], [invoice]![paid],
PercentAsAParameterOrFromAField * [invoice]![Paid] as Item_Rebate from
[payee] inner join [invoice] on [Payee]![someKEYfield] =
[invoice]![someKEYfield]

PercentAsAParameterOrFromAField this will be the percent. You didn't
specify whether there was a field that held this percentage or if you would
enter that later, but since you showed examples with different percentages
it will either have to be a parameter or stored in the table either at the
payee level or at the invoice level depending on how you intend to use it.
If you want the percentage to be applied on an invoice level, the percentage
will need to be stored in the invoice table, otherwise it can be a parameter
to the query or stored in the payee table.

I'm processing rebate checks. I have many payees in a table. In a SubTable
is
their invoice information. I enter the total price paid in a PAID field.
I
need to calculate a percentage (30%) of the PAID amount which is their
rebate. I want this percentage "ITEM_REBATE" amount to record in a field
on
that same table line.

...because Step #2 -- each payee may have subsequent item invoices, so I
also need a REBATE CHECK TOTAL (for each payee) totalling the ITEM_REBATE
amounts for that particular payee.

example for 10% discount:
JOE'S STORE Rebate Check Total 4.15
Invoice #1 Paid $24.00 Item_Rebate $2.40
Invoice #2 Paid $17.50 Item_Rebate $1.75

BROOK'S STORE
Invoice ........etc.

 

[tw]

in your query you can try a couple of things...

one is to right click somewhere in the colums and select totals, then make
sure that all columns have "Group by" in the totals row

the other is to switch to sql view and change "select..." to "select
distinct..."

Thank you. I created query and from a blank field in query, put in a
calculation to get a certain percentage of the Extended Total field.
(Percent is the same for all and will not change.)

However, now in my reports -- when I include the new query field that
contains the calculated percentage -- it shows up fine, but, all my
entries
in the table duplicate them selves. Now I have 2 entries forr each
actual
one. The table still shows only 1 entry. All totals, etc., are now
double.
Any further ideas to correct this would be appreciated. I'm a newbie.
Thanks.
--
Tammy S

Item_rebate should not be a field in the table. It should be calculated
in
a query. The query shows it the way you want it to be. Use the query
for
the report and group it on PayeeName.

select [payee]![PayeeName], [invoice]![InvoiceNumber], [invoice]![paid],
PercentAsAParameterOrFromAField * [invoice]![Paid] as Item_Rebate from
[payee] inner join [invoice] on [Payee]![someKEYfield] =
[invoice]![someKEYfield]

PercentAsAParameterOrFromAField this will be the percent. You didn't
specify whether there was a field that held this percentage or if you
would
enter that later, but since you showed examples with different
percentages
it will either have to be a parameter or stored in the table either at
the
payee level or at the invoice level depending on how you intend to use
it.
If you want the percentage to be applied on an invoice level, the
percentage
will need to be stored in the invoice table, otherwise it can be a
parameter
to the query or stored in the payee table.

I'm processing rebate checks. I have many payees in a table. In a
SubTable
is
their invoice information. I enter the total price paid in a PAID
field.
I
need to calculate a percentage (30%) of the PAID amount which is their
rebate. I want this percentage "ITEM_REBATE" amount to record in a
field
on
that same table line.

...because Step #2 -- each payee may have subsequent item invoices, so
I
also need a REBATE CHECK TOTAL (for each payee) totalling the
ITEM_REBATE
amounts for that particular payee.

example for 10% discount:
JOE'S STORE Rebate Check Total 4.15
Invoice #1 Paid $24.00 Item_Rebate $2.40
Invoice #2 Paid $17.50 Item_Rebate $1.75

BROOK'S STORE
Invoice ........etc.