Keeping a List of Sub Categories in Access

[Jeff Gaines]

I need to keep records in an Access 2007 database that belong to one main
category and any number (in practice between 1 and 12 usually) of sub
categories. Currently I keep the main category as an integer and the sub
categories as a string which is a csv list, e.g. 1,3,5,7,9,11.

The database is driven by a front end written in C# so converting the csv
list to a List<int> and back to a csv list is straightforward in the
program.

However, what I can't do is to create a SQL statement to pull records off
the database that are in, say, main category 2 and sub-category 7. I keep
a second table which has one record for each main category/sub category
combination with the original record number in it so I can search that by
main cat and sub cat and get the affected record numbers.

Can anybody give me any pointers to a better way of setting up the main
table so that I can query it directly for a main cat/sub cat combination?
If there were always a fixed number of sub-categories it night be easier
but this isn't an option.

Pointers to websites covering the subject would be good as well. It looks
a bit like a home-work question but the last time I had home-work was 44
years ago honest!!!

Many thanks.

 

[Tom van Stiphout]

On Thu, 30 Oct 2008 03:31:27 -0700, "Jeff Gaines"

You have a classic one-to-many situation, which must be resolved with
two tables:
MainTable:
MainTableID PK
Other MainTable Fields

ChildTable:
ChildTableID PK
MainTableID FK
Other ChildTable Fields

Now the number of children is unlimited (actually, 2^32 max) and
querying will become trivial with a join across both tables.

-Tom.
Microsoft Access MVP

 

[Jeff Gaines]

You have a classic one-to-many situation, which must be resolved with

Many thanks Tom

I think that is effectively what the second table is, although I hadn't
thought of it that way.