Solved

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

Posted on 2009-06-27
4
315 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:Wayne Barron
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 24729912
'!' == 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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 24729917
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24729946
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
 
LVL 31

Author Closing Comment

by:Wayne Barron
ID: 31597622
Thanks guys.
You both Rock!

Carrzkiss
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question