Help with a Word Order Database

[donm1021]

I am a modular housing sales consultant and I am trying to setup a work order database in access. This database would allow me to spec out a modular home for a prospective customer/client as they provide me with all of their home options (floors, exterior, doors & windows, interior, appliances, bathrooms, kitchen, etc.) and then print out an invoice with an estimated total.

I am currently using an excel spreadsheet and it is very cumbersome to use. I use it as an invoice and the invoice contains the following information on it.

Bill to:
Dealer Name
Dealer Rep
Phone, Fax, Cell

Ship to:
Customer Name
Customer Address
Phone, Fax, Cell

Home Options:
Floors:
Joist: (Structure Wood 2X8 Joist, Structure Wood 2X10 Joist, Plywood 2X10 Joist)
Carpet: (Cappuchinno, Biscuit, Pebble, Summer Straw, Praline, Herringbone, Cashew, Buckskin, Shale, Botanical, Cordovan, Stone, Cascade, Black Ice)
Vinyl Flooring: (Woodgrain #05296, Square Tile #57296, Mosaic #55296, Tan Stone #08296, Large Square Tile #07296)

Interior:
Interior Doors: (install, ship loose, 6 panel, flat)
Hardware: (Brass, Brushed Nickle, Round Knob, Level Handle)
Crown Molding: (4", 5")
Base Molding: (4", 5")

Appliances:
Color: (White, Black, Bisque, Stainless)
Dishwasher: (Standard, Power Scrub)
Microwave: (Over Range, Free Standing)

There are other options but this is just a brief listing of options as an example.

Each option would have a price attached, and selected price would be added to the invoice, and once work order is completed, the invoice would have options totalled. Ready for the customer and dealer to confirm and approve.

Can someone start me in the right direction on getting the tables setup for this - the one that I seem to be having the most difficulty with is how to setup the options for this invoice. I pretty well have a grasp on the others. I might also need help in relating the tables to one another.

I hope this all makes sense.

Any help would be greatly appreciated.

Donm

 

[FoFa]

Lets start with your Bill To and Ship To:
Usually you would have a Dealer table and a Customer table.
Then you would have an address table with a column that ties it back to either dealer or customer, and what kind of address it might be (Ship To, Bill To, Mailing, etc.)
However this does add a little complexity to the aplication, but gives it flexibility also. In the case above any unique data elements for a dealer or a customer would be contained in that table, except for addresses which would be in the address table. Depending on the requirements you may also have contacts table (for multiple contacts at a dealer for example) or some other reoccuring data element. But if you do not require this kind of flexability, you could get away with a Bill to (delaer) and Ship to (Customer) table only.
Now for your options. I would say it would be better to see if they could be contained in as few tables as needed. From your example I would say a Options Category drop down from a lookup table and that would be Floors, Interior and Appliances. Once the Category was selected a limited number of SubCategories (dependent on the Category) would be presented, for Floors it would be Joist, Carpet, Vinyl, Ceramic (as an example), for Appliances it would be Dishwasher, Microwave, Oven (but not color). Once that was all selected any pertainent information about the subcategory would be entered (like color).

How does all this tie back? Do you have a contract number, or sales order number or something? What if the same dealer sales something to the same customer more than once?

Depending on how much maintenance you wish to do (always that downside) you could make the selection of the invoice easy, but require more maintenance, or make more data entry at invoice entry time but less maintenance to the tables.

Any way this is more a "think about" than a "you should do it this way" kind of thing.

 

[ScottGem]

What you are describing is essentially a Bill of Materials application. You might want to search here and/or on the WEB for that phrase for more info about it.

Besides your buyer and dealer info, you need at least five other tables:

tblModels
ModelID (Primary key Autonumber)
ModelName
MFgID (Foreign Key)
Rooms
Length
Width
other info about the model

tblSales
SalesID (PK Autonumber)
DateofSale
DeliveryDate
ModelID (FK)
other info specific to the sale itself

tblFeatures
FeatureID (PK Autonumber)
FeatureDescription
CategoryID (FK)

tjxFeatureModel
FeatureModelID (PK Autonumber)
FeatureID
ModelID
FeaturePrice

tblSalesFeatures
SaleFeatureID (PK Autonumber)
SalesID (FK)
FeatureID (FK)
FeatureCost

The tblModels is a list of all the models you sell with specs on them. The tblSales is a record of each individual Sale. The tblFeatures is a list of all the features that might be available on any model. The CategoryID is used to sort features by cateogry and filter your selections. The tjxFeatureModel lists the features for that particular model. This will be used to to restrict features that are only available for the model chosen. (Note: I use tjx as a prefix for Join tables, a Join table is used to manage a many to many relation. Since a multiple models can have the same feature and since a model will have several features, this is a many to many relation). Finally, tblSalesfeatures is you BOM. It lists all the features for that sale and their costs.