Maximum Date Query Problem

[SmokyMtnzz]

Hello everyone,

I would have thought that this query would be very, very simple to
create, but I'm having problems. I want to return the very last dated
PTC of each item. As you can see below, I get EVERY entry of Item, not
just the maximum dated one. So, I don't want to see the Item 106
$1.29 because it is not the last MaxOfPTCChgDate. Same with the item
125 $1.29. 101, 102, 103, etc. are ok because there is only one entry
listed. (For simplicity I pulled my data from PTC Query into another
query, instead of pulling from an actual table.)

CustID Item PTC MaxOfPTCChgDate
01813 101 $5.59 7/11/2005
01813 102 $6.39 7/11/2005
01813 103 $8.99 7/11/2005
01813 106 $1.29 7/11/2005
01813 106 $1.59 7/12/2005
01813 107 $14.99 7/11/2005
01813 113 $5.59 7/11/2005
01813 114 $8.99 7/11/2005
01813 125 $1.29 7/12/2005
01813 125 $1.39 7/11/2005


SELECT [PTC Query].CustID, [PTC Query].Item, [PTC Query].PTC, Max([PTC
Query].PTCChgDate) AS MaxOfPTCChgDate
FROM [PTC Query]
GROUP BY [PTC Query].CustID, [PTC Query].Item, [PTC Query].PTC
HAVING ((([PTC Query].CustID)="01813"))
ORDER BY [PTC Query].Item;

Above is the sql that I am using. Please let me know what I'm doing
wrong so I can learn more about sql.

Thank you for any help you can provide.

Bob

 

[Guest]

It is working like it is suppose to. They are all the same date and
therefore the maximum date. If you want the last one then you need to add
time with it.

 

[John Spencer (MVP)]

What you are doing wrong is grouping by the PTC column. Try rewriting this

SELECT [PTC Query].CustID, [PTC Query].Item, [PTC Query].PTC,
[PTC Query].PTCChgDate
FROM [PTC Query]
WHERE [PTC Query].PTCChgDate =
(SELECT Max(Tmp.PTCChgDate) AS MaxOfPTCChgDate
FROM [PTC Query] as Tmp
WHERE Tmp.Item =[PTC Query].Item AND
Tmp.PTC = [PTC Query].PTC AND
Tmp.CustID = [PTC Query].CustID)
AND [PTC Query].CustID = "01813"
ORDER BY [PTC Query].Item

You can also do this in two queries. First query gets the CustId, the PTC, and
the MaxDate and then you join that to PTC Query on Custid, PTC, and PTCChgDate.

 

[SmokyMtnzz]

Thank you John for the quick reply! I tried your new sql that you
suggested and it came back with the exact same info that I got before.
Your second suggestion about the two queries didn't make sense to me
at first because it left out Item. I then did what you suggested and
created a couple of new queries in design view. On the final new query
below is what the design view created in sql. It seems to work
perfect.

Thank you so much for your help!

Bob

from the first new query:

SELECT [PTC Query].CustID, [PTC Query].Item, Max([PTC
Query].PTCChgDate) AS MaxOfPTCChgDate
FROM [PTC Query]
GROUP BY [PTC Query].CustID, [PTC Query].Item
ORDER BY [PTC Query].CustID;


from the second and final new query:

SELECT [PTC Query].CustID, [PTC Query].Item, [PTC Query].PTC
FROM Query4 INNER JOIN [PTC Query] ON (Query4.CustID = [PTC
Query].CustID) AND (Query4.MaxOfPTCChgDate = [PTC Query].PTCChgDate)
AND (Query4.Item = [PTC Query].Item)
WHERE ((([PTC Query].CustID)="01813"));

What you are doing wrong is grouping by the PTC column. Try rewriting this

SELECT [PTC Query].CustID, [PTC Query].Item, [PTC Query].PTC,
[PTC Query].PTCChgDate
FROM [PTC Query]
WHERE [PTC Query].PTCChgDate =
(SELECT Max(Tmp.PTCChgDate) AS MaxOfPTCChgDate
FROM [PTC Query] as Tmp
WHERE Tmp.Item =[PTC Query].Item AND
Tmp.PTC = [PTC Query].PTC AND
Tmp.CustID = [PTC Query].CustID)
AND [PTC Query].CustID = "01813"
ORDER BY [PTC Query].Item

You can also do this in two queries. First query gets the CustId, the PTC, and
the MaxDate and then you join that to PTC Query on Custid, PTC, and PTCChgDate.

Hello everyone,

I would have thought that this query would be very, very simple to
create, but I'm having problems. I want to return the very last dated
PTC of each item. As you can see below, I get EVERY entry of Item, not
just the maximum dated one. So, I don't want to see the Item 106
$1.29 because it is not the last MaxOfPTCChgDate. Same with the item
125 $1.29. 101, 102, 103, etc. are ok because there is only one entry
listed. (For simplicity I pulled my data from PTC Query into another
query, instead of pulling from an actual table.)

CustID Item PTC MaxOfPTCChgDate
01813 101 $5.59 7/11/2005
01813 102 $6.39 7/11/2005
01813 103 $8.99 7/11/2005
01813 106 $1.29 7/11/2005
01813 106 $1.59 7/12/2005
01813 107 $14.99 7/11/2005
01813 113 $5.59 7/11/2005
01813 114 $8.99 7/11/2005
01813 125 $1.29 7/12/2005
01813 125 $1.39 7/11/2005

SELECT [PTC Query].CustID, [PTC Query].Item, [PTC Query].PTC, Max([PTC
Query].PTCChgDate) AS MaxOfPTCChgDate
FROM [PTC Query]
GROUP BY [PTC Query].CustID, [PTC Query].Item, [PTC Query].PTC
HAVING ((([PTC Query].CustID)="01813"))
ORDER BY [PTC Query].Item;

Above is the sql that I am using. Please let me know what I'm doing
wrong so I can learn more about sql.

Thank you for any help you can provide.

Bob

 

[Van T. Dinh]

Slight alteration from John's original suggestion:

****Untested****
SELECT Main.CustID, Main.Item, Main.PTC, Main.PTCChgDate

FROM [PTC Query] AS Main

WHERE
( Main.PTCChgDate =
(
SELECT Max(Sub.PTCChgDate) AS MaxOfPTCChgDate
FROM [PTC Query] as Sub
WHERE (Sub.Item = Main.Item)
AND (Sub.CustID = Main.CustID)
)
)
AND (Main.CustID = "01813" )

ORDER BY [Main].Item
********

See if this works for you.

 

[SmokyMtnzz]

Yes Van, this one did work. Now I can sit back and relax while I
decipher and learn exactly what you and John wrote. I understand some
of it but I need to look at it some more. Ahh, the fun of learning
something new! Much thanks to you and John with the help.

Bob

 

[Van T. Dinh]

Check Access Help Topic "SQL SubQueries". I can't find this topic in the
Index or the Answer Wizard of Access Help (2002) but you can find it through
the Contents tab:

Microsoft Access Help / Microsoft JET SQL Reference / Microsoft JET SQL
Reference (again) / Data Manipulation Language / SQL Subqueries.

There is also an excellent article on The Access Web:

http://www.mvps.org/access/queries/qry0020.htm