Allow Duplicates

Hello John,
I tried using your recommendation yet in trying the join in the relationship
window i continue to get inderterminate error. I set up the two field
composite key using the fields InvID, InvCtry by holding the shift key and
highlighting both fields. The same was done for details table yet no sucess.
Any further direction is greatly appreciated.




"John W. Vinson" wrote:

On Tue, 4 Aug 2009 18:34:01 -0700, acss wrote:

Apologies for the confusion Graham,

I would like for the primary key to be unique in the invoice table and as a
foreign key in the details Table however i have the challenge that duplicate
invoices need to be entered and should be accepted only if it is from a
different country.

InvTable
InvID---PK
InvCtry
Invdate
etc

InvDetails
InvID-FK
ProductID--FK---COMPOSITE KEY consisting of InvID and ProductID
Price
Qty

ProductTable
ProductID-----PK-Autonumber
ProductName
Category
Etc

I am stuck since i need a one to many relationship between Inv and
details(one invoice can have many details).The problem occurs as the same
invoice number needs to be entered 3 times once for china another for ecuador
and another for brazil..that is why i need acess to accept same invoice
number only if by different countries. Can you suggest some direction please?

It sounds like you need a composite, two-field primary key (InvoiceNo and
CountryCode), which will also require a composite, two-field foreign key in
the details table. You can use

InvCtry;InvID

as the Master Link Field and Child Link Field property of your subform.

You will not be able to use an Autonumber for InvID, but then it's
inappropriate to use an autonumber for that purpose in any case.
--

John W. Vinson [MVP]

On Sun, 9 Aug 2009 22:29:01 -0700, acss wrote:

Hello John,
I tried using your recommendation yet in trying the join in the relationship
window i continue to get inderterminate error. I set up the two field
composite key using the fields InvID, InvCtry by holding the shift key and
highlighting both fields. The same was done for details table yet no sucess.
Any further direction is greatly appreciated.

If you look at the parent table in design view are there key icons on InvID
and on InvCtry?

When you're creating the join, are you dragging InvID to InvID, InvCtry to
InvCtry, so that there are two lines, prior to setting the relationship's
properties?

--

John W. Vinson [MVP]

Yes. In the Invoice Table and Details table there are two key icons. Not sure
as your description of two lines since trying to drag more than one field
onto another table will bring an error of there can only be one relationship.
The previous autonumber as primary was working in that it created a new
record yet it did not allow for a control to be in effect. That control is to
allow duplicates of the invoice number entered as long as it is from a
different country. If from same country then invoice number cannot be
entered. Apologies for causing you this grief...any other suggestions for me
to follow...really appreciate your support.

"John W. Vinson" wrote:

On Sun, 9 Aug 2009 22:29:01 -0700, acss wrote:

Hello John,
I tried using your recommendation yet in trying the join in the relationship
window i continue to get inderterminate error. I set up the two field
composite key using the fields InvID, InvCtry by holding the shift key and
highlighting both fields. The same was done for details table yet no sucess.
Any further direction is greatly appreciated.

If you look at the parent table in design view are there key icons on InvID
and on InvCtry?

When you're creating the join, are you dragging InvID to InvID, InvCtry to
InvCtry, so that there are two lines, prior to setting the relationship's
properties?

--

John W. Vinson [MVP]

On Mon, 10 Aug 2009 19:11:26 -0700, acss wrote:

Yes. In the Invoice Table and Details table there are two key icons.

Then you will - MUST!!! - have a *one to one* relationship.

The two fields should be the Primary Key of the Invoice table.

Since the Invoice Table is related one-to-many to the Details table, the
Details table must NOT have these two fields as its primary key. They must be
foreign keys, non-unique.

Not sure
as your description of two lines since trying to drag more than one field
onto another table will bring an error of there can only be one relationship.

Incorrect.

There is only one relationship... but it can consist of up to TEN fields in
each table, which would be displayed onscree as TEN lines.

Did you try it?

The previous autonumber as primary was working in that it created a new
record yet it did not allow for a control to be in effect. That control is to
allow duplicates of the invoice number entered as long as it is from a
different country. If from same country then invoice number cannot be
entered. Apologies for causing you this grief...any other suggestions for me
to follow...really appreciate your support.

Try what I suggested, for starters. It will work.
--

John W. Vinson [MVP]

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?
"John W. Vinson" wrote:

On Mon, 10 Aug 2009 19:11:26 -0700, acss wrote:

Yes. In the Invoice Table and Details table there are two key icons.

Then you will - MUST!!! - have a *one to one* relationship.

The two fields should be the Primary Key of the Invoice table.

Since the Invoice Table is related one-to-many to the Details table, the
Details table must NOT have these two fields as its primary key. They must be
foreign keys, non-unique.

