[AccessD] Compound Unique Keys...Again

[John W. Colby]

If you have a unique id and you are getting "duplicates" then the key 
isn't truly unique.  All making it a PK does is set a unique ID.

John W. Colby

On 4/2/2015 10:52 AM, Charlotte Foust wrote:
> ​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.
>>
>>
>> Charlotte
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>