Query for most common values

[buratti]

I have a table holding banking transaction information (exported from quickbooks). This table holds a few thousand records. I am using Access to get some quick results so this database does not have a separate “payee” table with a relationship to the transaction but is only a single table with “payee”, “amount” & “ account” fields among others. What I need to query the results to be is:

1 result for each separate payee and the most common account used for that payee.

For example let’s say in this table there are 50 entries with the payee of “Home Depot”. Let’s also say that the account field for 40 of those Home Depot records was listed as “home improvement” and the other 10 the account field stated “other”. I would need a query that returned 1 result for payee - Home Depot and the most common acount used for Home Depot being”home improvement”.

BUT, not just return the single Home Depot record. The same results for each unique payee filed in the table is needed. So the table may have 150 different unique payees, so I would need the results to contain 150 records - one for each payee, but list the most common account name used for every record of each payee.

Any suggestions? I’m really stumped on this one.

 

[The_Doc_Man]

The way to do this is some form of SQL Aggregate query. Perhaps if your table structure supports it, you can COUNT the number of entries with each listing grouped by the account payees in question. Then look up the TOP qualifier.

Since I don't know your table structure, all I can do is suggest looking up the SQL COUNT and TOP keywords.

 

[plog]

This will require 2 subqueries:

SELECT payee, account, COUNT(payee) AS Transactions
FROM YourTableNameHere
GROUP BY payee

Name that 'sub1'. That will get the total transactions for each payee/account permutation. Next use this SQL:

SELECT payee, MAX(Transactions) AS MostTransactions
FROM sub1
GROUP BY payee

Name that sub2. It determines what the highest total per payee is. Finally, put them both together to get the result you want:

SELECT sub1.payee, sub1.account
FROM sub1
INNER JOIN sub2 ON sub1.payee=sub2.payee AND sub1.Transactions=sub2.MostTransactions

That will get the data you want with one caveat: If there is a tie it will return both records. So although you expect 150 records there could be more because you havn't dictated a way to break ties.

 

[buratti]

Hey all

Thanks for the suggestions. Sounds like they should work. I haven’t tried it yet though as I ran into other snags with access in general regarding not being able to import a simple spreadsheet into any database, then getting some random active x error. Been working on those which is why the delayed response.

Thanks