Solved Lowest and Highest price and date (1 Viewer)

[sandy70]

hi all

is there anyway to find the highest and the lowest price related to date ??

The result i am looking for :

Item 01
Lowest > 170.000 > 22 May 22
Highest > 189.000 > 04 Aug 22

Item 02
Lowest > 171.000 > 27 Jan 22
Highest > 192.500 > 04 Aug 22

for the price i can get from query using max and min

for the date , still clue how to find it

tq all

 

[June7]

You want to return the lowest and highest records for each Description group. That does get complicated. Try building two TOP N for each group queries - one will be DESCENDING and one ASCENDING. Review http://allenbrowne.com/subquery-01.html#TopN. If you want all in one dataset, put those two queries in a UNION query.

Post your data as a text table, not an image, or provide a file (Excel, text, Access).

 

[sandy70]

Sample attached

 

[arnelgp]

see 1_LowPrice query and 2_HighPrice query.

 

[sandy70]

see 1_LowPrice query and 2_HighPrice query.

how about the Date ??

the Date i am looking for is 04 Aug 22

 

[June7]

Consider:

SELECT Sheet1.*
FROM Sheet1
WHERE InvNo IN (SELECT TOP 1 InvNo FROM Sheet1 AS Dup WHERE Dup.Des = Sheet1.Des ORDER BY Price, [Date] DESC)
UNION SELECT Sheet1.*
FROM Sheet1
WHERE InvNo IN (SELECT TOP 1 InvNo FROM Sheet1 AS Dup WHERE Dup.Des = Sheet1.Des ORDER BY Price DESC, [Date] DESC);

Note: I removed the space from InvNo field name. Really should use a more descriptive name than Date. Date is a reserved word.

 

[sandy70]

Consider:

SELECT Sheet1.*
FROM Sheet1
WHERE InvNo IN (SELECT TOP 1 InvNo FROM Sheet1 AS Dup WHERE Dup.Des = Sheet1.Des ORDER BY Price, [Date] DESC)
UNION SELECT Sheet1.*
FROM Sheet1
WHERE InvNo IN (SELECT TOP 1 InvNo FROM Sheet1 AS Dup WHERE Dup.Des = Sheet1.Des ORDER BY Price DESC, [Date] DESC);

Note: I removed the space from InvNo field name. Really should use a more descriptive name than Date. Date is a reserved word.

Tq for correction

The price is correct but the date is not

The result i am looking for

Item 01
Lowest > 22 May 22
Highest > 04 Aug 22

Item 02
Lowest > 27 Jan 22
Highest > 04 Aug 22

 

[arnelgp]

edit 2_HighPrice query in design view.
instead of Max, use Min on Date field.

 

[sandy70]

edit 2_HighPrice query in design view.
instead of Max, use Min on Date field.

tq so much its work

 

[arnelgp]

you can create the "final query" using the two numbered query:

SELECT
    [1_LowPrice].Description,
    [1_LowPrice].LowPrice,
    [1_LowPrice].MinOfDate AS DateLowPrice,
    [2_HighPrice].HighPrice,
    [2_HighPrice].MinOfDate AS DateHighPrice
FROM 1_LowPrice
    INNER JOIN 2_HighPrice
    ON [1_LowPrice].Description = [2_HighPrice].Description;

 

[June7]

The lowest price for Item1 is 170,000 and the associated date for that record is 5/28/2022. Why do you say 5/22/2022?
There are five high records for Item1 with price of 189,000 - you want the oldest date of 8/4/2022, not the most recent?

Why do you want 8/4/2022 for Item2 high?

Revised query achieves same results as arnelgp except it also includes InvNo.

SELECT Sheet1.*
FROM Sheet1
WHERE InvNo IN (SELECT TOP 1 InvNo FROM Sheet1 AS Dup WHERE Dup.Des = Sheet1.Des ORDER BY Price, [Date])
UNION SELECT Sheet1.*
FROM Sheet1
WHERE InvNo IN (SELECT TOP 1 InvNo FROM Sheet1 AS Dup WHERE Dup.Des = Sheet1.Des ORDER BY Price DESC, [Date])
ORDER BY Des, Price;

 

[sandy70]

The lowest price for Item1 is 170,000 and the associated date for that record is 5/28/2022. Why do you say 5/22/2022?
There are five high records for Item1 with price of 189,000 - you want the oldest date of 8/4/2022, not the most recent?

Why do you want 8/4/2022 for Item2 high?

Revised query achieves same results as arnelgp except it also includes InvNo.

SELECT Sheet1.*
FROM Sheet1
WHERE InvNo IN (SELECT TOP 1 InvNo FROM Sheet1 AS Dup WHERE Dup.Des = Sheet1.Des ORDER BY Price, [Date])
UNION SELECT Sheet1.*
FROM Sheet1
WHERE InvNo IN (SELECT TOP 1 InvNo FROM Sheet1 AS Dup WHERE Dup.Des = Sheet1.Des ORDER BY Price DESC, [Date])
ORDER BY Des, Price;

for the lowest price Date , i am sorry I tipe the wrong date

You right its 28 May 22

for the highest Price Date , my boss want to know when the highest price start to begin