Using a Table as a Referance - Unions

[AtomicEnergy132]

Hi, I was wondering if there was any union code that i could use to use a
table as a reference. what i mean is, if i had a main table that was related
to this reference table by the primary key, and the reference table had
fields choresponding to things in the in main table. this way, any one
record in the main table would have a value in the reference table determined
by two of the fields in the main table.

i just realized how amazingly confusing i made that, so i will try to
diagram it.

MAIN TABLE:
Joe - A - 1
Sally - B - 2
Kari - A - 2

Reference Table
-- - 1 - 2
A - x - y
B - z - q


where the goal is to be able to tie x with Joe, q with Sally, etc. i think
this could be done by making each field it's own table? but i would really
like to keep all of the information in the same table (is this asking for
something more excel oriented?)

Thank you,

Dan

 

[Allen Browne]

That's not quite the way you design a relational database.

Presumably Joe, Sally etc are clients, and the reference table contains
something (such as the preferences for each client.) If so you have a
many-to-many relation between clients and preferences. You do not model this
as many fields in the reference table. Instead you create a junction table
between Clients and Preferences, with a *record* for each applicable
combination of client and preference.

So the tables will be like this:
Client table, with fields
ClientID AutoNumber
Surname Text
FirstName Text
...

Preference table:
PreferenceID AutoNumber
Preference Text (what this actually is)

ClientPreference table:
ClientID
PreferenceID

If you ever need to show a matrix with clients (down the left) and
preferences (across the top), you use a crosstab query to generate that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...