Solved

Highest Values in Query

Posted on 2012-03-31
6
291 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
ID: 37791362
Use a grouping query:

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37791365
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
ID: 37791367
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Closing Comment

by:DatabaseDek
ID: 37791413
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
ID: 37791475
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
ID: 37792072
Thank you.That's interesting.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

831 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