Software

Ask something
Question Locked

Combo box or many-to-many relationship - data entry problem

0 helpful
8 replies

by eckardtk

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi, I am creating a contacts database using ACCESS 2003. I hoped this would be a simple enough task suitable for my level of Access knowledge. As usual, it turns out to be more complex.

Currently, I have 4 tables: Contact Details, Call Log, Event Schedule, Event Attendance.

The problem is with Contact Details table. This table has a field called Contact Type. Originally, I had planned to make it a combo box/ lookup field on the data entry form where I would enter a short list of choices into the Wizard.

Now, it turns out that 1 person (contact) can belong to several contact types. For instance, the person (contact) can be board member, a donor and an expert (in how to assist victims of domestic violence).

As far as I know - ACCESS 2003 - Combo boxes let you select only one value and, you do not want more than one value in a single cell of the table anyway.

The only solution I can think of is an extra table Contact Type plus a Junction Table. But how would I make that work within the confines of a basic data entry form ?? Currently, there is one text box called Contact Type.

Any suggestions ?

8 replies

by scott_heath

You will most likely need more table to hold things like contact types.

Table1:
contact_id, name, address, phone, etc
1, Bob, 123 Main St, 555-4567891
2, Sam, 789 Timbuktu way, 555-3216549

Table2:
Contact_ID, Contact_type
1, board member
1, donor
2, expert
2, donor

Then you use a join to populate the list box.

Regards,
Scott

http://scottisageek.spaces.live.com

by boxfiddler Hot topic

from the junction table in your form. Make the text box called Contact Type dependent on the appropriate field in the junction table?

Forms can be dependent on multiple tables.

by eckardtk

Thanks for the help. Seems I am on the right track. But, I still have a few questions:

1. I assume the List Box will have the join table as data source ?

2. How would the data entry work ? Would the user just go back to the list box 2 or 3 times and make different selections? I hope each new selection will not overwrite the previous entry??

by scott_heath

I just fiddled with it a bit and it worked OK. Yuo'l lhave to write some VBA behind the scenes to handle populating the list box, but I used a textbox and an add button to add items to the listbox, then when you save the record you parse the data to the correct table. Same goes for reading it in. There maybe be a cleaner way, but I have not researched it.

Regards,
Scott

http://scottisageek.spaces.live.com

by eckardtk

Hi Scott,
thanks for your help. Unfortunately, VBA code is out of my league. Can you forsee any major problems if I simply have 2 fields:

contact type 1 and contact type 2

in my main table. I know, one is not supposed to have such repeating fields.

Before I started this question, I had searched quite a bit and all I could find were highly sophisticated procedures involving subforms combined with convoluted routines and sub-routines that I did not even try to comprehend.

In essence, what I have is a single data item that can have 2 (at the most 3) possible values. 50% of the time it will be just 1 value.

by scott_heath

In databases unlikely to exceed a few thousand records normalization is not that important. Just make 3 seperate fields so you don't have to mess around with coding. If I had some extra time I'd figure it out and code it for you, but then you'd be left trying to support it, which isn't a great idea.

Regards,
Scott

http://scottisageek.spaces.live.com

by eckardtk

Hi Scott,
your reply brings a great relief to me. I took this on as a volunteer assignment because it sounded like a basic contact management datbase. Nobody expects a polished, professional product from me. Thanks again for your help.

This discussion is closed to new replies.