Query expression will not work

[Guest]

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

 

[Gary Walter]

it sure looks to me like tblpercent should be
(at least) 3 fields and multirow:

pmttype pmtname pmtpercent percentdate
1 PayPal .029 9/21/2006
2 ECheck .025 9/21/2006

then just join on pmttype to get pmtpercent

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

 

[Guest]

I will give it a try but I think then I would have a second drop down box in
the form/query, plus I am not sure then how I would write the expressions I
need any advise on that one.
Thanks

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

 

[Gary Walter]

I had assumed that adding your original tblpercent
to the query *without a join* was the cause of you
no longer being able to add new records in the query
(I could be wrong...)

With a table structure as I suggested for tblpercent,
you would add the table to your query, then *join*

tblpercent.pmttype to tblRegistration.paymenttype

then your calculations would simply be:

AmountPaid: [number of registrations]*[cost]
Fees: tblpercent.pmtpercent*[AmountPaid]

I would think this join will solve the new record
problem.

It was not immediately obvious to me how "paymenttype"
is "structured."

In fact, the "drop down box" in a table (if I understand
correctly) has me worried. It is almost always a
mistake to create *lookup fields* in a table (if that is
what you have done).

Queries should be used only to provide sources
for your forms or reports.

On a form, you can provide a combobox
bound to tblRegistration.paymenttype
whose source is a query into tblpaytype.

I assume tblRegistration.paymenttype would be
type Long and then serve as a foreign key to
tblpaytype.

In the combobox, you would show your users
the paymenttype string field ("Paypal" etc) but
store the Long field (combo's "bound column")
from tblpaytype for paymenttype.

There is no need for a lookup field because you
will never show the users a table.