Setting Criteron for Query: Relation of two fields' values?

L

Lea

I am working on a query combining two tables: a "tblClients" table, which
includes the field "FPRate" - the fee for a client's plan; and "tblPayments"
table, which includes the field "Amount" - the amount of payments received
thus far from said client. The query sums the "Amount" field, giving me the
total received (this field in the query is labeled "Sum of Amount: Amount"

What I need is a way to use my query to display only records in which the
"Sum of Amount: Amount" is less than the "FPRate" value. Both fields are
number, long-integer. I tried, intuitively, placing the following in the
criteria field of "Sum of Amount: Amount": <"FPRate" but received the error
"Date type mismatch in the criteria expression" when I try to leave design
view.

Any help would be appreciate.
 
L

Lea

I'm working in a query; I'm not sure what you mean. Are you referring to
doing the "filtering" from the form/report itself, as an event expression for
the form/report? Or some other way of describing the query itself? I'm a
user somewhere between novice and beginning.
 
L

Lea

Sorry about that; I think I found what you were talking about.

SELECT DISTINCTROW [tblClients].Key, [tblClients].[FP Rate],
Sum(tlbPayments.Amount) AS [Sum Of Amount]
FROM [tblClients] INNER JOIN tblPayments ON [tblClients].Key =
tblPayments.ClientsKey
GROUP BY [tblClients].Key, [tblClients].[FP Rate]
HAVING (((Sum(tblPayments.Amount))<[FP Rate]));
 
K

KARL DEWEY

Try this --
SELECT tblClients.Key, tblClients.[FP Rate], Sum(tblPayments.Amount) AS
[Sum Of Amount]
FROM tblClients INNER JOIN tblPayments ON tblClients.Key =
tblPayments.ClientsKey
GROUP BY tblClients.Key, tblClients.[FP Rate]
HAVING Sum(tblPayments.Amount)<[FP Rate];

You say that [FP Rate], ClientsKey, and Amount are number, long-integer.

Lea said:
Sorry about that; I think I found what you were talking about.

SELECT DISTINCTROW [tblClients].Key, [tblClients].[FP Rate],
Sum(tlbPayments.Amount) AS [Sum Of Amount]
FROM [tblClients] INNER JOIN tblPayments ON [tblClients].Key =
tblPayments.ClientsKey
GROUP BY [tblClients].Key, [tblClients].[FP Rate]
HAVING (((Sum(tblPayments.Amount))<[FP Rate]));


KARL DEWEY said:
Post your SQL statement.
 
L

Lea

So far, it look like it will work - I do have a related problem, however. The
tblPayments table underlies a subform that records all payments for a given
client. No related records are created for a given client until an actual
payment is recorded. Therefore, this Sum of Amount: Amount is excluding all
clients who have made no payments yet, as they have no records in Payments to
Sum - and yet this is a very important group to include in my query.

Any suggestions?

KARL DEWEY said:
Try this --
SELECT tblClients.Key, tblClients.[FP Rate], Sum(tblPayments.Amount) AS
[Sum Of Amount]
FROM tblClients INNER JOIN tblPayments ON tblClients.Key =
tblPayments.ClientsKey
GROUP BY tblClients.Key, tblClients.[FP Rate]
HAVING Sum(tblPayments.Amount)<[FP Rate];

You say that [FP Rate], ClientsKey, and Amount are number, long-integer.

Lea said:
Sorry about that; I think I found what you were talking about.

SELECT DISTINCTROW [tblClients].Key, [tblClients].[FP Rate],
Sum(tlbPayments.Amount) AS [Sum Of Amount]
FROM [tblClients] INNER JOIN tblPayments ON [tblClients].Key =
tblPayments.ClientsKey
GROUP BY [tblClients].Key, [tblClients].[FP Rate]
HAVING (((Sum(tblPayments.Amount))<[FP Rate]));


KARL DEWEY said:
Post your SQL statement.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top