Pardon me if this question already been asked before.
I tried to sort a query to meet my desire. Here is the example: - Date Customer Supplier Price
-
31-Oct-09 Charlie Australia $ 100
-
20-Oct-09 Omega Singapore $ 50
-
4-Oct-09 Charlie Australia $ 20
-
5-Oct-09 Omega Singapore $ 60
-
15-Oct-09 Charlie China $ 200
-
20-Oct-09 Charlie China $ 300
My goal is to get latest price for each customer and supplier. Which the result should be: - Date Customer Supplier Price
-
31-Oct-09 Charlie Australia $ 100
-
20-Oct-09 Omega Singapore $ 50
-
20-Oct-09 Charlie China $ 300
I tried to descending the date order and set Last on Date and Price at the Total row. But it doesn't effect a thing.
Please help me on this case.
Regards,
Eko/Indonesia
5 12956
This is not one of my stronger areas, but I do know of one way of getting the results you desire. It will require 2 queries.
1. Assuming the data is stored in a table called "Customer", this is the first query: - SELECT Max(Customer.Date) AS MaxOfDate, Customer.Customer, Customer.Supplier
-
FROM Customer
-
GROUP BY Customer.Customer, Customer.Supplier;
2. Assuming the first query is named "qryMaxCustomer", this is the second query, which should return the data you desire: - SELECT qryMaxCustomer.MaxOfDate, qryMaxCustomer.Customer, qryMaxCustomer.Supplier, Customer.Price
-
FROM Customer INNER JOIN qryMaxCustomer ON (Customer.Supplier = qryMaxCustomer.Supplier) AND (Customer.Customer = qryMaxCustomer.Customer) AND (Customer.Date = qryMaxCustomer.MaxOfDate);
Let me know if you have any questions.
-AJ
NeoPa 32,556
Recognized Expert Moderator MVP
For that sort of query I normally use a technique where I join the fields together in a string; find the maximum; then extract the data I'm interested in from the resultant string and convert it if required.
In this case that would translate to : - SELECT Max([Date]) AS MaxDate,
-
[Customer],
-
[Supplier],
-
Val(Mid(Max(Format([Date],'yyyymmddHHnnss') &
-
[Price]),15)) AS LastPrice
-
-
FROM [SomeTable]
-
-
GROUP BY [Customer],
-
[Supplier]
Dear AJ,
Thank you so much for the answer.
Since I don't understand SQL, I tried to apply your method by using query design view, and it works very well. Wow using 2 queries, why didn't I think of that.
And for NeoPa, I appreciate your answer and I'll learn more about your method.
Regards,
Eko
NeoPa 32,556
Recognized Expert Moderator MVP
SQL is fun. Remember though, if you're still quite new to it, that every QueryDef (saved query in Access) has SQL associated with it. Simply select View / SQL View and View / Design View to switch between two different, but matching, views of the same QueryDef. Each view allows design changes to it. This means you can always Copy the SQL of any QueryDef to be posted for instance. It also means that SQL already copied can be Pasted into the SQL View of a query to change it completely to reflect that new SQL. Very powerful concept.
NeoPa 32,556
Recognized Expert Moderator MVP
Another point to ponder is that AJ's solution can be combined into a single query if you use a subquery (See Subqueries in SQL). - SELECT tC.Customer,
-
tC.Supplier,
-
tC.Date,
-
tC.Price
-
-
FROM Customer AS tC INNER JOIN
-
(
-
SELECT [Customer],
-
[Supplier],
-
Max([Date]) AS MaxDate
-
-
FROM [Customer]
-
-
GROUP BY [Customer],
-
[Supplier]
-
) AS subQ
-
ON (tC.Customer=subQ.Customer)
-
AND (tC.Supplier=subQ.Supplier)
-
AND (tC.Date=subQ.MaxDate)
PS. I'm very glad AJ posted this technique as I've been using the other for so long I forgot about this (more standard/typical) one. I prefer my one for one or two fields, but when there are a number of them then this method wins hands down (& I forgot to use it recently - Kicks self).
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Steve |
last post by:
I'm trying to do a multi table query that displays all the info I need
in one array, and I'm having problems figuring out how to do it.
The two tables are product and vendor. A vendor can be a...
|
by: Randell D. |
last post by:
Folks,
I have a SELECT that returns with multiple records - It works when I have a
LIMIT clause but when I try to include a GROUP BY clause, the select returns
nothing (ie no records, no...
|
by: Pasquale |
last post by:
Hello,
I wondering if there is a way to dynamically update a select list with
javascript from a database query without having to reload the page to
update the list?
|
by: kiqyou_vf |
last post by:
Hello all, I'm trying to figure out how to search a "key_words" column,
that contain key words (obviously) for that row. An example of what one
of the key_words contains is: "home interior...
|
by: Evan Smith |
last post by:
During a routine performance check using an event monitor, I
discovered a class of query whose performance has me baffled.
The monitor captured:
SELECT * FROM EWM_CASE fetch first 1 rows only...
| |
by: Redbeard |
last post by:
I am a newbie that is running Access 2003. I am trying to have a multi select listbox run a query that filter a form. I have tried several codes off the internet to try and just get the selected...
|
by: lostdawg |
last post by:
Hi,
I am having trouble with the following query.
I need to sort from a list of contacts the last date each was contacted.
This is to be represented in days so for instance:
0-42 days...
|
by: Cyprus106 |
last post by:
So I've got about a million records that I'm sifting through with a SELECT statement. Right now I've been saying SELECT * FROM MYTABLE WHERE MYFIELD='GUIDE'...
But I only want the first record it...
|
by: IAuditor |
last post by:
Access 2003, XP - I have a multi-table query that is losing data. 1 table is a lookup (Hierarchy), and the other 3 are virtual duplicates (all with a one-to-many relationship with the lookup) except...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |