mastertable for orders/different order numbers

[Brook]

I am setting up a database to track my customer and
inhouse orders. My inhouse orders use NW-0000 for an
autonumber identifier and amy custom orders have KI-0000
for an autonumber identifier. I would like to be able to
use one master table for all my orders both inhouse and
custom.

I was thinking that I would have to set up two different
forms for the inhouse orders and custom orders (possibly a
Tab Control?), but I am unsure on how I would use one
table for what I would like to do.

If anyone has any ideas or suggestion on a better way to
accomplish what I would like to do please fill me in or
give me any tips or suggestions that you would like.

Thanks in advance and please let me know if you have any
questions.

Brook

 

[tina]

if the two types of orders do not require tracking substantially different
data, then you should be able to house them in one table. (what you want to
avoid is having multiple fields that are only used in one type of order *or*
the other type, but not both - that leads to a lot of wasted space.) suggest
two fields to comprise the order number: OrdPrefix (for the alpha portion)
and OrdNumber (for the number portion).
not sure re the set of 4 zeros for the order number. after you enter order #
9999, what happens next?
also, if an order number sequence *without gaps* is important, you really
can't use an autonumber data type. you can't control the sequencing, and
sooner or later you will have gaps (0001, 0002, 0004, 0005, 0006, 0010,
etc). you would need to manually generate the number to ensure correct
sequencing; and if you will have more than one user entering new records *at
the same time*, that can be problematic. see the comments from MVP Lynn
Trapp, posted today to your previous thread dated 7/29 in this newsgroup.
re the form: if you house the orders in one table, then you can probably
enter them from one form. you could have a combobox, listbox or option group
to offer the two types of orders for data entry; when one type is picked for
a new record, the corresponding prefix is entered in the OrdPrefix field.

hth

 

[Brook]

The reason that I would like to track them in the same
table is for inventory purposes.

Brook

 

[Tim Ferguson]

The reason that I would like to track them in the same
table is for inventory purposes.

Sorry: that is not the what tables are about. The rule is One Table Is One
Entity. Oh yes: there is no Rule Two, either.

If inhouse orders and external orders are effectively the same thing[1],
then they belong in the same table. If they are different, then they belong
in two tables. If they are quite-a-lot the same, but really-quite-different
too, then you may want to use a technique called subtyping that uses three
tables. But the decision is always based on the semantics of the actual
business objects, never on what you would like.

As for forms, the best approach is to follow a similar rule: One Forms Is
One Process. If your data flows treat the inhouse and external orders in
radically different manners, then they probably need different forms. If,
however, your users have to bash though a pile of orders of either variety
using the same processes, then they probably need to use the same form.

Hope that helps


Tim F


[1] The "same" in this context refers to the rules that govern the
existence of the orders: for example, do both types of order have to belong
to a department? Do they both attract monetary payments? Partial payments?
Relate to products made by the company or bought it? And so on. Once you
have started the analysis phase it usually becomes clear that the
complications of treating them both the same far outweigh the advantages;
or else that you are duplicating all the data flows in two needlessly
separated maps.