asked on

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

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wayne Barron

ASKER

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

Wayne Barron

ASKER

Thanks guys.
You both Rock!

Carrzkiss