Advice for structure

[Catalina]

I have a database that was started in the mid 1980's using dBase IV.
At some point it was converted to Access and modified several times.

It contains biographies of more or less famous people.
The main table (People) contains fields such as: Dateofdeath, Age, Firstname, Lastname, etc.
The actual biography is stored in a memo field in a second table (Biographies). These tables are tied together and it works well.

Table People has a field called: Category. Using a lookup field in a form I can select a Category for every record: Business, Religion, Music, Film, etc.

The problem is that a person can belong to more than one category, what to do for example with Dean Martin who was a singer and actor.

I suppose I can create another table with a one-to-many relationship but I'm not sure how to setup an efficient way of selecting records by one or more categories without duplicates.

Another solution I thought of is to select categories in a Listbox and then transfer the values to a single text field separated by a space or comma, if that is possible. A wild card search on that text field could do the rest but may get slow with 45,000 records.

Obviously I'm open for suggestions in this matter and I'll be thankful for any help.

Catalina

 

[Pat Hartman]

To handle a many-to-many relationship, you will need three tables.
1. the people table you currently have (as the last step, you will remove the category field)
2. a category table to define all valid categories with an autonumber CategoryID as the primary key.
3. a relation table to relate multiple categories to a single person. This table contains two columns - the PersonID and the CategoryID and the primary key is compound (use cntl-click to select both columns and then press the key icon).

You will need a small subform on the main form to make it simple to enter multiple categories per person. you could use a multi-select listbox but this involves VBA and is much more difficult to work with. The mini-subform can be formatted to blend into the main form so no one even realizes it is a subform.

To convert your existing data, run an append query that selects the personID and the one categoryID in the person table and append the rows to table 3. Once you are happy with the contents of table 3, remove the category column from the person table.

You will need to change your reports to use the new tables and use subreports to show the categories.

 

[Catalina]

Not sure about subform

Thanks Pat for the reply.

I'll give it a try.
I also found a good explanation on Allen Browne's site:
http://allenbrowne.com/casu-23.html

 

[Catalina]

Avoiding duplicates

I have set up the tables as suggested by Pat.
A subform on the main form allows me to assign unlimited categories.

The subform is set as continuous and holds a single combobox with "CategoryID" as the Data Source.

It all works well, however it is possible to assign the same category multiple times. Is there a way to avoid this?

All suggestions will be greatly appreciated.

Catalina

 

[georgedwilkinson]

In your junction table, go into design mode and click on "Indexes". Create a new index with your two FKs (PersonID and CategoryID?) and select "Unique".

 

[Catalina]

Index

Thanks George, I'll give it a try.

Catalina

 

[Pat Hartman]

I did point that out as #3

 

[Catalina]

Index

Yes Pat, I overlooked that.

 

[Catalina]

No duplicates

I made the compound key as Pat and George suggested.

Now I like to get rid of the Access error message (3022) "The changes you requested to the table....." and replace it with my own message and subsequently delete the record that caused the problem.

I tried error trapping in the AfterUpdate of the field, but can't get it to work.
Suggestions anyone?

Thanks,
Catalina

 

[georgedwilkinson]

Why not use the before update event? Then you can do something about it (cancel) before the DB says "no way".

 

[Catalina]

No duplicates

George, your solution seems obvious now but I didn't see it before.

In the AfterUpdate event I put:

MsgBox "Category already assigned!", vbExclamation, ""
Me.Undo
Exit Sub

And it works like a charm.

Thanks,
Catalina