Group and Sorting - Group Names
I created query to Sort by Room, Points, and finally Name. I would like to group by Name without affecting the Room and Point sorting and be able to group the names. How can I accomplish this?
Sample Data
Sorted By Room, Point, Name
Name...............................Room....Point
Eddie Van Halen.................51x50....20
Robert Johnson..................30x20....30
Joe Satriani.......................20x50....11
Yngwie Malmsteen..............20x25....12
Steve Vai......................... 10x20....19
Eddie Van Halen.................10x10.....20
Would like to sort by size, points, and name but somehow group by name if the person is in the listing more than once. In the listing below by sorting by size points, and name the line number 2 would be last entry.
Desired Result
1. Eddie Van Halen.................51x50....20
2. Eddie Van Halen.................10x10.....20
3. Robert Johnson..................30x20....30
4. Joe Satriani........................20x50....11
5. Yngwie Malmsteen...............20x25....12
6. Steve Vai..........................10x20....19
Your help would be greatly appreciated.
Here's the SQL code:
Code:
SELECT TBLGuitarist.Rid, TBLGuitarist.lname, TBLGuitarist.fname, TBLGuitarist.point, First(TBLRoom.Size) AS [Size], First(TBLMfg.MFG) AS MFG,
FROM ((TBLRoom INNER JOIN TBLGuitarist ON TBLRoom.Rid = TBLGuitarist.Rid) INNER JOIN TBLProfile ON (TBLGuitarist.Rid = exTBLProfile.Rid) AND (TBLRoom.locid = exTBLProfile.locid)) INNER JOIN TBLMfg ON exTBLProfile.lines = exTBLMfg.MFG
GROUP BY TBLGuitarist.lname, TBLGuitarist.fname, TBLGuitarist.Rid, TBLGuitarist.point, TBLRoom.category, TBLGuitarist.currshow
HAVING (((TBLGuitarist.currshow)=True))
ORDER BY TBLGuitarist.point DESC , First(TBLRoom.Size) DESC;
Re: Group and Sorting - Group Names
Name...............................Size....Point
Eddie Van Halen.................51x50....20
Akira Takasaki...................40x29....21
Eddie Van Halen.................39x20....20
Robert Johnson..................30x20....30
Ted Nugent.......................30x20....25
Joe Satriani.......................20x50....11
Yngwie Malmsteen..............20x25....12
Robert Johnson...................10x10....30
Eddie Van Halen..................10x10....20
Steve Vai..........................10x10....19
Is there a way of doing it like this?
Name...............................Size....Point
Eddie Van Halen.................51x50....20
Eddie Van Halen.................39x20....20
Eddie Van Halen..................10x10....20
Akira Takasaki...................40x29....21
Robert Johnson..................30x20....30
Robert Johnson...................10x10....30
Ted Nugent.......................30x20....25
Joe Satriani.......................20x50....11
Yngwie Malmsteen..............20x25....12
Steve Vai..........................10x10....19
Re: Group and Sorting - Group Names
Table containing the guitarist information is where I gather all the contact information and is assigned a unique record id. The profile is where I gather items such as equipment being used and brand names. I then assign equipment to be stored in rooms based on models and size. The information passed to the rooms is the record id from the guitarist file. The guitarist can have multiple rooms with all sorts of different equipment. I am trying to gather all the rooms assigned to guitarist based on size and then seniority points. So size and then points are very important in this report but just as critical is to be able to gather all the guitarist record and group them like this:
Size....Point..................Name
51x50....20................. .Eddie Van Halen
39x20....20................. .Eddie Van Halen
10x10....20...................Eddie Van Halen
40x29....21...................Akira Takasaki
30x20....30.................. Robert Johnson
10x10....30...................Robert Johnson
30x20....25...................Ted Nugent
20x50....11...................Joe Satriani
20x25....12...................Yngwie Malmsteen
10x10....19................... Steve Vai
Re: Group and Sorting - Group Names
Please if u can try this
SELECT TBLGuitarist.Rid, TBLGuitarist.lname, TBLGuitarist.fname, TBLGuitarist.point, Max(TBLRoom.Size) AS [Size], Max(TBLMfg.MFG) AS MFG,
FROM ((TBLRoom INNER JOIN TBLGuitarist ON TBLRoom.Rid = TBLGuitarist.Rid) INNER JOIN TBLProfile ON (TBLGuitarist.Rid = exTBLProfile.Rid) AND (TBLRoom.locid = exTBLProfile.locid)) INNER JOIN TBLMfg ON exTBLProfile.lines = exTBLMfg.MFG
GROUP BY TBLGuitarist.lname, TBLGuitarist.fname, TBLGuitarist.Rid
HAVING (((TBLGuitarist.currshow)=True))
ORDER BY TBLGuitarist.point DESC , First(TBLRoom.Size) DESC;