Access Database Design

[Nick]

I need help with creating a database. I have taught myself the rudimentary
basics of Access through trial and error over the last 4 years. However, I
am certain that I am utilizing less than 10 percent of Access' capabilities.

My company deals in used product. Therefore, we source and sell goods
manufactured by multiple OEMs (thus multiple part IDs, model IDs, and serial
number styles). A majority of our goods are sold on "exchange" --- whereby
the customer receives a discounted price in return for the damaged good being
sent back to us. Thus, an RMA is used at the time of sale in anticipation of
the damage/exchange good being sent to us.

Currently:
1) I do NOT use Access to track the RMA process --- those RMAs that are
"closed" based on positive receipt of the exchange item. I track "open" RMAs
in a separate, manual Excel sheet.
2) I have separate tables for shipping carriers, product models, product
part numbers, customers, etc. This information is fed via a Form into a
single table. This single (master) table refers back to the "sub" tables
which contain the category lists. Thus, the "Shipped Table" reflects the
carrier as FedEx. The ID of "FedEx" is refered back to the separate "Carrier
Table". I provide this detail because none of these tables are formalized in
the "Relationships" feature. However, from what I read on-line, it seems
Relationships are the most important, advantage to using Access.

Sorry to trouble anyone with all this minutia. Any advice or tutelage is
greatly appreciated.

 

[Ken Hudson]

Hi Nick,
I haven't seen any replies to your post, so I thought I would give you some
feedback.
I have been using Access for 7-8 years and consider myself an intermediate
user. Fortunately, I work for a large company and was able to attend a
database modeling class (eight hours) a few years ago. In it I learned the
rudiments of designing a relational database. As you are aware, table design
and setting table relationships is critical.
Some folks have great intuition and logical thought processes and can pick
up these concepts by reading books.
So, my two advice is find some way to learn about database modeling through
a formal course, manuals, friends, trial and error, community news groups,
etc.
Here are some of my thoughts about your specific DB design.

Set up a "Sales" table (tblSales) with fields:
PoNumber (Primary Key)
CustomerID (Foreign Key)
ProductID (Foreign Key)
SaleDate
SalePrice
ShipDate
RMS (Y/N)

Set up a "Product" table (tblProduct) with fields:
ProductID (Primary Key)
SerialNumber
PartNumber

Set up a "Customer" table (tblCustomers) with fields:
CustomerID (Primary Key)
CustomerName
CustomerAddStreet
.....

Set up a "RMA" table (tblRMA) with fields:
RMAID (Primary Key
PoNumber (Foreign Key)
MerchReturnDate

The primary keys get linked to the foreign keys in the table relationships.

Remember, I am only an intermediate user, so take my response in that light.
This design may, however, help you get started or al least give you an intro
to the basics of design.