product number based upon type

[Mindshadow]

Hi

I have a table with 5 product types Universal, Custom Stocked, Stocked, Accessory and Other

In an input form I am trying to get the field Part Number to automatically enter the next number in the sequence based upon type selected i.e Universal starts at 100000, Custom Stocked 200000 and so on.

Si if I enter a new part that is Universal it will look up the last Universal Part Number used and assign the next value.

Advice and pointers really really appreciated
Rob

 

[jdraw]

Why do these number/identifiers have to be "customized" in that manner? With data base keeping things simple --one fact, one field -- is often the preferred design.
You have 2 fields:
ProductNumber and ProductType
and you can concatenate them for presentation if necessary.

You can google DMax +1 to get the next number for your Types, if you really need that scheme.

 

[Mindshadow]

Hi draw and thank you for your reply.
I think I may have made things over confused by trying to keep them simple.

My idea was that if I had a different number set of each type, the part number would act as an easy reference as to where that part falls - for example these are all small parts with only enough room on the pot for the part number. I could then store all the parts in a grouped order. If the part number were customised I thought that this would be an easy way of doing this.

I have more than two fields and tried to do it with the IIF and DMAX but could not get it to work. My fields (number fields) are Part Number, Custom Stocked, Stocked, Accessory and Other. My text field "TYPE" is a drop down selection containing the text of the types.

Rob

 

[jdraw]

Is this your first database?
Do you have some requirements/specifications?
Do you have a description of th "business" this database is intended to support?
In 5-6 sentences, what is the business and processes involved - in simple English as you would tell a 10yr old or your 80 yr old granny.

 

[Mindshadow]

Hi jdraw
Yes this is my first database other than excel spreadsheet types

The business is embedding minerals, pure elements and compounds into differnet types of mounts (blocks) polishing them to 1/4 micron finish and used for electron microscopy.
The database is a list of different types of blocks in different sizes machined with various hole layouts
The aim of the database is to give each block a record recording, type, size, description, qty in stock, stock level required, order needed Yes/No(req-qty) and amount, cost price and two images.

 

[jdraw]

What is the structure of your evolving database? What tables? What relationships?
Type,size and description might be reserved words in Access. Just a heads up -see this for a list.

Also do NOT use names with embedded spaces for fields or objects with Access. Failure will cause you syntax issues a some point. Use alphanumerics and "_" underscore only.

Research Access Inventory or Stock Control and review the threads/links for ideas and insight.

Good luck.

 

[gemma-the-husky]

anyway, assuming you have two separate fields for the product type, then you can get the highest number by

highest = nz(dmax("numberfield", "tablename", "product type = " & chr(34) & "whatever" & chr(34)),0)

then increment this by 1 for the new number.

Personally, I think it would be better to have a single sequence, which would make it easier to reallocate any existing products into different groups. You can still use the same code idea with a single ID sequence

 

[Mindshadow]

Thank you for all your suggestions and help so far but I am now even more confused.

The fields I have are my primary key FileID (this is autocalculated and hidden) TYPE is a text field with a validation rule that only Universal, Custom Stocked, Custom, ect can be entered. I then have numeric fields headed with the types of blocks e.g Universal, Custom_Stocked ect. and finally a numeric field called Part_Number. All this data is on one table.

What i am trying to do is create the part number based upon the following rule when entering a new record in a form.
If the text of the TYPE field = "Universal" then Part_Number = dmax [Universal]+1, if the text in TYPE field = "Custom Stocked" then Part Number = Dmax [Custom_stocked]+1 and so on.

In my head this seems an easy route. Am I making things over complicated?

 

[Mindshadow]

Hi O great wise ones, I have tried and tried to sort this out but continually fail.
I have a table with the following fields:
Primary Key - This is hidden and not used as such
Type - This is a drop down text field with the following options: Universal, Custom Stocked, Stocked, Accessory and Other.
I also have 5 numeric fields headed with the above titles that start at different number i.e Universal starts at 100000, Custom Stocked at 200000 and so on.

I have managed to create an input form to add a new product. In this form I have a box named part number and a drop down selection box named Type. If the Type that is selected from the Type drop down = Universal, I want the box named Part Number to auto fill by looking up the highest value in the selected field in the table and adding 1 This number would not be allowed to be duplicated.

I have it in my head that this would be done using the IIF statement but I cannot seem to get it to work and am unsure where to put this statement i.e in the form or in the table?

You help would greatly be appreciated as I have tried researching online and read and re read Access for Dummies.