Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Possible to allow duplicates on table?

Status
Not open for further replies.

lorirobn

MIS
Mar 15, 2005
450
US
Hi,

I am setting up a small table in my Access database. Is it possible to allow duplicates? It is not one of my main tables. There are 2 fields - Design Type, and Item Number. The 2 together would make up a logical 'index'. There may possibly be duplicates, which would be fine. But is this allowed in Access? If so, how do you set that up?

Thanks in advance.
Lori
 
PS - this table will be a linked table, on an MS SQL server, in case that makes a difference.
 
Yes. If you just want duplicates all over the place, don't create any Primary Keys. Now, do mean one Designtype can have many Itemnumbers but no duplicate Designtype? Or the combination of Designtype/Itemnumbers can have duplicates?
 
One DesignType can have many ItemNumbers.
There cannot be duplicate DesignTypes (however, I get a little confused by this, because on the table, because one DesignType can have many itemnumbers, DesignType could be repeated just on the table).

And yes, it is possible that a combination of DesignType/ItemNumber would be duplicated.
 
So, you want to create a possibly NOT UPDATABLE table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
So what you want to do is have DesignType and ItemNumber as a multi-field primary key. So taken together, DesignType/Itemnumber will be unique. eg.
Porsche/944
Porsche/928
Porsche/911
Porsche/carrera

Go to design view of your table, click the little square next to DesignType, hold down your Shift key and then click the little square next to your ItemNumber. They should both be highlighted. Now click the Key Button.
 
If a table with 2 fields allows duplicates (of both fields), how do you distinguish between them? What information does it provide?

Maybe you need an additional field as an identifier, in addition to the designtype and itemnumber.
 
PHV and pbrodsky
You mean I have to learn how to READ to do Access?
"You mean my whole fallacy’s wrong?" McLuhan
Add another key, geeez!



 
Thanks for all the responses. I disappeared for a few days...

fneily, you got my design correctly. And yes, there may be duplicates - for example, Porsche/944 might appear twice. And pbrodsky - your question of identifier is exactly why I posted this question. I was wondering if I needed such an identifier, or if Access would allow me to not have one. The thing is, I do not need to distinguish between each of the duplicates.

I may as well explain the table's use (which I actually posted in another thread). I have a database of all rooms in a large facility. Some groups of rooms have been designed by decorators, thus "Design Type". My main tables are Rooms, Items, and RoomItems linking the two. Now I want to set up a prototype of what items would be part of each design type.

As a hypothetical example, Design Type 2000-1 would contain items:
Mirror, Item 425
Bed Throw, Item 199
Bed Pillow, Item 222
Mirror, Item 425
Bed Pillow, Item 222

I don't need to distinguish between the two mirrors or two bed pillows. I will be using this table so the user can print what items should be in a room (may use it in other ways down the road but am not sure right now).
 
Why not have DesignType/ItemNumber as primary key and a quantity field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top