Query a recordset possibly! 1

[Sarky78]

I have a database that I have inherited. This database is used to filter organisations based on various answers that the user gives. When the final stage is reached the main query is run. This query returns duplicate organisations, which I removed in the past using DISTINCT in the select statement.

Now the client wants to be able to order the organisation output based on a sort order set in their administration system.

The problem that i am having is when I add the order by clause SQL throws a mental, and says that I can't use DISTINCT and the order by statement. but without the distinct I get duplicate organisations, which isn't acceptable.

Is there anyway that I can use the recordset from one query as the input to another one in ASP? kinda like this:

query1 = "SELECT this and that from there order by sortorder"

query2 = "SELECT DISTINCT(THIS) from Query1"

is this sort of thing possible?

I am using MS Access (unfortunatly) and the main query(query1 in the case above) is built up dynamically based on what the user has selected, over the various pages.

any help appreciated, have been banging my head against the desk for a few hours on this one!

Cheers

Tony

 

[BG12424]

How about using a GROUP BY within your select statement.

select field1, field2
from table1
group by field1, field2
order by field1 asc _______________________________
regards,
Brian

AOL IM: FreelanceGaines

 

[ufobaby]

Tony,

i don't see any reason why u can't use "Distinct" and "Order By" clause together ! .... are u sure u selecting the field , on which u order by

as for querying a recordset ... it's not possible .. what u'll need to do is to write the "query1" as a QuerDef to the database and then open a recordset as "Select ... from query1" ...
remember u'll have to delete the querydef before u re-write to the same.

hope this helps ....


Niraj

 

[nadskram]

you can sort the recodset using the sort method

sometimes you may have to use the filter method.

i often use a global rs, filter, set the bookmark, recurse through records and the reset the filter and bookmark

 

[Sarky78]

thanks guys!!

At the moment i think that my problem is that I am being thick (shocker!!!), and not selecting the dam field to filter against! doh!

will let you know how i get on with it

cheers again!

Tony