Allow Duplicates

Hello John,

I still could not get the results as using the fields you displayed:

INV-TABLE
InvID <Text, Primary Key>
InvCntry <Text, Primary Key>
<other fields>

INV-DETAILS TABLE
DetailID <Autonumber, Primary Key>
InvID <Text, matching INV-TABLE.InvID in size>
InvCntry <Text, matching INV-TABLE.InvCntry in size>
<other detail fields>

My understanding :
INV-TABLE
InvID----IS TEXT PRIMARY KEY
InvCntry---is part of a composite key consisting of InvID & InvCntry (set
InvCntry as unique-index-no duplicates

INV-DETAILS
DetailID --AUTONUMBER -If this is the autonumber should i have FK field as
number long integer in the INV-TABLE. ( Have tried this and it creates a one
to many from the details table. I need a one to many from the INV-TABLE to
the INV DETAILS TABLE.
InvID--set as index-allow duplicates--FK
InvCntry--set as index-allow duplicates--FK

I have tried yet not able to obtain referential integrity or the one to many
from Invoice table to the Invoice Details table. Is it imperative to have
referential Integrity for what i need?

Thank you for th support



"John W. Vinson" wrote:

> On Tue, 11 Aug 2009 10:09:01 -0700, acss <joekru98.DeleteThis@hotmail.com> wrote:
>
> >Hello John,
> >
> >I created a field called PRIME and assigned that as primary key then I
> >assigned both the InvID and InvCntry as composite keys. When clicking the
> >indexs icon it displays Prime as unique while the other two fields allows
> >duplicates. The same set up applys with the Details Table. I went into the
> >relationship window to drag and drop muliple fields to the details table and
> >this resulted in 3 relationship lines of one to many. I cannot set the
> >(enforce referential integrity on the relationship) yet in testing data entry
> >within the invoices table it accomplishes what i needed...it accepts
> >duplicate invoices as long as a different country is entered and rejects if
> >same invoice and same country is entered.
> >INV-TABLE
> >Prime----PK-text
> >InvID---Composite-text
> >InvCntry--Composite-text
> >
> >INV-DETAILS TABLE
> >Prime----PK-text
> >InvID---FK-text
> >InvCntry-FK-text
> >
> >Is this what you were referring to or at the very least a hint of progress?
>
> Again... *you are misunderstanding what a primary key is*.
>
> It's PRIMARY! It is - must be - *unique* within the table. It can occur once,
> and only once!
>
> Therefore, it's appropriate to have it as the primary key of the INV-TABLE but
> it is *WRONG* to have it as the primary key of the INV-DETAILS table. The
> INV-DETAILS table should have its *own*, separate, independent, unrelated
> primary key; it should have the PRIME field as a non-unique foreign key,
> related to the Prime field in a one-to-many relationship.
>
> What I had suggested is a multifield primary key:
>
> INV-TABLE
> InvID <Text, Primary Key>
> InvCntry <Text, Primary Key>
> <other fields>
>
> INV-DETAILS TABLE
> DetailID <Autonumber, Primary Key>
> InvID <Text, matching INV-TABLE.InvID in size>
> InvCntry <Text, matching INV-TABLE.InvCntry in size>
> <other detail fields>
>
> If you then open the Relationships window you can join InvID to InvID, and
> join InvCNtry to InvCntry; enforce relational integrity and you should get a
> one to many relationship.
>
> Creating a separate Prime field is an alternative; it could be an Autonumber
> in INV-TABLE, with a separate unique two-field index on InvID and InvCntry. In
> this approach you would have a Long Integer Prime field as a foreign key (NOT
> a primary key) in INV-DETAILS, and the InvID and InvCntry fields would not
> exist in the details table at all (since that would be redundant, and they can
> be found by linking to INV-TABLE).
>
> How are you assigning a value to the Prime field anyway?
>
> --
>
> John W. Vinson [MVP]
>

On Thu, 20 Aug 2009 10:48:01 -0700, acss <joekru98.TakeThisOut@hotmail.com> wrote:

>Hello John,
>
>I still could not get the results as using the fields you displayed:
>
>INV-TABLE
> InvID <Text, Primary Key>
> InvCntry <Text, Primary Key>
> <other fields>
>
>INV-DETAILS TABLE
> DetailID <Autonumber, Primary Key>
> InvID <Text, matching INV-TABLE.InvID in size>
> InvCntry <Text, matching INV-TABLE.InvCntry in size>
> <other detail fields>
>
>My understanding :
>INV-TABLE
>InvID----IS TEXT PRIMARY KEY
>InvCntry---is part of a composite key consisting of InvID & InvCntry (set
>InvCntry as unique-index-no duplicates

Incorrect.

InvCntry should be a unique index, no duplicates, ONLY if you want to enter
each country in the table ONLY ONCE. That is, if you have an item in Brazil,
that is the *only item in the entire table* that can be in Brazil.

My understanding - and maybe I'm mistaken - is that you want an InvID of "ABC"
in the US, an InvID of "ABC" in Brazil, and an InvID of "ABC" in Argentina;
and you may also want an InvID of "XYZ" in the US, Argentina and Kenya. Right?

If so, *neither* InvID *nor* InvCntry can be unique. There will be duplicates
in the table on both fields. What needs to be unique is the combination of the
two fields! That's what the two-field joint primary key does. Either field can
have duplicates (you can have product ABC in the US and in Argentina; you can
have products ABC and XYZ in Argentina) but the combination is unique (you
can't have ABC - United States in the table twice).

The only unique index in INV-DETAILS table should be the primary key, since
you say you want to have multiple details records for the ABC-United States
inventory item.



>
>INV-DETAILS
>DetailID --AUTONUMBER -If this is the autonumber should i have FK field as
>number long integer in the INV-TABLE.

Incorrect, and I never said that. The foreign key is in the *MANY* side table,
not the One side table.

> ( Have tried this and it creates a one
>to many from the details table. I need a one to many from the INV-TABLE to
>the INV DETAILS TABLE.
>InvID--set as index-allow duplicates--FK
>InvCntry--set as index-allow duplicates--FK


>I have tried yet not able to obtain referential integrity or the one to many
>from Invoice table to the Invoice Details table. Is it imperative to have
>referential Integrity for what i need?

Yes. And you would do it by having *two* join lines in the relationships
window: from INV-TABLE.INVID to INV-DETAILS.INVID, and from INV-TABLE.INVCNTRY
to INV-DETAILS.InvCntry. There would be no join line to DetailID.
--

John W. Vinson [MVP]