Prefix number form one table onto Primary key.

[Guest]

I have an existing table for products and an existing table for categories.
The key also happens to be the Product ID. For example, item 105 is a
frisbee. It is a toy, 83 is the and Category ID (not a primary key) for toys.
So i will make the new product ID (the new primary key in products table)
83105. I can change the existinig data with an append query i imaginie, but
What i am having trouble getting a solution for is using a form to enter new
products. I would like to be able to enter the product info and have it
create a primary key for that product by using the category numbers and then
the next primary key available for that category. My first instink would have
been to use autonumber, but i have read that is not a possible solution.

If i have
82101, 82102... 82150, 83102, 83103, 83104, 83105, 84101, 84102
82 = tools
83 = toys
84 = candy
How do i create 83106 using the category selected from a combo box entered
in the form.


I hope you can help me,
Thanks for your time.
Yaac

 

[Luke Dalessandro]

Yaac,

Most of the flexibility and power of a relational database system like
access is the concept of keys/relationships between tables. What you are
trying to do sort of bypasses all of the customary relational database
design principles and is a recipe for disaster (one simple example of a
problem is that you are restricting yourself to only 100 products per
category).

The standard approach in a grouping situation is to simply set up a
one-many relationship between the two tables, with Category ID stored as
a foreign key in the products table. The product id would be an
autonumber. Then, all of the built in database tools will actually work
for you. You can run totals queries, joins, and can cascade updates and
deletes automatically.

Actually doing what you are suggesting would take a fair bit of
programming and a good understanding of SQL so that you could select the
highest "ID" between two category boundaries and increment it.

Don't worry about Category ID not being a primary key. Just set the
field's Index property to Yes (No duplicates) and it will act like one
(presumably no two groups have the same group ID).

Good luck,
Luke

 

[Guest]

Sorry maybe you missunderstood, there would be 999 item numbers available
each category, but yes still limited.
It sounds like there are too many other problems that could occur.
That's a shame, haveing the e category as the first two digits would be very
nice.