Not sure
as your description of two lines since trying to drag more than one field
onto another table will bring an error of there can only be one relationship.

Incorrect.

There is only one relationship... but it can consist of up to TEN fields in
each table, which would be displayed onscree as TEN lines.

Did you try it?

The previous autonumber as primary was working in that it created a new
record yet it did not allow for a control to be in effect. That control is to
allow duplicates of the invoice number entered as long as it is from a
different country. If from same country then invoice number cannot be
entered. Apologies for causing you this grief...any other suggestions for me
to follow...really appreciate your support.

Try what I suggested, for starters. It will work.
--

John W. Vinson [MVP]

On Tue, 11 Aug 2009 10:09:01 -0700, acss 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]

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 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]

InvCntry---is part of a composite key consisting of InvID & InvCntry (set
InvCntry as unique-index-no duplicates

No, if you make InvCntry a unique index then you will not be able to add two
invoices fron the same country!

Have you seen my posts from more than two weeks ago where I suggested using
a surrogate (autonumber) primary key and having the InvNum/InvCntry as a
secondary unique index? As I've explained before, I believe this is by far
the best approach.

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

"acss" wrote in message
...
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 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]

Hello - what would the criteria for deleting a duplicate record, e.g
PO#1, LINE 1, RECEIVED1
PO#1, LINE 1, SHIPPED1
I am looking to delete whatever is the Same PO#, Same LINE but
SHIPPED(instead of Received) I only need one record or RECEIVED.
thank you


"Graham Mandeno" wrote:

Hi Joe (?)

I'm confused. You say "The primary key is the invoice number as an
autonumber" but you also said that the invoice number was in the imported
data, so it can't be an autonumber!

I suggest you add a new autonumber field named InvoiceID and make that your
primary key. Then, make InvoiceNumber and Country both required fields and
make them a composite unique key.

To do this, open the Indexes window (ViewIndexes) and in the first blank
row, enter "InvoiceNumberCountry" as the index name and select InvoiceNumber
as the field name. Then, in the index properties below, change Unique to
"Yes". Now, in the next blank line, choose Country as the field name (but
leave index name blank).

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


"acss" wrote in message
...
Hello,
I am still working on this and i am curious as to how you create a unique
index on a combination of invoice and country field? The primary key is
the
invoice number as an autonumber so the table can not have more than one
primary key....can you eloborate for me please?

"Noëlla Gabriël" wrote:

Hi,

I always take an autonumber field as PK field. You can then create
another
unique index on the combination of the invoice number field and the
country
field
--
Kind regards
Noëlla


"acss" wrote:

i have a table for invoices and at times i receive a duplicate invoice
number
from another country. How do i set up a primary key (InvoiceID) and a
secondary key (country) that would allow a duplicate entry only if from
a
different country?

Thanks

Hi JUNEBUG

You will need a query something like this:

Delete from tblOrders as O WHERE O.Status"SHIPPED" and
Exists (Select PO from tblOrders as X where X.PO=O.PO and X.Line=O.Line
and X.Status="SHIPPED")

In plain English: Delete from the table any record whose status is not
"SHIPPED" if there is a "SHIPPED" record in the table for the same PO and
Line number.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"JUNEBUG" wrote in message
...
Hello - what would the criteria for deleting a duplicate record, e.g
PO#1, LINE 1, RECEIVED1
PO#1, LINE 1, SHIPPED1
I am looking to delete whatever is the Same PO#, Same LINE but
SHIPPED(instead of Received) I only need one record or RECEIVED.
thank you


"Graham Mandeno" wrote:

Hi Joe (?)

I'm confused. You say "The primary key is the invoice number as an
autonumber" but you also said that the invoice number was in the imported
data, so it can't be an autonumber!

I suggest you add a new autonumber field named InvoiceID and make that
your
primary key. Then, make InvoiceNumber and Country both required fields
and
make them a composite unique key.

To do this, open the Indexes window (ViewIndexes) and in the first blank
row, enter "InvoiceNumberCountry" as the index name and select
InvoiceNumber
as the field name. Then, in the index properties below, change Unique to
"Yes". Now, in the next blank line, choose Country as the field name
(but
leave index name blank).

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


"acss" wrote in message
...
Hello,
I am still working on this and i am curious as to how you create a
unique
index on a combination of invoice and country field? The primary key is
the
invoice number as an autonumber so the table can not have more than one
primary key....can you eloborate for me please?

"Noëlla Gabriël" wrote:

Hi,

I always take an autonumber field as PK field. You can then create
another
unique index on the combination of the invoice number field and the
country
field
--
Kind regards
Noëlla


"acss" wrote:

i have a table for invoices and at times i receive a duplicate
invoice
number
from another country. How do i set up a primary key (InvoiceID) and
a
secondary key (country) that would allow a duplicate entry only if
from
a
different country?

Thanks