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

[Guest]

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.

 

[Guest]

Post your SQL statement.

 

[Guest]

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.

 

[Guest]

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]));

 

[Guest]

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.

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]));

Post your SQL statement.

 

[Guest]

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?

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.

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]));

Post your SQL statement.