How to sort by using ORDER using a field as the sort category in other table within the same database file?

Using a VB6.0 DAO Database in my software, green on Databases & SQL so my question might be a simple one:

I'm looking for a SQL query solution to get a RecordSet in a sorted result where the sort category sits in a different table in the same Database file

Database structured as below:

DBase picture

This works fine:


SELECT * FROM BASETable,DescriptionTable ORDER BY DescriptionTable.Description ASC

comes up with 7 times repeated data in the RS, unsorted

Any ideas to solve it into a single list, sorted by the sort category are most welcome,

Thanks for your help.


you have to join the two tables ...
do the order by


create a temp table with column order by you want
join the temp table to main table
do the order by

hope this helps

1 Like

Thanks harishgg1,

joined the two tables and added the WHERE function which solved it:

SELECT * FROM BASETable,DescriptionTable
WHERE BASETable.D_ID=DescriptionTable.Id
ORDER BY DescriptionTable.Description ASC


joins are done like this in SQL

select * from tableA join tableB on tableA.columnz = tableB.columnz

1 Like


tried the JOIN word instead of a comma but resulted in an error in the SQL line where the comma solved it fine.

Must be the ancient VB6.0 DAO environment I'm using as a lot of solutions here are causing errors in my program. Not exchanging the old Access 95 database I use anyway, just using it to import ASCII text data, sort and manipulate and export it back to a ASCII text file as the Land Survey controllers I use are ASCII text based.

thanks for your info.


:slight_smile: :slight_smile:

SQL Server Microsoft ( thats what i am using )

there are so many variations and limitations
from one thing to other

It can drive anyone crazy
-- or at least me

-- some dont work in other applications
-- same thing is done differently
-- cannot do becuase no functionality
-- same thng to do is 100 times more code

if you are learning for the first time ..
then its even more painful