Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Subquery to get max(date) from transaction file by customer

Posted on 2007-10-07
3
Medium Priority
?
196 Views
Last Modified: 2008-01-09
Hi Experts,

I have a transaction journal file with customer sales and related information.

One field is a sales code, which can change overtime, and isn't always entered (it should be mandatory input but currently isn't).  I need to come up with a query that gives me a Name and the sales code from the latest dated transaction in the case where there is one, or "none" if there isn't one at all in any of the transactions for that company.

I have attempted a subquery with max(date) but it is taking forever to run....

This is what I have currently:

SELECT QTL.Name, QTL.Salescode
FROM QuickenTrxList AS QTL
WHERE (((QTL.Date)=(SELECT Max(Date) From QuickenTrxList as S WHERE S.Name = QTL.Name )));

Deedub
0
Comment
Question by:deedub84
  • 2
3 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 20031401
SELECT Name, Nz(Salescode,"none") As Saves_Code, Max(Date) FROM QuickenTrxList Group By Name, Nz(Salescode,"none") Order By Name

Mike
0
 
LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 800 total points
ID: 20031404
correction...

SELECT Name, Nz(Salescode,"none") As Saves_Code, Max(Date) As RecentDate FROM QuickenTrxList Group By Name, Nz(Salescode,"none") Order By Name

Mike
0
 
LVL 58

Accepted Solution

by:
harfang earned 1200 total points
ID: 20031453
Try this subquery, but make sure you have an index or a relationship on QTL.Name!

SELECT QTL.Name, QTL.Salescode
FROM QuickenTrxList AS QTL
WHERE QTL.Date >= ALL (
  SELECT S.Date From QuickenTrxList as S WHERE S.Name = QTL.Name
  )

And use Mike's Nz() suggestion to handle cases where Salescode is null.

(°v°)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month20 days, 16 hours left to enroll

810 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