You use a subquery as a "calculated field" to get the Payer and Payee values
that you seek. Here's an example of how you'd do it for the Payer value:
SELECT [Date],
(SELECT DISTINCT T.Payer FROM YourTable AS T
WHERE T.[Date] = YourTable.[Date] AND
T.PayerPaidAmountField =
(SELECT Max(Q.PayerPaidAmountField) AS A
FROM YourTable AS Q WHERE Q.[Date] = T.[Date])) AS TopPayer
FROM YourTable
GROUP BY [Date];
If you don't want to use subqueries within the query, you can build separate
queries for each of the subqueries, and use one query as a tables in the
next query where you join the query and your table (I didn't test these
queries, so they may need a bit of debugging).
qryMaxPayerAmount
-------------
SELECT Max(PayerPaidAmountField) AS MaxPayerAmount,
[Date] FROM YourTable
GROUP BY [Date];
qryMaxPayer
--------------
SELECT DISTINCT YourTable.Payer, YourTable.[Date]
FROM YourTable INNER JOIN
qryMaxPayerAmount ON
YourTable.[Date] = qryMaxPayerAmount.[Date] AND
YourTable.PayerPaidAmountField =
qryMaxPayerAmount.MaxPayerAmount;
final query:
----------
SELECT YourTable.[Date], qryMaxPayer.Payer AS TopPayer
FROM YourTable INNER JOIN qryMaxPayer ON
YourTable.[Date] = qryMaxPayer.[Date]
GROUP BY [Date];
Also, note that you should not name a field "date". It and many other words
are reserved words in ACCESS and should not be used for control names, field
names, etc. Allen Browne (MVP) has a very comprehensive list of reserved
words at his website:
Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html
See these Knowledge Base articles for more information about reserved words
and characters that should not be used (these articles are applicable to
ACCESS 2007 as well):
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266
Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763
See this site for code that allows you to validate your names as not being
VBA keywords:
basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18
--
Ken Snell
<MS ACCESS MVP>
jjgs said:
Hi there,
I am creating a query to show the following information (all gathered from
one table)
Date (grouped by day)
Payer (person who paid the most to company on this day)
Payee (supplier company paid the most to this day)
Inflow (sum of all deposits by day)
Outflow (sum of all payments by day)
I am having trouble showing the payer & payee who had the greatest payment
value for each day. Using the max function just shows the last text so I
somehow need to refer it back to the inflow/ outflow amount and am not
sure
how to do this. I only have very basic knowledge of access so any help
would
be greatly appreciated!
