Solved

Highest Values in Query

Posted on 2012-03-31
6
289 Views
Last Modified: 2012-03-31
Hi All

I'm trying to create a query to get a list of purchase items that shows only the last time an item was purchased. The Table is a Purchase Item table where there are many duplicates.

I have a date field for each purchase (so the records are not duplicates really) I only want the ones with the latest date where there are duplicate discription, price and referance number.

How?
0
Comment
Question by:DatabaseDek
  • 3
  • 2
6 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
Use a grouping query:

SELECT ItemPurchased, Max(PurchaseDate) AS MostRecent
FROM YourTable
GROUP BY  ItemPurchased
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
select *
from [Purchase Item]
where [date] =(select max([date]) from [Purchase Item] as p where p.[referance number]=[Purchase Item].[referance number]

you can use an itemID or productid if there is one, in place of [reference number]
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Or if you want to include all those fields:


SELECT ItemPurchased, description, Price, RefNo, Max(PurchaseDate) AS MostRecent
FROM YourTable
GROUP BY  ItemPurchased, description, Price, RefNo
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Closing Comment

by:DatabaseDek
Comment Utility
Just as a matter of interest is it possible to get the latest 2 records from the duplicates?.

Brilliantly simple. Thank you
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
So the top two records for each 'group'?

That is definitely doable, but a little more complicated.

This page has a good explanation:
http://www.sql-ex.ru/help/select16.php

The first solution in that thread will work in Access SQL.

The second does not - I believe it is specific to SQL Server.
0
 

Author Comment

by:DatabaseDek
Comment Utility
Thank you.That's interesting.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

728 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

10 Experts available now in Live!

Get 1:1 Help Now