Table Designs 1

[specktur]

I have been tasked with implementing a new Part Number Generator. I have done simple databases with not a problem. This one is pretty big.
What I need to do is generate a part number based on choises a user selects.

Our part numbers look like this
XX-##-##-####

The XX is a prefix this determines wether the part is hydraulic or electronic
The first ## - is a series number 01 - 10 (Pumps, Valves,etc...)
The second ## is a detail number 01-20 (Usually a vendor or type of product (Say its a pump detail would be Centrifugal))
and the last four #### is a sequential number. (is the next number in the series for unique products)
Ex: HY01010001 = Hydraulic - Pump - Centrifugal - Seq num (basiclly pump 1)
HY01010002 = Hydraulic - Pump - Gear - Seq num (basiclly pump 2)
EL01010001 = Electronics - Control - Harness - Seq num (basically controller #1, harness #1)
So on and so on.
I have a table setup for the prefix and a second table that contains the series number and desc, a third that holds the detail number and desc. I'm stuck on the seq number part.
The reason I'm stuck is because each part number will have a different sequence. How do I ensure that HY01010002 goes up to HY01010003 and that EL01010001 goes to EL01010002?
If I have confused please let me know - I will try to clarify.

Thanks for reading,
Sean

 

[specktur]

I don't know how to edit a post so here is my edit:

Ex: HY01010001 = Hydraulic - Pump - Centrifugal - Seq num (basiclly Centrifugal pump #1)
HY01020001 = Hydraulic - Pump - Gear - Seq num (basiclly Gear Pump #1 )
EL01010001 = Electronics - Control - Harness - Seq num (basically controller #1, harness #1)

 

[PHV]

A crude monouser way is to play with the DMax function:
strSeqNum = Format(1+Nz(DMax("Right([PartNum],4)","yourTable","Left([PartNum],6)='" & strPrefix & strSerie & strDetail & "'"),0),"0000")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[specktur]

Thanks PHV,

I'm a little confused - am I making another table? What exactly does "your table" refer to? I dont quite understand what happens in your formula, I think its a little over my head.

Sean

 

[PHV]

The PartNumbers aren't stored somewhere ?
If not, create a table for last used seqnum for each prefix-serie-detail combination.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[specktur]

PHV,
I gave you a star because your expression works!

Disclaimer - Stupid question follows.
Now, how do I increment it by one. I did this StrSeqNum = StrSeqNum + 1. But I lose the leading zeros. I tried a format statement but to no avail. any suggestions.

Thanks alot.

 

[PHV]

How is Dim'ed strSeqNum ?
Have you tried this (as I showed you) ?
StrSeqNum = Format(StrSeqNum + 1, "0000")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[specktur]

Ok, I got it PHV.
They now want to select each of the criteria (prefix, series, detail) and search for the last used (Highest) numbered product. So I can work it a different way. After they select their criteria I can query or filter the data and go from there.

Thanks, You have been a great help.