allowing only 3 records to be related to another table

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 wrote:
On Jun 6, 2:47 pm, Nikos Yannacopoulos
wrote:
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...b;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.

--

On Jun 7, 1:13 pm, Nikos Yannacopoulos
wrote:
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/ac...704831033.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.

--

Jamie,

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

Regards,
Nikos

Jamie Collins wrote:
On Jun 7, 1:13 pm, Nikos Yannacopoulos
wrote:

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/ac...704831033.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.

--

On Jun 7, 2:12 pm, Nikos Yannacopoulos
wrote:
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.

--

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 wrote:
On Jun 7, 2:12 pm, Nikos Yannacopoulos
wrote:
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.

--

On Jun 11, 10:58 am, Nikos Yannacopoulos
wrote:
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/micro...271b75c7d9fe31

CHECK constraints tested on left-to-right table basis rather than at
end of SQL statement
http://groups.google.com/group/micro...c3f233ba3336cc

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/ac...314411033.aspx

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

Jamie.

--

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!

"DawnTreader" wrote:

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?