Autonumber problem

[richely]

Hi all,
I am in need of some help with making a database. I have no experience of using Access so have spent last few weeks playing and have become more familiar with it but still having some problems getting it to do what I need



The point of the database would be for entering new product information which is then exported into various documents including price lists, safety data sheets and product sheets

My problem at the moment is that each product has a parent SKU and then different variants (usually 2 or 3 variants) of this product are available as child SKU
e.g. PAB1234 is the parent reagent which is then sold in different volumes and assigned its own code for each volume e.g. PAB1234-25 or PAB1234-100 for 25 ul volume or 100 ul volume vials.

The products are currently assigned this SKU based on searching an excel sheet and trying to find the last number created for that product category (MAB, PAB, REC etc). However, I would like the database to automatically assign a unique SKU when a new product is entered to avoid people inadvertently creating duplicate SKUs.

My plan was to make the parent SKU primary key as an AutoNumber. The problem is that this wont take into account the child SKUs, which are essentially the products e.g. PAB1234 will be entered and then the next product would be PAB1235

I have been trying to work out if I can put the child products into a separate sheet and link them via 1 to many relationship but its not working for various reasons

Is there a better way to do this? Essentially I want to be able to have a unique SKU code for each product and then have the child SKUs associated with it
Thanks
Richard

[orange]

Richard,


Research this to understand what autonumbers are and are not

Here is a link to several articles/different formats on Database Planning and Design.

I recommend you work through the tutorials from RogersAccessLibrary mentioned in the link. Once you have worked through the tutorials, try using the technique with your own proposed database.
Once you have experienced the process, you can use the technique with any database.
Good luck.

[ArviLaanemets]

It looks like you need a table for e.g. base article (I prefer for cases like this to use term article, product is an article you do produce, and for producing it are used one ore many other articles -components, materials, etc.), and another one for products (articles you are selling). Something like:
tblArticles: articleID, ArticleNo, ArticleName, ...
tblProducts: ProductID, BaseArticleID, Variety, ProductName.

When you sell base article only/or too, then variety is empty. When you sell several varieties of article, they have all same base article, and different varieties (BaseArticleID + Variety form an unique non-zero index), or ProductNames (must be unique).

When registering sales, you always use only table tblProducts (tblArticles is used only to define products). To select product in form, you use a combo bound to ProductID, which displays either ArticleNo of base article concantenated with Variety, or ArticleName of base article concantenated with Variety, or ProductName (whatever you like more).
Additionaly: when you decide to use article info and variety, you can drop ProductName field, when you decide to use ProductName, you can drop Variety field.

[richely]

Thanks Orange and ArviLaanemets for your replies...i quickly looked down list of what autonumbers are and what they are not and clearly says not meant to be for end user so i guess that explains why i was struggling ...i will work through some of the tutorials....i liked the idea of article tables and combo box so i will give this a go also...its a steep learning curve at the moment!

[GinaWhipp]

You might also find this helpful...
https://regina-whipp.com/blog/?p=704

[ArviLaanemets]

An afterthought: there is also a way to do same as described in post #3 with a single article/product table.
tblArticles: ArticleID, ArticleNo, ArticleName, BaseArticleID, ...

The field BaseArticleID is always filled, and contains either ArticleID of same article, or ArticleID of some other article. And ArticleID + BaseArticleID together form an unique ID.