Query Expressions that need to be saved and field entries

[Guest]

I am having a major problem with expressions in a query and saving the
results. I have three tables so far in my data base named; tblChurchPmt,
tblPmtType, tblPercent. Each table has a primary key as follows:
tblChruchPmt- ChurchID, tblPmtType-PaymentID, tblPercent-LineID.
tblPmtType is used in a drop down box in tblChurchPmt to make selections as
follows: 1=PayPal, 2=Check. tblPercent is used for percentage fees and
surcharges from Pay Pal. I am using a table as these fees can change from
time to time and this way I would only have to change the figures in the
table and they would be changed in all of the expressions. There will only be
one record in tblPercent (1).

Each table has the primary key as an autonumber and each table has a field
named after the other tables primary keys as number.

I created a query from the tblChurchPmt and I have three fields named;
AmountPaid, FeeAmount, NetPayment. I also have entered the following
expressions
FeeAmount:iif([PaymentID]=1,[AmountPaid]*[PayPalPercentage]+[Surcharge],iif([PaymentID]=2,0))
NetPayment:AmountPaid-FeeAmount.

PaymentID is the primary key for either Pay Pal or Check from the drop down
box
PayPalPercentage is the percentage amount entered that I get from Pay Pal
Surcharge is the amount entered that I get from Pay Pal

Here are the two questions I am trying to get resolved:
1- Is there anyway that I can have the LineID from tblPercent enter
everytime I make a new entry? As it is now I have to enter the number 1
inorder for the expressions to work if I don't enter 1 a 0 is entered and the
results of the expressions are blank.
2- How can I save the results of the expressions to the table. I need to
save the Fee Amounts and the Net Payment in the table tblChurchPmt as these
amounts are needed for the reports I generate and are required for audits. As
these amounts change with the amount paid I have to have them saved in the
table.

Any help with this would be greatly appreciated. I would also like to ask
that you try to explain this to me in simple terms as my understanding of
Access is still limited so if you could give me good details that would be
great.

Thank you in advance
Bill_De

 

[John Vinson]

I am having a major problem with expressions in a query and saving the
results.

Well, the best solution is... DON'T save the results.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]

 

[Guest]

John,
Thanks I can't believe I forgot that basic rule in Access let the program do
the computations each time.
There is one thing I am still trying to address and that is the expression I
have will not work unless I enter the primary key from the table I have for
fees from pay pal. If I don't enter the number 1 in the field LineID the
expression returns blank, If I enter 1 then the expression does the
calculations I need correctly.

Is there any way to either get the lineID to fill in automaticaly, or can I
rewrite the expression so it will run without having to have the primary
field/ LineID number entered in the table/query. The exression I am using now
is:

FeeAmount:iif([PaymentID]=1,[AmountPaid]*[PayPalPercentage]+[Surcharge],iif([PaymentID]=2,0))

PayPalPercentage is the percent amount I get from Pay pal subject to change
currently at 2.9% (0.029) and the surcharge is $0.31 per transaction. LineID
is the primary key autonumber in the table tblpercent. I use this table as
the percentages and surcharge can change and this way I won't have to go into
multiple expressions and make changes it will be done automaticaly based on
the table tblpercent.

Please advise on how I can either enter the LineID automatically or what I
need to change in the expression to make it work without needing the LineID.

Thanks again for all your help. This should complete what I need to finish
the project.

Bill_De

 

[John Vinson]

John,
Thanks I can't believe I forgot that basic rule in Access let the program do
the computations each time.
There is one thing I am still trying to address and that is the expression I
have will not work unless I enter the primary key from the table I have for
fees from pay pal. If I don't enter the number 1 in the field LineID the
expression returns blank, If I enter 1 then the expression does the
calculations I need correctly.

Is there any way to either get the lineID to fill in automaticaly, or can I
rewrite the expression so it will run without having to have the primary
field/ LineID number entered in the table/query. The exression I am using now
is:

FeeAmount:iif([PaymentID]=1,[AmountPaid]*[PayPalPercentage]+[Surcharge],iif([PaymentID]=2,0))

PayPalPercentage is the percent amount I get from Pay pal subject to change
currently at 2.9% (0.029) and the surcharge is $0.31 per transaction. LineID
is the primary key autonumber in the table tblpercent. I use this table as
the percentages and surcharge can change and this way I won't have to go into
multiple expressions and make changes it will be done automaticaly based on
the table tblpercent.

Please advise on how I can either enter the LineID automatically or what I
need to change in the expression to make it work without needing the LineID.

I'm sorry, I have NO idea what you mean here. The expression doesn't
reference a field named LineID; and I'd never enter the value of an
Autonumber as a constant anyway, since they are arbitrary and
uncontrollable. If this is a one-record table (not keeping a history),
then perhaps it should not have an autonumber field at all, and you
can just use a Cartesian join (no join line) to include it in your
query.

John W. Vinson[MVP]