Compound key/composite key

[pudgie]

Hi there Guys!

I'me having a problem setting up a db at the moment, it's an
MS ACCESS problem.

In table A, I need to combine 2 attributes (int and char)
as a key to be used as a foreign key in table b.

Any help on this?

I am working without any test data at the moment.

 

[930driver]

Use an artificial primary key in the form of an autonumber and link on that single unique field to any foreign keys. The foreign key will be a long integer to match up with the autonumber. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[pudgie]

Can you clarify?

tbls as they are:

TABLE-a
-----------
KEY(Autonumber)
attrib_a
attrib_b


TABLE-b
-----------
KEY(Autonumber)
attrib_c
(I need to populate this with TABLE-a.attrib_a+TABLE-a.attrib_b)


I'm sorry, but can you clarify pertaining to the above?
Which will need indexing?

 

[930driver]

In TABLE-a name your key something other than KEY (for the sake of clarity) such as TableAID. Then add TableAID as a long integer to TABLE-b. That will be the foreign key to TABLE-a.

TABLE-a
-----------
TableAID (Autonumber)
attrib_a
attrib_b


TABLE-b
-----------
KEY(Autonumber)
TableAID (Long Integer)
attrib_c

Then, any place you use a subform or query, link or join on TableAID. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[pudgie]

930driver

Many, many thanks. I fear I have not done my basic groundwork here. Thanks again for your time

P

 

[WildHare]

Both key fields in your main table can/should foreign key fields in your child table.

TableA
*FieldA_Int } Composite
*FieldB_char } Key
FieldC
...

TableB
*FieldB-KEY
FieldA_AInt } Composite
FieldA_BChar } Foreign Keys
....

R/I may or may not be enforced or required for this design, it depends on your business rules.







Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm