Allow Duplicates

[Graham Mandeno]

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

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

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 (View>Indexes) 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

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?

:

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


:

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