[AccessD] Compound Unique Keys...Again

[Gustav Brock]

Hi Charlotte

Oh, I missed that you hadn't dupes in the text file itself.
Then you may have to create an outer join to the existing data and rule matches out.

Strange though. I can't recall having seen issues with unique compound indexes.

/gustav

________________________________________
Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af Charlotte Foust <charlotte.foust at gmail.com>
Sendt: 2. april 2015 16:52
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Compound Unique Keys...Again

​I'm not sure what you mean, Gustav.  I've certainly tried append queries
but with the same uneven results. The problem seems to be on the Access
table end in the accdb.  It isn't keeping duplicates out even though the
unique key is set and none of the fields is null and all are required.  If
someone slips and re-imports the same data file, we wind up with duplicate
records when the unique compound key should have kept them out.​  The text
file is one big flat file of about 75 fields.  It's getting broken into
chunks on the import but there has to be a unique transaction  (and ID) in
the primary table to keep the data straight.  Filtering a query on about
six fields to avoid duplicates is asking for "query too complex" responses,
if it runs at all.

I've used this technique forever in Access to keep duplicates out, and
suddenly it isn't working.  My hair can't get any grayer, but I'm pulling
it out in handfuls over this!

Charlotte Foust
(916) 206-4336

On Thu, Apr 2, 2015 at 7:39 AM, Gustav Brock <gustav at cactus.dk> wrote:

> Hi Charlotte
>
> Haven't seen this, but couldn't you link the textfile, then run a grouping
> append query using the linked file as source?
>
> /gustav
>
> ________________________________________
> Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af Charlotte
> Foust <charlotte.foust at gmail.com>
> Sendt: 2. april 2015 16:33
> Til: Access Developers discussion and problem solving
> Emne: [AccessD] Compound Unique Keys...Again
>
> I'm beating my head against the wall on this.  I set a unique key on
> multiple fields and Access 2010 allows me to import the same data
> repeatedly!  Since the import is from a text file which has no unique
> values in it, I'm finding it impossible to keep duplicates out.
>
> Anyone else encountered this?  I may have to set those multiple fields to
> the PK, which is a nuisance.
>
>