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.
10
