Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 39
  • Last Modified:

Need help finding highest dollar item on an invoice

I have a database filled with invoice line items.  To keep it simple lets say there are only three columns in the main table:

Project name/Item name/Extended Invoice Amount
1234 W Main Street / Driveway / $3,450
1234 W Main Street / Approach / $350
1234 W Main Street / Sidewalk / $750
625 W 55th Street / Driveway / $650
625 W 55th Street / Steps / $850
625 W 55th Street / Patio / $950

The desired Group Query result would look like:
  Group on Project name / Select highest amount line Item Name / Total all line items

1234 W Main Street / Driveway / $4,550
625 W 55th Street / Patio / $2,450
  • 5
  • 4
  • 4
4 Solutions
This requires a sub query.

Try this:
SELECT b.ProjectName, (Select Top 1 ItemName From YourTable as a  where a.ProjectName = b.ProjectName Order by InvoiceAmount Desc) as TopItem, Sum(b.InvoiceAmount) AS SumOfInvoiceAmt
FROM YourTable as b
GROUP BY b.ProjectName;
Dale FyeCommented:
Or you might try:

SELECT yourTable.Address, yourTable.ItemName, T.TotAmt
FROM yourTable
SELECT yourTable.Address, Max(yourTable.Amount) as MaxAmt, Sum(yourTable.Amount) as TotAmt
FROM yourTable
Group by yourTable.Address) as T
ON yourTable.Address = T.Address AND yourTable.Amount = T.MaxAmt

This will create duplicate rows for an address if to ItemNames have the same invoice amount, but so would Pats.

For a small recordset, I would guess her query would probably run quicker, but for larger datasets, I would anticipate mine would run quicker because of the correlated subquery in hers.  That would have to be evaluated for each record in your table, whereas the subquery in mine would only be evaluated once.

Recommend you give them both a try.
Eric ShermanAccountant/DeveloperCommented:
Or ... If you can't get the SQL to work I have better results doing this in VBA Code.  It kind of depends on the size of your data tables.  Running sub-queries on large data tables can get slow at times ... just my personal observation.  The following VBA function will find the record with the largest extended invoice amount within the group then write that record along with the sum of invoice items for the group to a separate temp table.  It works, it's fast and clean.

Function InvoiceItems()
DoCmd.SetWarnings False
DoCmd.Hourglass True

Dim rstSumInvItems As Recordset
Dim rstProjName As Recordset
Dim rstDetailItms As Recordset

DoCmd.RunSQL "delete * from table2;"

Set rstSumInvItems = CurrentDb.OpenRecordset("table2", dbOpenDynaset)
Set rstProjName = CurrentDb.OpenRecordset("select distinct project_name from table1;", dbOpenDynaset)
Set rstDetailItms = CurrentDb.OpenRecordset("select * from table1 order by project_name asc, ext_inv_amt desc;", dbOpenDynaset)

Do Until rstProjName.EOF
    rstDetailItms.FindFirst "project_name = '" & rstProjName!project_name & "'"
    rstSumInvItems!project_name = rstDetailItms!project_name
    rstSumInvItems!item_name = rstDetailItms!item_name
    rstSumInvItems!ext_inv_amt = DSum("ext_inv_amt", "table1", "project_name = '" & rstProjName!project_name & "'")

Set rstSumInvItems = Nothing
Set rstProjName = Nothing
Set rstDetailItms = Nothing
DoCmd.Hourglass False

End Function

Open in new window

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Eric ShermanAccountant/DeveloperCommented:
If you need help implementing my solution, let me know.
domain functions are just as much a slowness issue with large recordsets as subqueries are.    Additionally, the temp table causes bloat and so is undesirable unless there is no other way.  

Dale's solution is probably the optimum one for large recordsets but it still has the problem of multiple "top" values although that might be correctable by grouping all the columns in the outer query.

As i understand the documentation for Top value queries, if there is a unique identifier that is sequential and you include it in the sort, the query engine will use it to pick the actual top record.  I have not confirmed this though.
Dale FyeCommented:

I have confirmed in the past that you could add an ID field to the Order By Clause in order to get a single value in a query
SELECT Top 1 Address, Amount
FROM some table
ORDER BY Address, Amount DESC , ID
Eric ShermanAccountant/DeveloperCommented:
<<<<<domain functions are just as much a slowness issue with large recordsets as subqueries are.    Additionally, the temp table causes bloat and so is undesirable unless there is no other way.>>>>

I used the domain function DSum as a shortcut and you could easily aggregate the total in the recordset loop.  As far as bloat ... you simply clear the temp table on a report close event or other close event.  Set you db to compact on close.  It's really not a big issue.

I've seen this question pop up several times before.  The function that I posted is clean, fast, solid and it works.  

Compact on close is not desirable in a production database.

Aggregating the data using a query instead of dsum() simply replaces the dsum() with a dLookup() and Dale's solution uses a join so the aggregation happens only once instead of as a separate query row by row in the main recordset.

I'm sure your solution works fine.  However, using domain functions inside large loops is definitely expensive and less efficient than either of the other two methods proposed.  I was burned by DLookup() in one of my earliest Access projects.  The recordset was 110,000 rows and I used a code loop that included DLookup() because I was a newbie and that is what all the examples showed.  The procedure took 97 minutes.  Even though I was new to Access, I was an experienced programmer so I analyzed the loop and determined that the DLookup() was the costly element.  I replaced the DLookup() with a join and the procedure took 3 minutes.  I still use domain functions but never in a code loop and never in a query regardless of how large or small my outer recordset is.
Dale FyeCommented:

Back in the day, "Compact on Close" was not recommended.  However, I think that opinion has changed over the last couple of versions of Access.  I think I started using it regularly in my production database front ends with Version 2007.

My FE's are replaced each time they are opened so I never use compact on close.  I also work remotely so I don't want to have to wait for the compact to happen when I close the database and neither do the users.

When I need to use temp tables, I create a separate BE that can be discarded and never needs compacting rather than creating the temp tables in the FE.  Since I usually do this with a template, I can define indexes on the temp tables to optimize processing and all the bloat stays out of the FE.
Dale FyeCommented:
I frequently do much of the same Pat, especially with temp tables.

I don't generally copy a new FE to the users computer unless there is a new version posted on the server.
I use a batch file to do the copy and the user's short cut runs the batch file from a server directory so I can change it if I need to.  I started out with only pushing new versions but some other people develop bloat prone apps and so I went with the pull every time you open when I standardized the distribution mechanism.  It doesn't take long and it completely eliminates the bloat issue as well as the occasional corruption issue.
Eric ShermanAccountant/DeveloperCommented:
@PatHartman ...

There are pros and cons to using either methods  and nothing is absolute.  For solutions like this one ... depending on the environment, the machine, the server, the size of the data table  ... I generally test a SQL method and a VBA method to see which performs the best.

I simply provided an alternative to Dale's SQL method.  Let the user test them and see what works best for him before we start deciding what is optimal or not.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now