SQL Group By problem (1 Viewer)

[K_ZEON]

I have data that i want to list in a grid.
it contains the following data structure

GroupID, PartNumber, PartDesc , Date_Out, Date_In, History ,Log_Date

when a job is opened on date_Out it gets given a GroupID (number) so that i know each history update this record belongs too.

ie.
1 , 010-111 , Compressor , 12/12/10 , 15/12/10 , Test Data , 13/12/10
1 , 010-111 , Compressor , 12/12/10 , 15/12/10 , Test Data 2 , 14/12/10
2 , 010-222 , Filter , 13/12/10 , 14/12/10 , Test Data 3 , 14/12/10
1 , 010-111 , Compressor , 12/12/10 , 15/12/10 , Test Data 4 , 15/12/10
3 , 010-111 , Compressor , 16/12/10 , 19/12/10 , Test Data 5 , 18/12/10

i would like to group the info by GroupID then sort by PartNumber and also by Date_Out , so it now becomes

3 , 010-111 , Compressor , 16/12/10 , 19/12/10 , Test Data 5 , 18/12/10
1 , 010-111 , Compressor , 12/12/10 , 15/12/10 , Test Data , 13/12/10
1 , 010-111 , Compressor , 12/12/10 , 15/12/10 , Test Data 2 , 14/12/10
1 , 010-111 , Compressor , 12/12/10 , 15/12/10 , Test Data 4 , 15/12/10
2 , 010-222 , Filter , 13/12/10 , 14/12/10 , Test Data 3 , 14/12/10

I have tried different variants on an SQL statement but it never gives be the correct group and sort.

hope someone can help

 

[Fuga]

What have you tried so far? I don´t thinkt there should be a problem

 

[K_ZEON]

Hi
Ive tried varations on the following

SELECT Parts_History.GroupID, Parts_History.PartNumber, Parts_History.PartDescription, Parts_History.Date_Out, Parts_History.Date_In, Parts_History.History, Parts_History.Log_Date
FROM Parts_History
ORDER BY Parts_History.GroupID , Parts_History.PartNumber, Parts_History.Date_Out DESC ,Parts_History.Date_In DESC ;

did try and add Group By GroupID but it seems i have to add all others which i dont want to group by

thanks

 

[vbaInet]

If you want to group, then you have to use Group By.

Set GroupID as the first column followed by PartNumber and Date_Out. Group By GroupID, sort by PartNumber followed by Date_Out. You can change the order of the columns in the sql view.

 

[K_ZEON]

just to let you know, i am using an MSAccess Database

are you saying to use

SELECT Parts_History.GroupID, Parts_History.PartNumber, Parts_History.PartDescription, Parts_History.Date_Out, Parts_History.Date_In, Parts_History.History, Parts_History.Log_Date
FROM Parts_History
ORDER BY Parts_History.GroupID , Parts_History.PartNumber, Parts_History.Date_Out DESC ,Parts_History.Date_In DESC
GROUP BY Parts_History.GroupID

as this throws up an error

 

[vbaInet]

You're writing the SQL yourself. Do it in the query design grid. Order By comes after Group By.

 

[K_ZEON]

my bad. sorry did it that way for quickness

SELECT Parts_History.GroupID, Parts_History.PartNumber, Parts_History.PartDescription, Parts_History.Date_Out, Parts_History.Date_In, Parts_History.History, Parts_History.Log_Date
FROM Parts_History
GROUP BY Parts_History.GroupID
ORDER BY Parts_History.GroupID , Parts_History.PartNumber, Parts_History.Date_Out DESC ,Parts_History.Date_In DESC

but still throws up error

this does work but the GroupID numbers that get listed are not grouped

SELECT Parts_History.GroupID, Parts_History.PartNumber, Parts_History.PartDescription, Parts_History.Date_Out, Parts_History.Date_In, Parts_History.History, Parts_History.Log_Date
FROM Parts_History
GROUP BY Parts_History.GroupID, Parts_History.PartNumber, Parts_History.PartDescription, Parts_History.Date_Out, Parts_History.Date_In, Parts_History.History, Parts_History.Log_Date
ORDER BY Parts_History.GroupID , Parts_History.PartNumber, Parts_History.Date_Out DESC ,Parts_History.Date_In DESC

 

[vbaInet]

You have to Group By the other fields too.

You could try using First for the other fields.