Multiple Titles

[bbilotta]

I am rebuilding a db that is used for dog trials. The new version will have (among others) a Dog table.
One of the attributes of a dog is its Titles. Some dogs have none, some may have one, some may have many.
I have a Titles table.
My question has become - what is the best way to tie the two together.
I can use a modified look up field in the dog table to store multiple entries which the provides a drop down list on my "New Dog" form and the user selects the appropriate titles for the dog.
Or i could build an intermediary (many-to-many) table to connect dogs to multiple titles.
obviously the way i get the input field onto the form will differ and here's where i think i have a concern. using the modified field from the table directly on the form provides the drop down for the user. if i use a M-M approach i'm guessing i'd need to create a sub-form that allows the user to select the appropriate titles??

As an aside, one of the minor issues i'm having with using the modified field directly on the form is that i can't seem to get "On Click" macros to work. i wanted to show a message window instructing the user to "Please select ALL appropriate titles for the dog". Any help or suggestions on that front would be appreciated also.



I will ultimately build a query that provides the titles appropriately placed in front of the dog's name using trim and concatenation for use in reports.

The list of titles is finite (12), and the order of the titles as they appear ahead of the name is important.

Update:
After fooling around with the MultiValue, modified lookup i've discovered it is apparently very difficult to use this "value" in a concatenated string. Although the multiple values appear as a string under stand-alone conditions, when you try to concatenate that MultiValue to another field it separates the individual values into rows. So if i had one dog with three titles and it try to concatenate the titles to the name in a query, i end up with three rows for that one dog.

Example:

Dog name - Rover
Titles - FC AFC CFC
After a simple concatenation (TitlesTbl.Title +" "& DogsTbl.Name) the desired Result is: FC AFC CFC Rover
And when you view this MultiValue "field" on its own in a report or Query you see FC, AFC, CFC

However, when i try to concatenate that MultiValue field with the Name field the Actual Result is:
FC Rover
AFC Rover
CFC Rover

I've been searching and all I'm finding is some extensive VBA to get it to work. I'm definitely not ready for that much code. May have to default to M-M table format. Will wait to see what all the smart people here have to share.

[ranman256]

You'd have 2 tables, the main tDog table where each dog gets an ID.
tDogs
---------
DogID (auto)
Name
etc...

tDogTitles
--------
DogID (long)
Title

the child table tDogTitles will hold many alternate title the 1 dog may have.
use a parent/child form to enter all the titles 1 dog can have.

[bbilotta]

Thanks Ranman,
I think you're confirming what i said about the need for a subform if i want to use two tables.