Solved

Need help finding highest dollar item on an invoice

Posted on 2016-09-08
14
30 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
14 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 41790416
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 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 125 total points
ID: 41790552
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
ID: 41791513
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41791519
If you need help implementing my solution, let me know.
0
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41791659
@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 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 41791675
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41791709
@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 37

Expert Comment

by:PatHartman
ID: 41791778
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 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 41791812
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 37

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41791868
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 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 41791943
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 37

Expert Comment

by:PatHartman
ID: 41791954
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
ID: 41791981
@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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

710 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