The best Primary Key solution for my situation.

[Yaac]

I have created a database for products that i sell. I would like the Primary
key to be a combination of the Category Id for that product and a generated
number. This number will idetify this product but also has to be easy to type
on a regular basis. For exaple right now i have Category 83 is toys. I would
like all of the toys to be 83101, 83102, 83103 etc. etc.. What is the best
way to go about this. Can i have a macro for a control in a form to auto
generate ofter updating the Category feild.

Is this a bad idea for a primary key? Any sugestions or warnings are
appreciated.
Thanks,

 

[Jeff Boyce]

This is a bad idea for a primary key.

I'm assuming you're considering putting two values into one field. Don't.
One of the basic tenants of normalization (re: relational database design)
is "one fact, one field".

You can always make the two-field solution a unique index, to prevent
duplications.

You can always create a query that combines the two fields for display
purposes.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

 

[Yaac]

Makes sence.
Without the first two digits of the CategoryID, i am limited to a 4 digit
number for a ProductID. I currently have random digits from 0011 to 8223. If
i want my product ID to be from 0 to 9999, can i easily make a default value
of a form feild for ProductID search and find the numbers that are not used?

 

[Jeff Boyce]

I'm not sure what you mean by "find the numbers that are not used". If the
numbers that are in the table are only the used ones, you won't have any to
"find".

You've been describing a "how" (how you want to do something). Can you
describe more of the "what" (what business need are you attempting to
satisfy)?

Perhaps, something like "I need to uniquely identify products" -- that
leaves a lot of different ways how open...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

 

[Yaac]

That is exactyly the case. I have to identify products. Currently the
products are random numbers from 0011 to 8223. How can i fill in the holes by
using a form to create entries for new products?

 

[Jeff Boyce]

If all you need is a ProductID#, why do you have to fill in the holes?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Yaac]

Here is what has happend.

I created a New Access database with information from an old version of
Accpac.

We have existing Product Numbers scrambled from 0 to 9999. So i began
creating new numbers with 5 digits, the first two being the Catagory ID. So i
have a products table with Product ID of 83111 for example. That is the
primary key for that table. 83 is the categoryID and primary key for toys
from the category table, 111 is a porduct # for kites.

You said one talbe one piece of info. So i take out 83 and i am left with
111, if i have other products with product# of 111 i need to change them. If
i want to keep the Primary key for the Products table the Product# i need to
fill in all of the holes that were left from our manualy entry in Accpac. My
understanding is that i would also have a query put the 83 & 111 together in
the form if that is what i want to show.

I guess if i was to start all over, what is the best way to enter product#'s
am i asking for trouble by haveing them as the primary key?

 

[Jeff Boyce]

One fact, one field doesn't mean you have to throw away the categoryID, just
not keep it in the same field. If you want a primary key to consist of
CategoryID and ProductID, you can set that combination as your primary key.

My previous question was more to the issue of "filling in" between existing
numbers. Are you saying that the ProductIDs are already determined? I
thought you wanted to be able to assign product IDs?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Yaac]

We want to leave the existing product ID's of course, any new Product ID's i
have to type in a bunch of numbers before i get an origional because of the
manual entry that was used in the past. I wanted to simplify this by haveing
the form suggest the next available Product ID

 

[Jeff Boyce]

The problem with (what sounds like) re-using old ProductIDs is that the
numbers were used for something before, and your data could end up confused
about which (new or old) product is being referred to.

Are any humans going to need to see the ProductID, or could you just show
them the Product in a combo box? If you don't need humans to see ProductID,
you can make that an Autonumber instead, and let Access pick whatever ID it
wants to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Yaac]

We are not going to be reusing product ID's we are transfering all
information from accpac to access. So they are current ID's but they were
entered manually so there are a lot of digits that were not used. The Product
ID's will be used for ordering and billing etc.
Thanks

 

[John Vinson]

That is exactyly the case. I have to identify products. Currently the
products are random numbers from 0011 to 8223. How can i fill in the holes by
using a form to create entries for new products?

PMFJI but...

One way to do this is to create a little auxiliary table named Num,
with one long-integer field N. Fill this table with values from 1
through 9999.

Use an Unmatched Query as the rowsource of a combo box:

SELECT N
FROM Num LEFT JOIN Products
ON Num.N = Products.ProductID
WHERE Products.ProductID IS NULL
ORDER BY Rnd(N);

This combo will have all *currently unused* ProductID's in that range,
in random order; the user can pick one (the first one, or any of them)
and it will plug that number into the new record's product ID.

John W. Vinson[MVP]