Max on Multiple Columns - SQL 8.0

[Renee]

I'm trying to pull the maximum DatePosted for each Vendor...

VName VCode OpID PostDate

ABC Rental 0001 1234 1/1/2008
Nottingham 0002 1234 2/3/2008
ABC Rental 0001 5678 8/9/2008
Fox Chevrolet 0003 5678 10/12/2008
Dolls Unity 0004 1234 3/4/2009
Chesapeake 0005 5678 11/6/2008
Dolls Unity 0004 1234 5/5/2008
Fox Chevrolet 0003 5678 3/4/2008
ABC Rental 0001 5678 4/3/2008

My desired result:

ABC Rental 0001 1234 8/9/2008
Nottinghan 0002 1234 2/3/2008
Fox Chevrolet 0003 5678 10/12/2008
Dolls Unity 0004 1234 5/5/2008
Chesapeake 0005 5678 11/2/2008

Any help would be most appreciated

 

[Tom Wickerath]

Hi Renee,

Are you sure that you are showing all the correct data in your desired
results listing? The reason I ask is that for ABC Rental, I think the OpID
should be 5678, for Chesapeake, I believe you really want a PostDate of
11/06/2008, for Dolls Unity it looks to me like the maximum PostDate is
03/04/2009 (instead of 5/5/2008). Here are the results that I just produced
for the query that I think you want:

VName Vcode OpID PostDate
ABC Rental 0001 5678 08/09/2008
Chesapeake 0005 5678 11/06/2008
Dolls Unity 0004 1234 03/04/2009
Fox Chevrolet 0003 5678 10/12/2008
Nottingham 0002 1234 02/03/2008

To get this result, first create a new query. Dismiss the Add Table dialog.
Click on View | SQL View. Replace the default "SELECT" with the following SQL
(Structured Query Language) statement. Note that in this query, I named the
table "TestData"--you can make the appropriate substitution for the name of
your actual table:

SELECT TestData.VName, Max(TestData.PostDate) AS MaxOfPostDate
FROM TestData
GROUP BY TestData.VName;

Save the above query as "qryMaxPostDate" (without the quotes, of course).
Now, create another new query and switch to SQL View once more. Paste the
following SQL statement in, again substituting for your actual table name:

SELECT TestData.VName, TestData.Vcode, TestData.OpID, TestData.PostDate
FROM qryMaxPostDate
INNER JOIN TestData
ON (qryMaxPostDate.MaxOfPostDate = TestData.PostDate)
AND (qryMaxPostDate.VName = TestData.VName)
ORDER BY TestData.VName;


I think this second query will produce your desired results.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

I'm trying to pull the maximum DatePosted for each Vendor...

VName VCode OpID PostDate

ABC Rental 0001 1234 1/1/2008
Nottingham 0002 1234 2/3/2008
ABC Rental 0001 5678 8/9/2008
Fox Chevrolet 0003 5678 10/12/2008
Dolls Unity 0004 1234 3/4/2009
Chesapeake 0005 5678 11/6/2008
Dolls Unity 0004 1234 5/5/2008
Fox Chevrolet 0003 5678 3/4/2008
ABC Rental 0001 5678 4/3/2008

My desired result:

ABC Rental 0001 1234 8/9/2008
Nottinghan 0002 1234 2/3/2008
Fox Chevrolet 0003 5678 10/12/2008
Dolls Unity 0004 1234 5/5/2008
Chesapeake 0005 5678 11/2/2008

Any help would be most appreciated

 

[Renee]

Hi Tom,
You were right about Dolls Unity, my maximum date was incorrect, but for the
others I wanted to stay the same, reason being, opid's are not exclusive.
Any Oper could post on any Vendor. I tried the script you gave in Access and
in SQL Analyzer, with no luck.

I'm only running Access 2003, if that helps, but I'd rather just go straight
to Query Analyzer. Anything to help is appreciated!!!

Here's my query, and the results I'm getting. I only want the entire row
with the most
date for each WSPVCode...


select WSPVcode,
WSPVname,
ShuserCode,
max(WSDatePosted)as MaxDate
from Faster.WSublet
inner join Faster.SHeader
on SHopId=WSopId
where WSDatePosted<>0
group by WSPVcode,WSPVname,SHUserCode
order by WSPVcode

