allowing only 3 records to be related to another table

[Guest]

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 ?

 

[Jamie Collins]

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?

Suggestion 1: Add an arbitrary 'sequence' column to the table, give it
a BETWEEN 1 AND 3 column/field level validation rule, and include the
sequence column in a compound UNIQUE constraint with ProductID e.g.

CREATE TABLE tblBlockList (
ProductID INTEGER NOT NULL REFERENCES tblProduct (ProductID),
BlockID INTEGER IDENTITY PRIMARY KEY,
Sequence INTEGER NOT NULL,
CHECK (Sequence BETWEEN 1 AND 3),
UNIQUE (ProductID, Sequence),
BlockSerial VARCHAR(255) NOT NULL
);

Suggestion 2: Use a table-level CHECK constraint that ensures the row
count for each ProductID is no more than three e.g.

CREATE TABLE tblBlockList (
ProductID INTEGER NOT NULL REFERENCES tblProduct (ProductID),
BlockID INTEGER IDENTITY PRIMARY KEY,
BlockSerial VARCHAR(255) NOT NULL,
CHECK (NOT EXISTS (
SELECT T2.ProductID
FROM tblBlockList AS T2
GROUP BY T2.ProductID
HAVING COUNT(*) > 3)
)
);

Jamie.

--

 

[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

 

[Jamie Collins]

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

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.

--

 

[Jamie Collins]

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).

The suggestion is not limited to 'data entered by means of forms'. The
sequence can be generated using the tables themselves i.e. counting
the number of existing rows matching the ProductID.

Arguably the neatest way of encapsulating the process is in a stored
procedure (though I understand SQL procs are at odds with bound Forms
and all that jazz):

CREATE PROCEDURE AddBlock (
arg_ProductID INTEGER,
arg_BlockSerial NVARCHAR(255)
)
AS
INSERT INTO tblBlockList (ProductID, Sequence, BlockSerial)
SELECT P1.ProductID, (
SELECT COUNT(*) + 1
FROM tblBlockList AS B2
WHERE B2.ProductID = arg_ProductID
) AS Sequence,
arg_BlockSerial AS BlockSerial
FROM tblProduct AS P1
WHERE P1.ProductID = arg_ProductID;

We can miss out some steps if this time I jump straight to the point
where I post a couple of links to articles about PROCEDURE being added
to Jet:

Description of the new features that are included in Microsoft Jet 4.0
http://support.microsoft.com/default.aspx?scid=kb;en-us;275561
"Support for the definition of Procedures has been added to Microsoft
Jet."

CREATE PROCEDURE Statement (Access 2003 Help)
http://office.microsoft.com/en-gb/access/HP010322191033.aspx

Jamie Hates Jazz

--

 

[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.

 

[Jamie Collins]

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.

Your [sic] right: my bad grammar, my bad phrasing. I meant to say that
CHECK constraints were introduced into Jet at least seven years ago.
Thanks.

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

 

[Jamie Collins]

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?

To execute your SQL DDL from the SQL View of a Query object, you must
first put the database into 'ANSI-92 Query Mode'. See:

About ANSI SQL query mode (MDB)
http://office.microsoft.com/en-gb/access/HP030704831033.aspx
(I recommend you click 'Show All'.)

Alternatively, use an OLE DB connection, which natively uses ANSI-92
Query Mode e.g. in VBA:

CurrentProject.Connection.Execute "ALTER TABLE Table1 ADD CONSTRAINT
(Field1 >= 0);"

Jamie.

--

 

[Nikos Yannacopoulos]

Jamie,

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

Regards,
Nikos

 

[Jamie Collins]

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

Sorry, I didn't check you syntax. It should be

--either--
ALTER TABLE Table1 ADD
CONSTRAINT constraint_name_here
CHECK (Field1 >= 0);

--or--
ALTER TABLE Table1 ADD
CHECK (Field1 >= 0);

HTH,
Jamie.

--

 

[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.

 

[Jamie Collins]

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.

Here are some more for 'the flip side':

CHECK constraint usage:
http://groups.google.com/group/micr..._frm/thread/2e6ee72fd3ce3967/41271b75c7d9fe31

CHECK constraints tested on left-to-right table basis rather than at
end of SQL statement
http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc

It's not that CHECK constraints offer an 'advantage' as such; rather,
they are *required* to maintain data integrity in some designs, even
some simple and oft encountered ones. The example I usually cite is a
history table (called a valid-time state table in the SQL literature),
say a 'Payroll' table consisting of columns employee_ID,
salary_amount, start_date, end_date. The candidate keys include:

(employee_ID, start_date)
(employee_ID, end_date)
(employee_ID, start_date, end_date)

Pick one for PRIMARY KEY (SQL keyword in uppercase) and use UNIQUE for
the others.

However, none of the above prevent duplicate data e.g. they do not
prevent this:

(1, 12000, #2001-01-01 00:00:00#, #2001-12-31 23:59:59#)
(1, 14000, #2002-01-01 00:00:00#, #2002-12-31 23:59:59#)
(1, 15000, #2002-12-01 00:00:00#, #2003-12-31 23:59:59#)

The above shows two salary values for the employee as at #2002-12-15
00:00:00#. Clearly, a constraint is required to prevent overlapping
periods.

If you believe that every table should have a primary key (lowercase),
being something that prevents duplicates, they you need CHECK
constraints because this cannot be achieved with PRIMARY KEY alone.

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

Good spot!

My favourite is this:

http://office.microsoft.com/en-gb/access/HA012314411033.aspx

CREATE [TEMPORARY] TABLE? Named multiple-field NOT NULL constraint??

Jamie.

--

 

[Guest]

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!