allowing only 3 records to be related to another table

[DawnTreader]

hello

i have a very specific need to have only 3 records in my "block serial"
table to be related to one record in my "product serial" table.

the company i work for makes a product where the compressor products that we
manufacture have upto 3 blocks in them. there is information that we relate
through the blocks to the compressor, but each block has individual
information.

i want to limit how many blocks can be given the same product ID number.

tblProduct
ProductID - Primary Key - Autonumber field
ProductSerial - Number given to compressor - Manual text field

tblBlockList
ProductID - Foreign Key - Number field
BlockID - Primary Key - Autonumber field
BlockSerial - Number given to block - Manual text field.

tblProduct.ProductID 1 --> Many tblBlockList.ProductID

thats the structure of the relationship, now how do you tell it to only
allow for 3 blocks on the many side?

 

[Nikos Yannacopoulos]

Dawn,

AFAIK this is not possible through table or relationship design. The
only way to do it is to restrict data input through forms (as opposed to
entering data directly into the tables - which is the right thing to
do anyway!), and add some code behind the form(s) to check the number of
records in tblBlockList for a particular ProductID, and only allow you
to add a new record if the existing records are >= 2.
Regrettably, I cannot provide a more specific suggestion, as that would
depend on how your form(s) is/are structured, i.e. one plain form
filtered on a combo, one form with a subform, a separate form for
blocks, or what? What controls are involved on the form(s), etc.

HTH,
Nikos

 

[David Cox]

Could you do it with a separate link table

product serial
block serial
link no

and limit link no to 1,2 3 ?

 

[Nikos Yannacopoulos]

Jamie,

The question is for Access/Jet, not SQL Server!

The concept underlying in suggestion 1 could work in Access, provided
there is some mechanism to assign a Sequence value for new records
(easily done in several different ways if data entered by means of forms).

Suggestion 2 is simply impossible in Access, as no such thing as
table-level check exists in Jet.

Regards,
Nikos

 

[Nikos Yannacopoulos]

Nikos, Haven't we been here before, my friend <g>? It usually goes
like this: I post a few links to articles about CHECK constraints
being introduced into Jet, I draw your attention that this event
occurred three versions of Access and at least SEVEN YEARS ago, then
you post, "%$£!, your right!" in reply ;-)

Jamie,

Yes, we are old acquaintances, but not that old! Seven years ago I did
not know MSNewsGroups existed, and the quote above is not my style (plus
my grammar is better than "your right"...). You must be confusing me
with someone else.

Regards,
Nikos

 

[Nikos Yannacopoulos]

Thanks for the links, I'll check them out.

Nikos, Haven't we been here before, my friend <g>? It usually goes
like this: I post a few links to articles about CHECK constraints
being introduced into Jet, I draw your attention that this event
occurred three versions of Access and at least SEVEN YEARS ago, then
you post, "%$£!, your right!" in reply ;-)

ACC2000: How to Create a Jet CHECK Constraint
http://support.microsoft.com/kb/201888/EN-US/

Description of the new features that are included in Microsoft Jet 4.0
http://support.microsoft.com/default.aspx?scid=kb;en-us;275561
"One new feature added to the Jet CREATE TABLE syntax is Check
Constraints. This new SQL grammar allows the user to specify business
rules that can span more than one table..."

Jamie.

 

[Nikos Yannacopoulos]

Hi again Jamie,

I've been checking out the links below, and trying to add a constraint
as described (Access 2003, so Jet 4.0), with no success... I execute
this successfully:

CREATE TABLE Table1 (Field1 DOUBLE);

Then I keep getting a "Syntax error in CONSTRAINT clause" error message,
when I try to execute this:

ALTER TABLE Table1 ADD CONSTRAINT (Field1 >= 0);

Am I doing something wrong? Have you actually succeeded in doing this on
a native Jet table?

Thanks,
Nikos

 

[Nikos Yannacopoulos]

Jamie,

Thanks again, but still no luck... neither method worked, I still get
the same error message.

Regards,
Nikos

 

[Nikos Yannacopoulos]

Hi Jamie,

Thanks again, this did it (both ways)!

A couple of points on this process, for anyone following this thread:

The advantage of creating constraints programatically, is the ability to
extend those over several tables.
On the flip side, there are some disadvantages vs. using validation
rules on fields in table design, when there is no need to involve any
other table:
1) Constraints are not as obvious to see; one has to query system table
MSysObjects, filtering on Type = 9;
2) Constraints can only be changed / deleted programatically;
3) Validation rules added to an already populated field will perform an
integrity check and raise a red flag in case of violations; creation of
a constraint will not, it will simply ignore violations in existing data.
Therefore, I'll stick with validation rules for single-table criteria,
and consider constraints for multi-table ones only.

Regards,
Nikos

PS. The example in MS KB article 275561 is wrong!

<quote>
ALTER TABLE TableName2 ADD CONSTRAINT (FieldName4 <= (SELECT SUM
(FieldName) FROM TableName1));
<quote>

No name for the constraint in the expression.

 

[DawnTreader]

Wow.

and without me being here at all, other than to start the posts.

i kinda figured that there wasnt an easy way to do it. i have yet to try to
implement any of the ideas here. will let you all know what happens.

thanks!