WSPVCode WSPVName MaxDate SHUserCode
000154 A-1 DRIVE SHAFT 20021119 3010
000154 A-1 DRIVE SHAFT 20040301 3514
000154 A-1 DRIVE SHAFT 20030507 4007
000154 A-1 DRIVE SHAFT 20030104 4008
000154 A-1 DRIVE SHAFT 20051129 4018
000154 A-1 DRIVE SHAFT 20080722 6004
000154 A-1 DRIVE SHAFT 20041117 6012
000154 A-1 DRIVE SHAFT 20040624 STU1
000154 A-1 DRIVE SHAFT 20040205 STU3
000154 A-1 DRIVE SHAFT 20050823 TMP1
011610 ADSCOM CORP. 20040205 STU3
011610 ADSCOM CORP. 20040218 VAL
011725 ADVANCED TANK SYSTEMS,INC 20051116 4018
011725 ADVANCED TANK SYSTEMS,INC 20081014 6004
011725 ADVANCED TANK SYSTEMS,INC 20040707 STU1
011725 ADVANCED TANK SYSTEMS,INC 20031009 VAL
014310 ALBAN TRACTOR CO. INC. 20041202 3015
014310 ALBAN TRACTOR CO. INC. 20050317 3016
014310 ALBAN TRACTOR CO. INC. 20050210 3513
014310 ALBAN TRACTOR CO. INC. 20030306 4008
014310 ALBAN TRACTOR CO. INC. 20050929 4018
014310 ALBAN TRACTOR CO. INC. 20060523 4019
014310 ALBAN TRACTOR CO. INC. 20081209 6004
014310 ALBAN TRACTOR CO. INC. 20031211 STU3
014310 ALBAN TRACTOR CO. INC. 20031015 TMP4
014310 ALBAN TRACTOR CO. INC. 20040520 VAL
019783 ALTEC INC 20040806 3008
019783 ALTEC INC 20090218 6004
019783 ALTEC INC 20060606 AB
019783 ALTEC INC 20040611 STU1
058940 AUTO BARN, INC 20030610 1010
058940 AUTO BARN, INC 20031003 2003
058940 AUTO BARN, INC 20040112 3010
058940 AUTO BARN, INC 20050111 3513
058940 AUTO BARN, INC 20051129 4018
058940 AUTO BARN, INC 20050329 5046
058940 AUTO BARN, INC 20050819 AB
058940 AUTO BARN, INC 20061108 AID1
058940 AUTO BARN, INC 20090326 CL
058940 AUTO BARN, INC 20080219 STU1
058940 AUTO BARN, INC 20061213 STU2
058940 AUTO BARN, INC 20061016 STU3
058940 AUTO BARN, INC 20040309 TMP1
058940 AUTO BARN, INC 20030729 TMP3
058940 AUTO BARN, INC. 20021217 2003
058940 AUTO BARN, INC. 20030216 3004
058940 AUTO BARN, INC. 20030403 3506

 

[Bob Quintal]

Hi Tom,
You were right about Dolls Unity, my maximum date was incorrect,
but for the others I wanted to stay the same, reason being, opid's
are not exclusive. Any Oper could post on any Vendor. I tried
the script you gave in Access and in SQL Analyzer, with no luck.

I'm only running Access 2003, if that helps, but I'd rather just
go straight to Query Analyzer. Anything to help is appreciated!!!

Here's my query, and the results I'm getting. I only want the
entire row with the most
date for each WSPVCode...

you need to
SELECT WSPVCode,
max(WSDatePosted)as MaxDate
FROM Faster.WSublet
GROUP BY WSPVCode
ORDER BY WSPVCode;

then inner join both columns from the above to the below, which could
be simplified to remove the max() and the group by statements

Q

 

[Tom Wickerath]

Hi Renee,

Did the suggestion that Bob Quintal posted help you out?
Note to Bob: Thanks for following up!

I tried the script you gave in Access and in SQL Analyzer, with no luck.
I'm only running Access 2003, if that helps,

Very strange, because I worked up the example using Access 2003.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

Hi Tom,
You were right about Dolls Unity, my maximum date was incorrect, but for the
others I wanted to stay the same, reason being, opid's are not exclusive.
Any Oper could post on any Vendor. I tried the script you gave in Access and
in SQL Analyzer, with no luck.

I'm only running Access 2003, if that helps, but I'd rather just go straight
to Query Analyzer. Anything to help is appreciated!!!

Here's my query, and the results I'm getting. I only want the entire row
with the most date for each WSPVCode...


select WSPVcode,
WSPVname,
ShuserCode,
max(WSDatePosted)as MaxDate
from Faster.WSublet
inner join Faster.SHeader
on SHopId=WSopId
where WSDatePosted<>0
group by WSPVcode,WSPVname,SHUserCode
order by WSPVcode

