Select Distinct (1 Viewer)

[WindSailor]

Hi all,

Lately I have been having problems with SQL statements using the ‘Distinct’ keyword.
The following code below in the SQLText2 statement has fictitious Table and Column names but the structure is the same.

Dim db1 As DAO.Database
Dim rst00 As DAO.Recordset

Dim db12 As DAO.Database
Dim rst01 As DAO.Recordset


Set db1 = CurrentDb
Set db12 = CurrentDb

‘OK… I first run a simple SELECT statement, Set rst00 = db1.OpenRecordset(SQLText1), find first, move next, (which works fine) and then if rst00 is End Of File then do the following…

SQLText2 = SELECT DISTINCT
FullN, Price1, Weight1, Palete1
FROM OrderDetails
INNER JOIN OrderPalettes
ON
OrderDetails . Palette1 = OrderPalettes .PaletteCoreLookUPNumber
AND
OrderDetails . Price1 = OrderPalettes .PriceCoreLookUPNumber
ORDER BY Price1,Weight1 ASC

Set rst01 = db12.OpenRecordset(SQLText2)

When I use the SQL scratchpad from the Access 2002 Developers Handbook to test my SQL statements inside my database, the above SQL statement works fine; but when I use it inside my project on a form, it doesn’t return any records or it doesn’t return the values correctly. I simply have tried so many different variations using the DISTINCT keyword that I have forgotten the original error with the above statement. It is just that I couldn’t get it to work correctly and simply went back and trapped for duplicates without using the DISTINCT keyword.

The only thing I can think of currently is that my structure is wrong, or Access 2003 doesn’t really like the DISTINCT keyword.

Any tips are greatly appreciated.

 

[FoFa]

Instead of select distinct have you tried select (no distinct) and order by FullN, Price1, Weight1, Palete1

 

[WindSailor]

Thanks FoFa,

Thats exactly what I had to do and then trap for duplicates.
And so far I haven't had any hiccups... but I am thinking of changing my If... Then... statements to Case when nesting several layers. Or at least switching back and forth so it is easier to read.

It just seemed real picky about using Distinct with DAO and I just didn't have the time to iron it out.

Much appreciated.

 

[WindSailor]

Well... confirmed.
Removed the 'Distinct' keyword from nested DAO recordsets and my clients mde file using the runtime is working correctly.

Thanks again.

 

[WindSailor]

Well...
After having some spare time and doing some more testing, I found that I wasn't really compliant with my SQL statements using 'Distinct' after-all. Usually when Access gets 'tweaky' I am trying to do something illegal.

I simply didn't have the SQL statements in my recordsets the way Access natively uses them.
Even though I used the 'SQL scratchpad' - absolutely a great tool - I don't think I was explicit enough in my statement and Access just got confused.
I had to go back and create a test query in a new test database to pull the records I wanted and work in SQL view of the query until I got the results I wanted, inserting the 'DISTINCT' keywords etc.

If you are creating recordset SQL statements, no matter how simple or complicated you get, I would definitely create a test query and work with it until you get the results you want. It will save you a lot of time later.
And so far I haven't had a problem with the runtime.
Lesson #120970147547