March 29, 2020, 10:34am
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:
This works fine:
SELECT * FROM BASETable ORDER BY PointNumber ASC
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
March 29, 2020, 1:42pm
joined the two tables and added the WHERE function which solved it:
SELECT * FROM BASETable,DescriptionTable
ORDER BY DescriptionTable.Description ASC
joins are done like this in SQL
select * from tableA join tableB on tableA.columnz = tableB.columnz
March 29, 2020, 2:12pm
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.
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