Constrain a value in a child table based on parent table

[Guest]

I have a parent table, which represents a list of businesses. Then a child
table which represents instances of a business participating in a program.
The child table has a program type field that draws from a lookup.

What I would like to do, is structure the database to constrain the child
table to only accept one instance of a program type per business. So in
other words, Business A can participate in many programs, but only once in
each program. Any thoughts on how to do that?

 

[Tim Ferguson]

What I would like to do, is structure the database to constrain the
child table to only accept one instance of a program type per
business. So in other words, Business A can participate in many
programs, but only once in each program. Any thoughts on how to do
that?

You need more tables sic:
(* means Primary Key, + means Foreign Key)

Businesses(*CompanyCode, Owner, FullName, etc )

Programmes(*ProgID, Description, StartYear, EndYear, etc)

Participations(*CompanyCode+, *ProgID+)


Notice that the participations table is all-key; i.e. that the Primary Key
is made up of both fields. This means that any combination of (CompanyCode,
ProgID) can only exist once in the table.

Hope that helps


Tim F

 

[John Vinson]

I have a parent table, which represents a list of businesses. Then a child
table which represents instances of a business participating in a program.
The child table has a program type field that draws from a lookup.

What I would like to do, is structure the database to constrain the child
table to only accept one instance of a program type per business. So in
other words, Business A can participate in many programs, but only once in
each program. Any thoughts on how to do that?

Create a unique Index on the two fields in question. To do so, open
the table in design view and select the Indexes tool (looks like
lightning hitting a datasheet). Put a name for the index in the left
column (iBusinessProgram say); add the business ID and program ID
fields in that and the next row of the right column; and specify that
the index is unique.

Note that if you have a Lookup Field you need to use the (hidden) ID,
not the looked-up field. See
http://www.mvps.org/access/lookupfields.htm for a critique of what
many consider a misfeature.

John W. Vinson[MVP]

 

[Guest]

John - I'm not quite following you. I understand the whole index thing, but
what I'm not understanding is how to assign two fields to one index. I tried
it the way you wrote, but it didn't constrain the addition of an additional
instance of the same program type?

 

[Douglas J. Steele]

An index can contain up to 10 separate fields. Did you perhaps put an index
name beside both field names?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[John Vinson]

John - I'm not quite following you. I understand the whole index thing, but
what I'm not understanding is how to assign two fields to one index. I tried
it the way you wrote, but it didn't constrain the addition of an additional
instance of the same program type?

An Index can consist of up to TEN fields. What you would see in the
Indexes window would be something like

iBusinessProgram BusinessID
ProgramID
iStartDate StartDate
iOtherField OtherField

with a blank box next to the second (or third, or...) field in the
index.

John W. Vinson[MVP]