QUERY confused!!! SQL Help!

[ombina]

Hi ASP experts!

Please help - query confused!

I wanted to display a listing by DATE DESC but only display if FIELD IN ('cat1','cat2','cat3') exists. So here's my hopeful output:

6/16/2002 - Cat2
6/12/2002 - Cat1
6/11/2002 - Cat3
6/9/2002 - Cat1

Here's actual data

6/16/2002 - Cat2
6/16/2002 - Cat5*
6/15/2002 - Cat4*
6/12/2002 - Cat1
6/11/2002 - Cat3
6/11/2002 - Cat7*
6/9/2002 - Cat1

*Don't want to show.

What I have OUTPUT with my code is this:

6/12/2002 - Cat1
6/9/2002 - Cat1

6/16/2002 - Cat2

6/11/2002 - Cat3

It's sorting by FIELD not DATE. I have a nested QUERY LOOP like this:

SQL = "SELECT * FROM TABLE1"
cat = rs("category&quot

DO WHILE NOT rs.OEF
SQL = "SELECT * FROM TABLE2 WHERE FIELD IN ('"&cat&"') ORDER BY DATE DESC"
DO WHILE NOT rs.OEF
response.write rs("date&quot & " - " & rs("category&quot
rs.MoveNext
LOOP
rs.MoveNext
LOOP

Any ideas? I really appreciate the time.

Thank you - Ricky Ombina
ombina@yahoo.com

 

[rico14d]

You dont need to use 'IN' just try =

SQL = "SELECT * FROM TABLE2 WHERE FIELD = '"&cat&"' ORDER BY DATE DESC"

 

[rico14d]

Actually, you only need one query and RS.

select * from table2 where field in(select field from table1) order by date desc

 

[ombina]

You guys are quick - thank you!
Oops! I forgot to mentioned one thing, the tables are in separate Access DB. Would this be okay still?

Table1 (data1.mdb)
Select...
Do While...
Table2 (data2.mdb)
select * from table2 where field in(select field from table1) order by date desc
...

Thanks
Ricky

 

[rico14d]

I've never worked with more than 1 DB so i have no idea.
Sorry.
Rich.

 

[ombina]

Good enough, thanks guys!