Single Table / Multiple Inventory Order Numbers

[Brook]

good day,

What I am trying to do is maintain a single inventory table, but I have
inentory for the following:

Custom Orders
Stock Orders
Sample Orders

What I would like to do is have some type of identifying factor for each
type of order such as Custom Orders (CUST), Stock Orders (STK) and sample
orders (SPL) and some type of incremental numbering.

I am unsure if any of this is possible, but wanted to check.

Thanks in advance for any tips , suggestions..

Brook

 

[rowiga]

I'm assuming you mean something like:

CUST0001
CUST0002
STK0001
SPL0001
SPL0002
SPL0003

My preference would be to just use an autonumber field for the order number
regardless of order type. In other words, all orders increment from the same
pool. It will be easier down the road when working with the data. Just have a
field to store the order type and use it to query the same.

Yeah, your method id doable but I think you'll be happier with a more simple
design.

 

[Brook]

Yes you are correct in assuming what I am looking for, and the reason I am
wanting/needing to do this, is b/c I have a query that I will be using for
the same calculations for all types of orders, so I thought that a single
table would be beneficial. If you have another idea, I am definately open for
anything.

Brook

 

[tina]

generally speaking, it makes sense put all Orders in an Orders table;
"Custom", "Stock", and "Sample" are data that describe the order, grouping
each order into a "type" or "category". (the alternative, separate tables
for each type of order, is not proper data normalization.)

suggest you add a field to tblOrders, i'll call it OrderType. use that field
to designate each order record as "Custom", "Stock", "Sample", etc. you can
easily use a single query to pull records of a specific type, simply by
specifying a criteria on the OrderType field.

for the incremental number, you'll want a separate field in the table to
store the number (don't put it in the OrderType field). if you want the
numbers to *not* have gaps in the sequence, such as 0001, 0002, 0005, 0007,
0008, 0011 - then do *not* use AutoNumber, because there is no way to ensure
that you won't have gaps in the sequence.

usually, incremental, sequential numbers are generated programmatically
during data entry. if your database will have multiple users entering
records in tblOrders at the same time, creating "gap-less" sequential
numbering can get tricky. this issue has been discussed many times in the
newsgroups, but i've never seen anyone come up with a completely foolproof
solution (of course, i could have missed that discussion! <g>).

hth

 

[Brook]

Tina,

Thank you for the information this has helped me tremedously! I do have
another question though. Would it be possible to have check box for me to
choose what type of "new" order I have, and then that check box would assign
the Type Code to the proper field in my table? If so, do you have any
suggestion on how I could do this, and how it would know to add an increment
to the proper order type?

Brook

 

[tina]

answers inline.

Tina,

Thank you for the information this has helped me tremedously! I do have
another question though. Would it be possible to have check box for me to
choose what type of "new" order I have, and then that check box would assign
the Type Code to the proper field in my table? If so, do you have any
suggestion on how I could do this,

to use checkboxes to select an OrderType, i would probably create an unbound
OptionGroup control on the data entry form, using the Controls Wizard, to
show the OrderType choices. then i would use code to set the value of the
OrderType field, based on which box was checked. usually, you would also
need to add code to the form's Current event, to show the correct checkbox
selection in existing records. unless you're comfortable with using
OptionGroup controls and VBA code, though, you'd probably find it a lot
easier to use a combo box. to do that:

create the combo box on your data entry form, i'll call it cboOrderType.
make sure the form's RecordSource is based on tblOrders. set the combobox's
ControlSource to OrderType. set the following properties of the combobox as:

RowSourceType: Value List
RowSource: Custom; Stock; Sample
ColumnCount: 1
ColumnHeads: No
BoundColumn: 1
LimitToList: Yes

and how it would know to add an increment
to the proper order type?

you would need to add the incremental number programmatically, as i said
before. if your database will have multiple concurrent data entry users, i
would probably *not* generate and assign the number until the record is
about to be saved to the table. if, however, you will *never* have more than
one user entering records in the table at the same time, you can generate
the incremental number and assign it to the record on the AfterUpdate event
of the control that is bound to the OrderType field.

in either scenario, one fairly easy way to generate the sequential number in
code is

Me!OrderNum = Nz(DMax("OrderNum", "tblOrders", "OrderType = '" &
Me!OrderType & "'"), 0) + 1

(the code would be all on one line.) in the example above, OrderNum is the
name of the field in tblOrders, where the sequential number is stored.
*remember*, though: when you have multiple concurrent users, the TIMING of
when the number is generated and saved can be very tricky - and can result
in non-sequential assignment and/or gaps in the sequence.

hth