Append query without duplicates (1 Viewer)

syntaktik

Registered User.
Local time
Today, 22:43
Joined
Oct 15, 2009
Messages
68
In my database i have three tables in a many to many relation ship. From there i have a query that pulls the person from table A and then places then in table B where it assigns the person with each event from table C. However, if i add a new person in table A, and run the query again, it adds the event to each person again. How do i prevent that?
 

stopher

AWF VIP
Local time
Today, 13:43
Joined
Feb 1, 2006
Messages
2,396
Sorry but I don't understand your question. Maybe you could explain the purpose of each table. Why do you need to copy data from one table to another? This is not normally a good thing to do.

Chris
 

syntaktik

Registered User.
Local time
Today, 22:43
Joined
Oct 15, 2009
Messages
68
ah, no problem.
I have a list of people in 'Table A'
I have a list of events in 'Table B'
those two are connected with a many to many relationship so i can list everyone's attendance to each event. (sort of like the college example in this video by Microsoft) And the exact thing this guy is doing here.
and then the query adds each person to each event.

But, when i run the append query twice, it creates duplicates when i add a new person for instance. How do i stop it from doing that? Since i don't want to go back to setting each and every person individual.
 

stopher

AWF VIP
Local time
Today, 13:43
Joined
Feb 1, 2006
Messages
2,396
One way is to create an index for your target table. In the example I assume the combination of "StudentID", "C/S ID" and "A/T ID" must not be duplicated i.e. we do not want more than one score for a given student, class/subclass, Assignment.

So in this example, create an index (which includes all three fields) and set the Unique attribute to Yes.

Now when you try to add records that already exist, the query will throw an error message saying how many records cannot be added. It will still allow you to add new records.

hth
Chris
 

syntaktik

Registered User.
Local time
Today, 22:43
Joined
Oct 15, 2009
Messages
68
interesting. But how do i set an index on all of them?
 

syntaktik

Registered User.
Local time
Today, 22:43
Joined
Oct 15, 2009
Messages
68
i found some more information that could be relevant. This is exactly what i'm trying to do here. But it only works from the form if you look at i'ts code. The append query would override that. Any ways to use some of this in a way?
 

stopher

AWF VIP
Local time
Today, 13:43
Joined
Feb 1, 2006
Messages
2,396
i found some more information that could be relevant. This is exactly what i'm trying to do here. But it only works from the form if you look at i'ts code. The append query would override that. Any ways to use some of this in a way?
I have to say I don't entirely agree with the answer given in your link. You could use the primary key but you already have a primary key that you should not change. You can create a multiple field index as explained here. There may be better explanations.

The last point is valid though. If you do this append solely through a query then you will probably want to suppress the warning messages temporarily. However, the user will be unaware that he/she has tried to append twice. If that is important to you then you will need to do some VBA as well to check and provide nice messages.

hth
Chris
 

syntaktik

Registered User.
Local time
Today, 22:43
Joined
Oct 15, 2009
Messages
68
Wow! You found the solution! Thanks, that actually worked.
 

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 06:43
Joined
Jun 12, 2018
Messages
29
I know this is an old thread, but I'm in the same boat. Unfortunately the link from You can create a multiple field index as explained here. Is no longer active. So I'm not able to follow the same advice.

My scenario is almost the same as @syntaktik but I will explain the details anyway.

I have a Tbl_SkuBySize that stores SkuSizeVariant_ID, BaseSku_IDFK, SizeStandard_IDFK, UniqueData.

My Qry_AllProductsBySize gathers all BaseSkus and the Sizes they are available in. In my case, for example, all pants are available in size 28 - 38; all shirts are available in XS-XL. This is based on the category of the product and my table relationships are set up properly so that the query looks like this:

Pant-101 | 28
Pant-101 | 30
Pant-101 | 32
Pant-101 | 34
Pant-101 | 36
Pant-101 | 38
etc.
Shirt-999 | XS
Shirt-999 | S
Shirt-999 | M
Shirt-999 | L
Shirt-999 | XL
etc.

So my query is functioning perfectly and I've done the Make Table from query. I now have Tbl_SkuBySize that stores SkuSizeVariant_ID, BaseSku_IDFK, SizeStandard_IDFK, UniqueData.

My problem is when I go to add Pant-444 for example, it shows up in my query correctly but when I append, I get duplicate records in my table of

Pant-101 | 28
Pant-101 | 30
Pant-101 | 32
Pant-101 | 34
Pant-101 | 36
Pant-101 | 38
Shirt-999 | XS
Shirt-999 | S
Shirt-999 | M
Shirt-999 | L
Shirt-999 | XL

