[AccessD] Compound Unique Keys...Again

Paul Hartland paul.hartland at googlemail.com
Thu Apr 2 21:33:01 CDT 2015


Charlotte,

So you have a table which has a unique key on one or more fields ?...If so
one of these isn't an autonumber is it ? (done this myself in the
past...banged head against wall many time for school boy errors like this)

Paul

On 2 April 2015 at 18:24, Charlotte Foust <charlotte.foust at gmail.com> wrote:

> I know that John.   The autonumbers is used strictly  for joins.   Today ,
> on my machine it's working perfectly and as I world expect. Yesterday, on
> their machine it didn't.  That pounding you may hear is my head meeting the
> wall.
> On Apr 2, 2015 8:43 AM, "John W. Colby" <jwcolby at gmail.com> wrote:
>
> > 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
> >>>
> >>>
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Paul Hartland
paul.hartland at googlemail.com


More information about the AccessD mailing list