Get the top (1) Record DESC from all members (Access Code supplied)

SQL Server 2005 Ent.

Trying to get the top (1) record DESC returned back for all members (1 record per member).

This was working in Access, but am getting an error about the    
Incorrect syntax near '!'.

I am in the process of attempting to re-write this in the SQL Query Designer.
I can get the top records per indivisual user. That is a no brainer.
But trying to get this one is proving to be a little more difficult.

Any and all suggestions are welcomed.

Carrzkiss
SELECT Members.Id AS MyID, Members.UserName AS MUname, (SELECT TOP 1 WTID FROM Tracker WHERE (((MyID) = [Members]![Id])) ORDER BY WTID DESC) AS MWTID, (SELECT TOP 1 WeightTime FROM Tracker WHERE (((MyID) = [Members]![Id])) ORDER BY WTID DESC) AS MaxOfWeightTime, (SELECT WeightNow FROM Tracker WHERE (((MyID) = [Members]![Id])) AND WTID = (SELECT TOP 1 WTID FROM Tracker WHERE  (((MyID) = [Members]![Id])) ORDER BY WTID DESC)) AS MaxOfWeightNow, WeightTable.MyW, WeightTable.MyWG, Members.MyLN, Members.MyFN FROM (Members INNER JOIN Tracker ON Members.Id = Tracker.MyID) INNER JOIN WeightTable ON Members.Id = WeightTable.MID GROUP BY Members.Id, Members.UserName, WeightTable.MyW, WeightTable.MyWG, Members.MyLN, Members.MyFN

Open in new window

LVL 31
Wayne BarronAuthor, Web DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
'!' == a MS Access syntax for separating portions of a qualified name; however, this is not valid in MS SQL Server, you will have to use '.'.

You can do this for what you are trying to do:
SELECT Members.Id AS MyID
, Members.UserName AS MUname
, Tracker.WTID AS MWTID
, Tracker.WeightTime AS MaxOfWeightTime
, Tracker.WeightNow AS MaxOfWeightNow
, WeightTable.MyW, WeightTable.MyWG, Members.MyLN, Members.MyFN 
FROM Members 
INNER JOIN Tracker ON Members.Id = Tracker.MyID 
INNER JOIN WeightTable ON Members.Id = WeightTable.MID 
INNER JOIN (SELECT MyID, MAX(WTID) AS MAX_WTID FROM Tracker GROUP BY MyID) tMax ON tMax.MyID = Tracker.MyId AND tMax.MAX_WTID = Tracker.WTID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT Members.Id AS MyID, Members.UserName AS MUname,
(SELECT TOP 1 WTID FROM Tracker WHERE (((MyID) = [Members].[Id])) ORDER BY WTID DESC) AS MWTID,
(SELECT TOP 1 WeightTime FROM Tracker WHERE (((MyID) = [Members].[Id])) ORDER BY WTID DESC) AS MaxOfWeightTime,
(SELECT WeightNow FROM Tracker WHERE (((MyID) = [Members].[Id])) AND WTID = (SELECT TOP 1 WTID FROM Tracker WHERE  (((MyID) = [Members].[Id])) ORDER BY WTID DESC)) AS MaxOfWeightNow,
WeightTable.MyW, WeightTable.MyWG, Members.MyLN, Members.MyFN
FROM (Members INNER JOIN Tracker ON Members.Id = Tracker.MyID)
INNER JOIN WeightTable ON Members.Id = WeightTable.MID
GROUP BY Members.Id, Members.UserName, WeightTable.MyW, WeightTable.MyWG, Members.MyLN, Members.MyFN
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
OK.
Both codes give the same identical results.

@mwvisa1:
I was searching for the MAX()
To be used with SQL Server prior to posting, but did not find nothing on it.
It is good to know that it is there to use...

---------
@aneeshattingal:
Your provided code worked like a charm.
I see that the only thing that was done was the removale of the ! and replaced with a   . period
------------------------------------

After seeing both codes, I am going to stick with the one that I am using, as it is used in several other places with other Tables. So instead of re-doing my code, I am going to continue with what I have.

Thanks to both of you for your help on this one tonight.
Love the knowledge.

1 question though.
WHY in the world does the SQL Query code so scattered for?
I have never understood that.

Thanks again guys.
Carrzkiss
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Thanks guys.
You both Rock!

Carrzkiss
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.