WSPVCode WSPVName MaxDate SHUserCode
000154 A-1 DRIVE SHAFT 20021119 3010
000154 A-1 DRIVE SHAFT 20040301 3514
000154 A-1 DRIVE SHAFT 20030507 4007
000154 A-1 DRIVE SHAFT 20030104 4008
000154 A-1 DRIVE SHAFT 20051129 4018
000154 A-1 DRIVE SHAFT 20080722 6004
000154 A-1 DRIVE SHAFT 20041117 6012
000154 A-1 DRIVE SHAFT 20040624 STU1
000154 A-1 DRIVE SHAFT 20040205 STU3
000154 A-1 DRIVE SHAFT 20050823 TMP1
011610 ADSCOM CORP. 20040205 STU3
011610 ADSCOM CORP. 20040218 VAL
011725 ADVANCED TANK SYSTEMS,INC 20051116 4018
011725 ADVANCED TANK SYSTEMS,INC 20081014 6004
011725 ADVANCED TANK SYSTEMS,INC 20040707 STU1
011725 ADVANCED TANK SYSTEMS,INC 20031009 VAL
014310 ALBAN TRACTOR CO. INC. 20041202 3015
014310 ALBAN TRACTOR CO. INC. 20050317 3016
014310 ALBAN TRACTOR CO. INC. 20050210 3513
014310 ALBAN TRACTOR CO. INC. 20030306 4008
014310 ALBAN TRACTOR CO. INC. 20050929 4018
014310 ALBAN TRACTOR CO. INC. 20060523 4019
014310 ALBAN TRACTOR CO. INC. 20081209 6004
014310 ALBAN TRACTOR CO. INC. 20031211 STU3
014310 ALBAN TRACTOR CO. INC. 20031015 TMP4
014310 ALBAN TRACTOR CO. INC. 20040520 VAL
019783 ALTEC INC 20040806 3008
019783 ALTEC INC 20090218 6004
019783 ALTEC INC 20060606 AB
019783 ALTEC INC 20040611 STU1
058940 AUTO BARN, INC 20030610 1010
058940 AUTO BARN, INC 20031003 2003
058940 AUTO BARN, INC 20040112 3010
058940 AUTO BARN, INC 20050111 3513
058940 AUTO BARN, INC 20051129 4018
058940 AUTO BARN, INC 20050329 5046
058940 AUTO BARN, INC 20050819 AB
058940 AUTO BARN, INC 20061108 AID1
058940 AUTO BARN, INC 20090326 CL
058940 AUTO BARN, INC 20080219 STU1
058940 AUTO BARN, INC 20061213 STU2
058940 AUTO BARN, INC 20061016 STU3
058940 AUTO BARN, INC 20040309 TMP1
058940 AUTO BARN, INC 20030729 TMP3
058940 AUTO BARN, INC. 20021217 2003
058940 AUTO BARN, INC. 20030216 3004
058940 AUTO BARN, INC. 20030403 3506

 

[Renee]

I'm pretty new to SQL, so maybe that's why I'm not getting it. Bob wrote for
me to inner join the columns... (where / how?) I interpreted simplifying it,
to mean, either do the aforemention, or ... remove the max statement and the
group by statements, and
just use:

Select
WSPVCode,
WSPVname,
from Faster.WSublet
inner join Faster.SHeader
on SHopId=WSOpID
Where WSDatePosted<>0
Order by WSPVcode;

....which of course just gave me a lot of duplicates. Maybe this will help:

Table "WSublet" contains:
WSPVName
WSPVCode
WSDatePosted
WSOpId

and Table SHeader contains:
SHOpId
SHUserCode

(SHUserCode is the actual name for WSOpId and SHOpId)

 

[Tom Wickerath]

Hi Renee,

I'm pretty new to SQL, so maybe that's why I'm not getting it. Bob wrote for
me to inner join the columns... (where / how?)

Bob gave you two SQL statements, which you can use to create two new queries
in your database. I believe he then wanted you to create a third query,
adding the two queries that he provided, as if they were tables, to the new
query. To create the Inner Joins, you would simply drag and drop the
WSPVCode and MaxDate fields from one "table" (1st saved query) to the other
"table" (2nd saved query), in query design view for your new third table. An
inner join will not display arrow heads on the join line; this type of join
will most likely be the default join that you get. Does that help?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________


I'm pretty new to SQL, so maybe that's why I'm not getting it. Bob wrote
for
me to inner join the columns... (where / how?) I interpreted simplifying
it,
to mean, either do the aforemention, or ... remove the max statement and the
group by statements, and
just use:

Select
WSPVCode,
WSPVname,
from Faster.WSublet
inner join Faster.SHeader
on SHopId=WSOpID
Where WSDatePosted<>0
Order by WSPVcode;

....which of course just gave me a lot of duplicates. Maybe this will help:

Table "WSublet" contains:
WSPVName
WSPVCode
WSDatePosted
WSOpId

and Table SHeader contains:
SHOpId
SHUserCode

(SHUserCode is the actual name for WSOpId and SHOpId)