Query Expression won't work need more help

[Bill_De]

I am having trouble with an expression in my queries. I have three tables:
tblRegistration which has number of registrations, cost, and a drop down box
to select type of payment. then tblpaytype alows the selection in
tblRegistration of Paypal, ECheck, Credit card, Check, cash. Then I have a
one record table tblpercent which I input the percentages and surcharges for
Pay Pal, ECheck and credit cards.

I created the query with the tblregistration only and draged the fields
Registration ID, Payment date, transaction number, payment type, number of
registrations, and cost down from the table. When I ran the query I was able
to select the type of payment, and enter the number of registrations and cost
and the query worked fine and allowed me to enter additional records.
I then created the following expression: AmountPaid:[numberof
registrations]*[cost]. I then ran the query and everything worked fine.

I was told that in the one record table tblpercent that I should not have a
primary key and that I should place the table in the query with no join
lines. I did this and then entered the following expression-
Fees:iif([paymenttype]=1,[amountpaid]*[tblpercent]![PPPercent],iif([paymenttype]=2,[AmountPaid]*[tblpercent]![ECPercent]))
1 is the paymentID for Pay Pal and 2 is the paymentID for e-Check PPPercent
is the percentage charged by paypal present 2.9% ECPercent is the e-check
percentage present 2.5%.
After inserting this table tblpercent into the query and writing the above
expression when I run the query I get the field names but no record lines to
enter new records. If I remove the part of the expression referencing the
tblpercent - [tblpercent]![PPPercent] and enter the figure 0.029 then run the
query the expression gives me the value I need $5.07. If I make the field
LineID the primary key and link it to tblRegistration the number field LineID
then I can make entries but I have to enter the number 1 each time in lineID.
Also if I place the tblpaytype into the query I then can't add any records to
the query.

Can anyone tell me how I can correct this so the expression works without
having to have the line ID record where I have to enter the one line from
tblpercent. Note: I have a seperate table for percentages as I will need to
use the above expressions in three different reports and three different
queries and by having the table I can enter any changes to percentages.

I just need to know how to reference the one record table percentages in the
expression and still get the query to run the expression.

Thanks
Bill_De

 

[Steve Schapel]

Bill,

I'm not sure, but there may be a fundamental misunderstanding here.
Queries are not for data entry.

I am also confused about the relationship/difference between
"paymenttype" and "paymentID"... sorry, just can't quite follow your
example at the moment.

Anyway, adding the tblpercent table to the query as you have done will
render the query non-updateable. This will be fine for your reports.
However, for the purposes of data entry/editing, this will not work.
You will need a different approach. One such approach would involve the
use of a DLookup function in the Control Source of an unbound textbox on
the form.

However, I will not recommend this, as there is also another fundamental
flaw in your design. Your tblpercent table should not be a "one record
table" with a separate field for PPPercent and ECPercent. This is
wrong. Your tblpercent table should have a separate *record*, not a
separate field, for each payment type. You should have a paymenttype
field and a ChargePercent field.

Then, in your query that your form is based on, you should have your
existing table joined (Left Join) to the tblpercent table via the
payment type fields from both tables. And then the calculated field
will be like this...
Fees: [amountpaid]*Nz([ChargePercent],0)

--
Steve Schapel, Microsoft Access MVP

I am having trouble with an expression in my queries. I have three tables:
tblRegistration which has number of registrations, cost, and a drop down box
to select type of payment. then tblpaytype alows the selection in
tblRegistration of Paypal, ECheck, Credit card, Check, cash. Then I have a
one record table tblpercent which I input the percentages and surcharges for
Pay Pal, ECheck and credit cards.

I created the query with the tblregistration only and draged the fields
Registration ID, Payment date, transaction number, payment type, number of
registrations, and cost down from the table. When I ran the query I was able
to select the type of payment, and enter the number of registrations and cost
and the query worked fine and allowed me to enter additional records.
I then created the following expression: AmountPaid:[numberof
registrations]*[cost]. I then ran the query and everything worked fine.

I was told that in the one record table tblpercent that I should not have a
primary key and that I should place the table in the query with no join
lines. I did this and then entered the following expression-
Fees:iif([paymenttype]=1,[amountpaid]*[tblpercent]![PPPercent],iif([paymenttype]=2,[AmountPaid]*[tblpercent]![ECPercent]))
1 is the paymentID for Pay Pal and 2 is the paymentID for e-Check PPPercent
is the percentage charged by paypal present 2.9% ECPercent is the e-check
percentage present 2.5%.
After inserting this table tblpercent into the query and writing the above
expression when I run the query I get the field names but no record lines to
enter new records. If I remove the part of the expression referencing the
tblpercent - [tblpercent]![PPPercent] and enter the figure 0.029 then run the
query the expression gives me the value I need $5.07. If I make the field
LineID the primary key and link it to tblRegistration the number field LineID
then I can make entries but I have to enter the number 1 each time in lineID.
Also if I place the tblpaytype into the query I then can't add any records to
the query.

Can anyone tell me how I can correct this so the expression works without
having to have the line ID record where I have to enter the one line from
tblpercent. Note: I have a seperate table for percentages as I will need to
use the above expressions in three different reports and three different
queries and by having the table I can enter any changes to percentages.

I just need to know how to reference the one record table percentages in the
expression and still get the query to run the expression.

Thanks
Bill_De