I've tried making a compound key in Tbl_SkuBySize of the three fields SkuSizeVariant_ID, BaseSku_IDFK, SizeStandard_IDFK. But I'm still getting duplicates.

In the table design view I've set the two IDFK fields to Indexed Yes (Duplicates OK) but I'm still getting duplicates. I don't think I can change it to Yes (No Duplicates) because see example data above.

I've also tried setting Unique Values and Unique Records to yes on the property sheet of the query. Still getting duplicate records in my table though.

What am I missing?
 

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 06:43
Joined
Jun 12, 2018
Messages
29
Thanks for your reply. I have already, as the article you linked instructs, set both fields to be the PRIMARY KEY (with the little key button) but changing their index type to YES, DUPLICATE VALUES OK. But I'm still getting duplicates when I append the query to this table. I was referring to the link from @stopher at http://en.allexperts.com/q/Using-MS-Access-1440/Creating-Unique-Value-check.htm

Any ideas?
 

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 06:43
Joined
Jun 12, 2018
Messages
29
Hmmm...should I not be using a new auto ID field for Tbl_SkuBySize?

Currently I have a compound key of SkuSizeVariant_ID, BaseSku_IDFK, SizeStandard_IDFK.

SkuSizeVariant_ID Indexed (No Duplicates) (the new auto ID for this table)
BaseSku_IDFK Indexed (Duplicates OK)
SizeStandard_IDFK (Duplicates OK)

But what I want to be unique is the combination of BaseSku_IDFK, SizeStandard_IDFK in this table. So Pant-101|size 28 can only exist once within this table.

Since BaseSku_IDFK, SizeStandard_IDFK combinations will have unique dependent data, do I even need a new auto ID for this record? Is my database better off with or without a new auto ID field for this table?

Should I just make BaseSku_IDFK, SizeStandard_IDFK the compound primary key and delete the auto ID field?

What is best practice here?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:43
Joined
Feb 19, 2002
Messages
42,970
Here is a picture of a unique index. Chose a unique name. Then select each field one at a time. Leave the index name blank for fields 2 through 10 (max number allowed) to indicate that they belong to the previous named index.
 

Attachments

  • uniqueIDX2.JPG
    uniqueIDX2.JPG
    52.3 KB · Views: 4,275

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 06:43
Joined
Jun 12, 2018
Messages
29
Thanks @Pat Hartman

The query appends with no duplicates now!

The Indexes interface is weird though. I would like to share screen shots to make sure I did it correctly.

Based on your example I changed my table back to one primary key, instead of a compound key. I opened the Indexes panel and these were it's defaults:



So based on your example I modified it to look like this:



I'm not really sure why my primary key field is listed twice...

Anyway, I created an index called UniqueIDX for the two fields I want treated as a unique record. I set the "Unique" property of UniqueIDX to "Yes".

According to your explanation SizeStandards_IDFK will fall under the UniqueIDX index, since there is no index name in it's row.

...In addition to this, there are also General Field Properties, one of which is the "Indexed" property. These are currently set to "No" for the fields BaseSku_IDFK and SizeStandards_IDFK.

I guess I'm still a bit hazy on all these overlapping indexing rules. Should this property be set to "Yes, Duplicates OK"? Or is "No" more appropriate here?

I will have "Pants-101" recur in the BaseSku column. And I will have size 28 recur in the SizeStandards column. But not that compound unique indexes have entered the picture, I'm not sure which to choose.
 

Attachments

  • indexes default load.jpg
    indexes default load.jpg
    86.7 KB · Views: 4,128
  • indexes changed to.jpg
    indexes changed to.jpg
    88.5 KB · Views: 4,040
  • what about this setting.jpg
    what about this setting.jpg
    91.2 KB · Views: 76

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:43
Joined
Feb 19, 2002
Messages
42,970
I'm not really sure why my primary key field is listed twice...
This is an Access "feature" being overly helpful to you. You should turn off this "feature" and go to every single table and delete all the duplicate indexes it helpfully generated for you.

File/Access Options/Object Designers --
The AutoIndex on Import/Create should be empty. It probably has a list of suffixes and every time you create a table with field names with those suffixes, Access automatically creates an index. Remove all of them. Access also automatically adds HIDDEN indexes on all foreign keys so if you see indexes on FK fields, delete those also since you don't need two indexes on the same field.

The Indexed property on each table field applies ONLY to single field indexes and is a shortcut to create indexes without having to open the index dialog. Sometimes "helpful" things are confusing and this is certainly one of them.
 

Users who are viewing this thread

Top Bottom