[thelist] More ASP/database questions...

[Beau Hartshorne]

So I've got an ASP page hooked up to an Access database. The Access database
has several tables in it that are related to each other. I would like to
create a hierarchal list where a category is pulled from one table, and a
sub-category is pulled from another, like so:

Category 1  <-- Table A
 Sub Cat 1  <-- Table B
 Sub Cat 1  <-- Table B
 Sub Cat 1  <-- Table B
 ...

Category 2  <-- Table A
 Sub Cat 2  <-- Table B
 Sub Cat 2  <-- Table B
 Sub Cat 2  <-- Table B
 ...

There are about two dozen major categories, and a couple hundred sub
categories. I've come up with two solutions to this problem:

Solution 1: Insert all the data from Table A into a recordset object
(Recordset A), and all the data from Table B into another recordset object
(Recordset B). A while loop goes through Recordset A, grabs the name and the
primary key from that recordset. It then prints the name to the page, and
use the primary key from Recordset A to filter the records from Recordset B
(showing only Recordset B's foreign key matches). Once the filter is applied
to Recordset B, I run another loop (within the Recordset A loop) to print
all of Recordset B's filtered records.

Solution 2: Run an SQL query on Table A to find all the Category names,
store this in a recordset. Loop through the recordset and run another SQL
query on Table B for each category to find all of that categorie's sub
categories. Print the results on the page.

I like solution 1, because as far as I understand it only makes two calls to
the database. I am having a hard time implementing solution 1, honestly
because I don't have much experience with asp, let alone recordsets.
Solution 2 would work, and it would be easy to implement, but it just seems
dirty.

I'm hoping that someone out there either has another solution for this type
of problem, or could at least let me know that I'm on the right track with
one of mine.

Thanks,

Beau