Distinct Query

[JK]

I hope someone can help me. Have a look at this example and tell me if the following is possible.

Fee Table Acc No Client Name Message Definition No Fee Amount
1421 City Service 100 2.50
1421 City Service 200 0.00
1421 City Service 2225 1.50
0041 Cash Enterprises 2225 1.50
0041 Cash Enterprises 2225 1.50
0346 ABC Enterprises 100 2.50
0346 ABC Enterprises 2225 1.50
0346 ABC Enterprises 2225 1.50
1444 City Finance 2225 1.50


I need a query that will display the users that only have done "Message Definition No" transactions which equal 2225 and not if they have done 100 and 200's. This should also be summed. Simply the result for the above mentioned table should look something like this:

Fee Query Acc No Client Name Message Definition No Sum of Fee Amount
0041 Cash Enterprises 2225 3.00
1444 City Finance 2225 1.50



Your'e help is appreciated and sorry this post is best viewed in HTML format.

TIA

JK

 

[Nikos Yannacopoulos]

Try this:

SELECT TableName.[Acc No], TableName.[Client Name], TableName.[Message Definition No], Sum(TableName.[Fee Amount]) AS [SumOfFee Amount]
FROM TableName
GROUP BY TableName.[Acc No], TableName.[Client Name], TableName.[Message Definition No]
HAVING (((TableName.[Acc No]) Not In (SELECT [Acc No] FROM TableName WHERE [Message Definition No] <> 2225)))
WITH OWNERACCESS OPTION;

Paste the SQL expression in the SQL view of a new query, and change TableName to the actual name of your table (watch out for word wrapping in the post).

HTH,
Nikos
I hope someone can help me. Have a look at this example and tell me if the following is possible.

Fee Table Acc No Client Name Message Definition No Fee Amount
1421 City Service 100 2.50
1421 City Service 200 0.00
1421 City Service 2225 1.50
0041 Cash Enterprises 2225 1.50
0041 Cash Enterprises 2225 1.50
0346 ABC Enterprises 100 2.50
0346 ABC Enterprises 2225 1.50
0346 ABC Enterprises 2225 1.50
1444 City Finance 2225 1.50


I need a query that will display the users that only have done "Message Definition No" transactions which equal 2225 and not if they have done 100 and 200's. This should also be summed. Simply the result for the above mentioned table should look something like this:

Fee Query Acc No Client Name Message Definition No Sum of Fee Amount
0041 Cash Enterprises 2225 3.00
1444 City Finance 2225 1.50



Your'e help is appreciated and sorry this post is best viewed in HTML format.

TIA

JK