By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,741 Members | 2,004 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,741 IT Pros & Developers. It's quick & easy.

No Unique Field to Identify Records

P: 10
I am working with Access 2000 in Windows XP.

I am putting together a table describing a pricing structure for product groups. A single product group, however, can have different prices (per lb) depending on what plant they are from. Despite the somewhat nonsensical nature of this pricing structure, it is what I must work with.

So basically the situation is that I am trying to put together a table whose records' uniqueness is identified by the combination of the fields plant and product group. Is there a good way to do this, other than just putting it in like that? The thing that bothers me is that there is no way I see to put a primary key into this table, because there is no field that is completely unique. This table will be used in many calculations on down the road in the queries in my database, and it is important that I have it designed well. I am worried that I am already seeing the consequences of this bad design in a query that uses this table and is having issues.

Any help in this matter will be greatly appeciated!
Jul 5 '07 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,365
You can specify a multi-field primary key, I know it's a misnomer, by selecting the two fields and right-clicking and selecting primary key. Or you could use an autonumber.
Jul 5 '07 #2

puppydogbuddy
Expert 100+
P: 1,923
I am working with Access 2000 in Windows XP.

I am putting together a table describing a pricing structure for product groups. A single product group, however, can have different prices (per lb) depending on what plant they are from. Despite the somewhat nonsensical nature of this pricing structure, it is what I must work with.

So basically the situation is that I am trying to put together a table whose records' uniqueness is identified by the combination of the fields plant and product group. Is there a good way to do this, other than just putting it in like that? The thing that bothers me is that there is no way I see to put a primary key into this table, because there is no field that is completely unique. This table will be used in many calculations on down the road in the queries in my database, and it is important that I have it designed well. I am worried that I am already seeing the consequences of this bad design in a query that uses this table and is having issues.

Any help in this matter will be greatly appeciated!
Firstly, you need to be aware that the primary key does not have to be meaningful. It just has to create a unique identifier (like the autonumber data type does) for each record in the table. In fact, most designers recommend that you not base the primary key on meaningful data in the table because the structure of that data could change, making your key invalid, creating unique complications. For example social security#'s could conceivably expand from 9 to 10 digits sometime in the future. Think of all the databases that will have to be restructured if they used ssn# as the primary key.

With that in mind, your product table could look something like this:

Autonumber.......Product............Plant......... .Unit........Price
...... Key.............Group............................. ................Per Unit
000001
000002
000003

Now to query above, you can do this:
Select * From ProductTable..........this will give you all the records in the table

If you want select a product group made in plant X:
Select * From ProductTable Where ProductGroup = zzzz and Plant = X

Hope this gives you a better idea. Before proceeding, you should take some of the tutorials on Database structure and normalization by clicking the link at the top of the Access forum home page.
Jul 5 '07 #3

P: 10
Thank you both very much!
Jul 6 '07 #4

thisisntwally
P: 19
you could also build your own (if you wanted to avoid autonumber) with =dmax("PKfield",[table])+1
Jul 6 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.