Basic design question

[Brant]

I have a very simple database with one table for which we utilize in making labels for our inbound inventory. I would like to expand this database to allow for inventory tracking and thus need it to be able to handle outbound side of the inventory. I need some help with how to do this. The exisiting table has is data related to product info, serial #, etc. and I need to have a few more fields added that have checkbox for sold, sold date, customer ID, and order #.



The difficulty I am having is figuring out, as orders are taken and the product is scanned, how to set up the databse so that time can be minimized in the data entry of these add'l data fields. We will be scanning mulitple serial#'s per order so how do I set this up so the order and customer ID only need to be entered one time per order. Hope this makes sense. Thanks for any input!

[ajetrumpet]

take a look here: http://www.ajenterprisesonline.com/_forms/#whichone

that example uses a temp table to hold all the entries until everything is scanned. and at the end, when the 'enter' button is pressed, all records from the temp are appended to the perm table and deleted out of the temp. this, IMO, reduces the risk of corrupting a table.

you might also want to check out MS's template for inventory. they use good methods, but some of it is not very clever. there is stuff to be learned from it though.

[jzwp11]

Welcome to the forum!

Just adding additional fields to your table will not accomplish what you want.

We'll have to do some analysis of your application in order to identify relationships.

First, we still need a table to hold the basic product information. This would include fields such as your product number and product description. I would also recommend have an autonumber, primary key field in this table (and every table for that matter). The primary key field just assigns a unique number to each record in that particular table. The primary key should have no significance to your users.

tblProducts
-pkProductID primary key, autonumber
-txtProductNumber
-txtProductDesc (description field)

I generally use prefixes in my field names pk denotes primary key, txt denotes a text field. I assumed that your product number might contain numbers and letters, so I called it a text field. Additionally, it is generally recommended to not have special characters or spaces in your table and field names.

I did not include a serial number field in the product table because for a particular product, you will have several pieces each with their own serial number. Since a product can have many pieces, that describes a one-to-many relationship. So we will need a table related to tblProducts that will hold the unique serial numbers for each product produced

tblProductSerialNumbers
-pkProdSerialNumbersID primary key, autonumber
-fkProductID
-SerialNo (a field to hold the serial number)

The fk in fkProductID above denotes a foreign key field that relates back to the corresponding record for a particular product that is listed in tblProducts, the foreign key field must be a long number datatype field since the corresonding autonumber field in tblProducts is also a long data type--just a special one that increments.
You might have other fields in this table, perhaps a date field that holds the date on which the product with the specific serial number was produced.

Since customers buy your products, you will need a table to hold the basic customer information

tblCustomers
-pkCustomerID primary key, autonumber
-txtCustomerName
-txtAddress
-txtCity
etc.


Now a company can place many orders over time so again you have a one(customer)-to-many(orders) relationship.

tblCustomerOrders
-pkCustomerOrderID primary key, autonumber
-fkCustomerID foreign key relating back to tblCustomers
-OrderNumber
-dteOrder (order date, dte=date)

Now for each order, a customer may buy several products each with specific serial numbers, so one(order)-to-many(product/serial#s)

tblCustomerOrderDetail
-pkCustOrderDetailID primary key, autonumber
-fkCustomerOrderID foreign key to tblCustomerOrders
-fkProdSerialNumbersID foreign key to tblProductSerialNumbers