Sort order from Access

[Elena]

Hi,

I have an SQL statement:

SELECT DISTINCT Tests.ConfigName, Tests.[C-SEL] FROM Tests WHERE Tests.OSID = 1 AND Tests.DriveType = 1

Now, this is working fine, but the order that the records are returned in is not correct. I want it to return the records in the order they were entered into the database (thought that was the default), but they are being returned alphabetically. The field Tests.ConfigName is not indexed. The only field in the Tests table that is indexed is the LogID field, which is autonumbered.

So, I changed the SQL statement to read

SELECT DISTINCT Tests.ConfigName, Tests.[C-SEL] FROM Tests WHERE Tests.OSID = 1 AND Tests.DriveType = 1 ORDER BY Tests.LogID

This is not working either. It is giving me the error:

Run-time error '-2147467259(80004005)':
ORDER BY clause (Tests.LogID) conflicts with DISTINCT

So, my question is, how can I order them correctly?

Thanks,
Elena

 

[AndyWatt]

Change the distinct query into a make table query and then select from the temp table using the sort order clause:

SELECT DISTINCT ConfigName, [C-SEL] INTO TMP_TABLE FROM Tests WHERE OSID = 1 AND DriveType = 1

SELECT * FROM TMP_TABLE ORDER BY LogID

DROP TABLE TMP_TABLE

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"A computer program does what you tell it to do, not what you want it to do." -- Greer's Third Law

 

[Elena]

Andy,

How would I do that from VB?

How would the TMP_Table know about the LogID, since I didn't select it in the first place?

Also, if I select the LogID first, of course it returns all the records because every one has a different ID. Then, I'm back to square one.

Thanks,

Elena

 

[AndyWatt]

Sorry, Elena, I wasn't thinking straight!

You can get a list of the lowest LogID associated with a each ConfigName by

SELECT MIN(LogID) AS Log_ID, ConfigName INTO TMP_TABLE FROM Tests WHERE ConfigName IN (SELECT DISTINCT ConfigName FROM Tests)

Then you'd need to do your select for the other fields and join with the temp table:

SELECT t.ConfigName, t.[C-SEL] FROM Tests t INNER JOIN TMP_TABLE m ON t.LogID = m.Log_ID WHERE t.OSID = 1 AND t.DriveType = 1 ORDER BY m.Log_ID

And that will (I hope) do what you want.

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"A computer program does what you tell it to do, not what you want it to do." -- Greer's Third Law

 

[Elena]

Andy,

Thanks for the help. I really appreciate it. I ended up playing around in Access until I got it the way I wanted it and then I copied the SQL statement into VB. Here's what it looked like, in case you're interested.

SELECT First(Tests.LogID) AS FirstOfLogID, Tests.ConfigName, Tests.[C-SEL]
FROM Tests
WHERE (((Tests.OSID)=2) AND ((Tests.DeviceTypeID)=2))
GROUP BY Tests.ConfigName, Tests.[C-SEL]
ORDER BY First(Tests.LogID)

Thanks again,
Elena

 

[AndyWatt]

I'm glad to learn you worked it out. It's so much more satisfying that way!

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"A computer program does what you tell it to do, not what you want it to do." -- Greer's Third Law