If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Keeping a List of Sub Categories in Access
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 Listint 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. -- Jeff Gaines Damerham Hampshire UK If it's not broken, mess around with it until it is |
#2
|
|||
|
|||
Keeping a List of Sub Categories in Access
On Thu, 30 Oct 2008 03:31:27 -0700, "Jeff Gaines"
wrote: 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 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 Listint 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. |
#3
|
|||
|
|||
Keeping a List of Sub Categories in Access
On 30/10/2008 in message Tom
van Stiphout wrote: 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. -- Jeff Gaines Damerham Hampshire UK 640k ought to be enough for anyone. (Bill Gates, 1981) |
Thread Tools | |
Display Modes | |
|
|