No duplicates on composite field.

[boundfree]

Hello,

Say I had item X, Y and Z and they are numbered 1-20.

They all can be copied 20 times, and have to have a unique number.



So a table of all these would look like this:

Item Number
X 1
Y 1
Z 1
X 2
Y 2
Z 2

etc all the way up to 20.

How could you index these fields so that duplicates are allowed, but so that no duplicates in the combination of fields is allowed?

boundfree

[LillMcGill]

Table Design View. Click Index button. Make one index name that maps to two field names. Click in the Index name and set property: Unique: YEs

[rpeare]

Set the combination of the two fields as your primary key. that will prevent duplicates over both fields. I would also recommend you have a unique identifier field (autonumber) for easy reference.

[LillMcGill]

Why would you do that?
The point of autonumber is to have a meaningless value that no one will ever want to change. It's ruined the moment you give it meaning.
Also, composite PKs are a pain.
So why would you have a field that makes a great PK, but use a sucky composite key instead?

Create a multiple-field index using the Index button.

Or make it your PK, but you don't need an autonumber too

[rpeare]

I disagree. This sort of looks like a matrix and if you are going to store a matrix value (let's say statue model and statue size) you want an easy reference instead of carrying TWO fields as the child records if you have unique value (NOT A PRIMARY KEY they are NOT the same thing) you only have to carry a single value and you can perform lookups to retreive the statue model and size for that unique identifier. an index is not the same as a unique key, and a primary key is not the same as a unique key. the autonumber is specifically for easy reference of your table regardless of what it's index is or it's primary key is. The whole purpose of a unique key for a table is exactly what you stated, a value nobody will ever change for any reason, it is strictly for reference of other values that ARE important.

[LillMcGill]

None of that explained why a composite PK would be created instead of using the autonumber that is already in the table anyway. I can think of several disadvantages to a composite key, but you haven't told me any advantages.

[rpeare]

he's looking to prevent duplicates in a table that is carrying two foreign keys, where each combination of foreign keys can only appear one time in the table. The easiest way to do that is to create a primary key based on the two fields, then duplicates are automatically refused. No further muss or fuss required. You're still getting hung up on the use of a PK vs Unique key.

Take a 3x3 matrix in table format

tblItems
ItemID ItemName
1      ItemA
2      ItemB
3      ItemC

tblSizes
SizeID SizeDesc
1      10"
2      12"
3      15"

tblItemSizeMatrix
ISID ItemID SizeID
1    1      1
2    1      2 
3    1      3
4    2      1
5    2      2
6    2      3
7    3      1  
8    3      2
9    3      3

In the itemsizematrix table if you set your PRIMARY KEY to a combination of ItemID/SizeID it will prevent duplicates but your UNIQUE key remains a field that has no meaning other than to other tables in your database. When you are referencing a matrix item you reference it by the ISID (which you are advocating) it has absolutely nothing to do with the primary key. There is no disadvantage to setting a dual field PRIMARY key as long as it is not also your intended UNIQUE key.

[LillMcGill]

You're still getting hung up on the use of a PK vs Unique key.

No I'm not, and I never was.

You have corrected me when my suggestion is far more common and certainly not wrong at all, works well, and again, is very common. It answered his question, likely solved his problem and showed him a valuable tool. I certainly never would have expected that anyone could find reason to argue with it.

And as you corrected me, you suggested to the guy new to Access that he create a dual field PK, but use a different field as if it were the PK? Is this correct?

[rpeare]

You and I are clearly not communicating well on someone else's thread so I will drop it instead of extending the conversation. You have your way, I have mine, your original post attacked my method, which is the only reason I responded to you instead of the poster.

[LillMcGill]

LOL,

your original post attacked my method

That is how I felt about your original post. My first post was

Table Design View. Click Index button. Make one index name that maps to two field names. Click in the Index name and set property: Unique: YEs

But it seemed that didn't suit, someone corrected my perfectly reasonable post. Of course now I understand that you didn't see my post. LOl, sorry to snap at ya.

[boundfree]

Thanks both for your help.
It is interesting to see that there are many possible ways of doing things on access.
To sum up, using a composite PK is good in this situation because it is quite simple to set up. However, creating a new index takes a few more steps but does the same job.
I have been advised elsewhere to always have an autonumber and it seems like the right thing to have, even if it just sits there doing nothing.

---

Now say I wanted to autogenerate the rest of the combinations.

So for example, I have sculptures which have a specific edition size, so I have my table looking like this:

title edition size sculpturenumber
sculpture1 20 1
sculpture2 10 1
sculpture3 200 1

How can I autogenerate all the rest in the edition?

thanks

Boundfree

[rpeare]

I wouldn't enter those on the table directly.

I'd be more inclined to choose a sculpture type, choose the lot size and then have some vb code cycle create a record for each item (assuming you want to be able to track individual sculptures, i.e. it's important for you to know that sculpture 1 of a series went to Person A)

If you don't care about being able to track individual scuptures you don't really need to have the sculpture number, i.e. you just want to know that x many of a particular sculpture production run are still available.

[SteveH2508]

Generating every possible combination is easy. You create what is technically known as a Cartesian join in a query. Add the relevant tables to your query but do not join them. Put in the fields that you want. Run the query and it gives you every combination (it 'multiplies' the two tables together). Use that as an action query to write your records quickly.

[rpeare]

I don't think that's what he's saying though.

I'm assuming it's the same project he's posted about on other threads where the tables to generate a cartesian query do not exist. He would have the sculpture name/ID but nothing for the second half of the join.

His company is producing a statue model, MODELX, in that model run there are 20 castings made. If he wants to be able to track each piece individually there's nothing to make a cartesian query with. It would require cycling from 1 to the maximum number of castings and adding a record to the table of 'available' items for sale. He could have a table that just has numbers listed from 1 to x where x would be the upper bound of any possible production run and limit that to the top y items then create a cartesian query based on that but I don't see the need for it.

you could do something like (didn't test this code so don't hold me to it)

Assume you have a form called 'CreateProductionRun' with two fields on it, a field that has your MODELID and a field MODELSMADE that has the number of items in the production run. Let's also assume he has a table called tblItems with a list of all the models possible for production with a MODELID unique identifier. Now let's say you want to create a record in the table "tblAvailableItems"

dim db as database
dim ssql as string
dim i as integer
dim iMaxModels as integer

set db = currentdb
i = 1
imaxmodels = forms!createproductionrun!modelsmade

do while i <= imaxmodels
     ssql = "INSERT INTO tblAvailableItems (ModelID, IssueNumber) 
     ssql = ssql & " VALUES ("
     ssql = ssql & forms!createproductionrun!modelid & ","
     ssql = ssql & i
     ssql = ssql & ")"

     db.execute ssql
     i = i + 1

loop

set db = nothing

[boblarson]

I may have missed it but I would use an Autonumber as the PRIMARY KEY but use a MULTI-FIELD INDEX to keep duplicates from occuring. This would simplify matters since you would not be able to duplicate over the multiple fields like you want to be able to keep from happening but if you need to use the key as a foreign key in another table you only have to use the autonumber which simplifies things and it stores less redundant data.

See here for how to set up a Multi-Field Index:
http://www.btabdevelopment.com/ts/mfi