allowing only 3 records to be related to another table

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?

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

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?

Could you do it with a separate link table

product serial
block serial
link no

and limit link no to 1,2 3 ?


"DawnTreader" wrote in message
...
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?

On Jun 6, 12:28 am, DawnTreader
wrote:
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.

--

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 wrote:
On Jun 6, 12:28 am, DawnTreader

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.

--

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 6, 2:47 pm, Nikos Yannacopoulos
wrote:
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

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...b;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/ac...322191033.aspx

Jamie Hates Jazz

--

Jamie Collins wrote:


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

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

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 6, 3:44 pm, Nikos Yannacopoulos
wrote:
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.

--