Duplicate Values

[Guest]

I have based my "Quotations" database on the Northwind sample. I receive an
error in my subform when trying to enter the same item a second time in the
same Quotation. The Northwind database encounters the same problem.

I have a table called QuoteDetails that uses a primary key of the Products
table. This field is set to allow Duplicates. I assume this is where the
hangup is happening.

Any suggestions on allowing duplicates. If you try entering the same item
twice in the Orders form of Northwind, you'll see what I am referring to.

Thanks for your help.

 

[tina]

if you take a look at the OrderDetails table in the Northwind.mdb, you'll
see that the table has a combination primary key consisting of fields
OrderID (foreign key from Orders table) and ProductID (foreign key from
Products table). though each field by itself allows duplicate values, the
two fields must have a *unique combination* of values - of course, since
together they are the primary key of the OrderDetails table.

this makes perfect sense, because there is no logical reason to list the
exact same product twice in a single order, such as
Apples 3
Pears 2
Apples 6

instead, it makes sense to list the item only once, as
Apples 9
Pears 2

do you have a sound business need for listing the same item more than once
in a single Quotation? if so, it's possible that your Quotations table has
data subsets that should actually be in separate "child" tables linked to
the Quotations table.

hth

 

[Guest]

Tina:

Yes it is necessary to have an Item listed twice on the same quotation. The
Quotations are based on blueprints with different taggings (notations) for
the same item.

For instance:

TD-1 = K100 10 feet
TD-2 = K100 5 feet

It is necessary to price each line separately - not has a total of 15 feet.

Hope I am describing this properly.

 

[tina]

okay, then you have a business reason for listing the same item twice. i'm
guessing these quotations are concerned with construction costs based on the
blueprints you mentioned; but i don't know enough about construction to make
informed comments - so i offer the following as rhetorical questions you may
want to consider, just to be sure your tables design is appropriate:

TD-1 = K100 10 feet
TD-2 = K100 5 feet

there must be something unique about each of the above records, which
prohibits them from being combined in the quotation. is that unique quality
identified and recorded in the database? if so, is it being stored as an
attribute of the correct entity? in other words, is the unique quality
specific to the "K100" entity? or is the unique quality specific to a
particular "K100" record linked to that particular quotation?

for instance, two detail records about Apples may be necessary because they
refer to
Red Delicious Apples 3
Granny Smith Apples 6

on the other hand (and changing the example to beer), identical cases of
Coors may be priced differently depending on where they're stocked in the
store:

Coors cooler 2
Coors shelf 5

depending on the answers to the above questions, it may be appropriate to
include another field in the QuoteDetails table's primary key, to ensure
uniqueness (beer example). or it may be appropriate to further define the
Products entity, to break down subsets of a given product into separate
product records (apples example).

hth

 

[Guest]

The beer example is more appropriate to my database. Do you mean to add a
field such as TagID as another primary key to the QuoteDetails table? The
Tag would be the same as the store location in your example. If this is the
case, I will have three Primary Keys in the QuoteDetails table.

 

[tina]

note: a table can have *only one* primary key. that key may consist of one
field, or multiple fields (commonly called a combination primary key or
"combo" primary key). the OrderDetails table in the Northwind.mdb has a
combination primary key consisting of two fields - OrderID and ProductID.

to answer your question: if the TagID is a data element that, in
combination with the two fields you're already utilizing for the pk, creates
a unique record in the QuoteDetails table - then yes, it is probably
appropriate to make it part of the table's primary key.

hth

 

[Guest]

Thanks - I believe I solved it by adding TagID as a Primary key.