Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / July 2004

Tip: Looking for answers? Try searching our database.

mastertable for orders/different order numbers

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Brook - 30 Jul 2004 02:35 GMT
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 - 30 Jul 2004 17:58 GMT
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

> I am setting up a database to track my customer and
> inhouse orders. My inhouse orders use NW-0000 for an
[quoted text clipped - 16 lines]
>
> Brook
Brook - 30 Jul 2004 18:37 GMT
The reason that I would like to track them in the same
table is for inventory purposes.

Brook

>-----Original Message-----
>if the two types of orders do not require tracking substantially different
[quoted text clipped - 41 lines]
>
>.
Tim Ferguson - 30 Jul 2004 23:46 GMT
> 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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.