Some advice if possible....

[Rick B]

Can someone advise me what would be most efficient way of storing information in an Access database with multiple categories.

The reason for my question is that the 'User' will be able to add categories (Tables) such as let's say 'CD Albums' to main headings such as 'Software' at runtime and I was wondering which would be the most efficient way to do this.

i.e.

Is it better to have multiple database files for example; CD.mdb, Video.mdb, Software.mdb etc with sub-categories (tables) created under this main heading. For example.


Software.mdb
(Main Category - Database file)
|
|
Utilities --------- Games ---------- Programming
(Table) (Table) (Table)

or

Is it better to have one database file, say, Data.mdb and store many tables within this database. For Example.


Data.mdb
(Main Category - Database file)
|
|
Utilities --------- CD Albums ---------- Action(Video) --------- Etc --------- Etc
(Table) (Table) (Table) (Table) (Table)

In the second method I would have to create Boolean fields that are titled as per the main headings, Music, Software, etc so that when a table is added by the user the sub-category is created under the correct heading...if you know what I mean....

Is there any other way of doing it, I would idealy like the following.

Data.mdb
(Main Category - Database file)
|
|
Music ------------------- Software ------------------- Books
| | |
| | |
CD Albums -----CD Singles Utils ----- Games Fiction ----- Sport

If anyone understands what I'm after please can you help, as it took ages to type :)

[JohnAtWork]

First things first: Study up on Normalization techniques. It sounds like you're building the DBase from scratch; if so, make sure that you're doing it right the first time.

With that in mind, you want one database.

You should have a field in your tables that indicates the CategoryType. . . either by numerical code or actual names.

Create a table that is dedicated to your categories. If a user is adding a new category, the application will append it to this list.

Don't use booleans, as you're going to have way too many unnecessary fields.
In terms of end-users adding a new item to a category, have them select the type from a combo box (bound to your Categories Table); it will make both of your lives easier.