kkirt1
asked on
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
EX:
1234 W Main Street / Driveway / $4,550
625 W 55th Street / Patio / $2,450
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you need help implementing my solution, let me know.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
@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
<<<<<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
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.
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.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 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.
@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.
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.
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;