Solved

Need help finding highest dollar item on an invoice

Posted on 2016-09-08
14
23 Views
Last Modified: 2016-10-28
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

EX:
1234 W Main Street / Driveway / $4,550
625 W 55th Street / Patio / $2,450
0
Comment
Question by:kkirt1
  • 5
  • 4
  • 4
14 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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;
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 125 total points
Comment Utility
Or you might try:

SELECT yourTable.Address, yourTable.ItemName, T.TotAmt
FROM yourTable
INNER JOIN (
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.
0
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 125 total points
Comment Utility
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)

rstProjName.MoveFirst
Do Until rstProjName.EOF
    rstDetailItms.FindFirst "project_name = '" & rstProjName!project_name & "'"
    rstSumInvItems.AddNew
    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 & "'")
    rstSumInvItems.Update
    rstProjName.MoveNext
Loop

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

End Function

Open in new window

0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
If you need help implementing my solution, let me know.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
@etsherman,
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Pat,

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
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
@PatHartman
<<<<<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.  

ET
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Pat,

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.

Dale
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
@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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now