Help with creating an estimating Database

[Guest]

I am trying to create an estimation database for my roofing comapny. I have a
table designated to materials with prices description etc. and a table
designated to the names of buildings/projects. I would like to be able to
create a form that allows me to choose a building then select materials and
quantities to be added to that building/project. Finally, having records of
all materials used on the building and a total price of all materials
selected according to quantity and unit price would be the goal. Ideas
suggestions help is very much appreciated.

 

[Guest]

Hi, Swansie.

In designing your database, start with the *things* and their attributes,
then consider their relationships. Be sure to assign a primary key to all
tables. A many-to-many relationship requires an "intermediate" table which
is in a one-to-many relationship with each of the others. For example, your
application has a one-to-many relationship between Company and Product, since
more than one vendor can supply a given product, and a single vendor can
supply many products.

For this application, you will need at least the following tables:

Company (For Vendors, Customers, Owners, Etc.)
CompanyID AutoNumber (Primary Key)
CompanyName Text
Street1 Text
Street2 Text
Phone Text
Fax Text
etc.

Contacts (Many-to-one with Company)
ContactID AutoNumber (PK)
CompanyID Number
ContactFName Text
ContactLName Text
Email Text
Phone Text
Extn Text
....etc.

Product
ProductID AutoNumber (PK)
Product Text
StandardCost Currency

ProductVendors
ProductVendorID AutoNumber (PK)
ProductID Number (Foreign Key to Product)
CompanyID Number (Foreign Key to Company)

Projects
ProjectID AutoNumber (PK)
ProjectName Text
Location, etc.

ProjectMaterials (Many-to-one with Projects)
ProjectMaterialID AutoNumber (PK)
ProjectID Number (Foreign Key to Projects)
MaterialID Number (Foreign Key to Product)
EstCost Currency
ActualCost Currency

Your main form could be based on Projects or on a query between it and
Company so that you could display the owner's information (phone, etc.). An
embedded continuous subform based on ProjectMaterials (linked by the
ProjectID) would record each item and insert an estimated price as is stored
in Product.

Pricing is an exception to the general rule about duplicating data between
tables. For example, you generally would record only the customer ID, and
not the name in an Orders table. In this case, however, since pricing is
likely to change over time, you need to record the ProductID and its stored
price as of today. Since Access will not do this automatically, you will
need to include the price in your Product combo box, and assign it to your
EstCost field explicitly. Assuming it's the 2nd column, the code would be:

Me![EstCost] = Me!YourComboBox.Column(1)

You can use this same form to do your estimate, and record actual costs.
Summary fields in the subform footer can display the total of each of these:

=Sum([EstCost])
=Sum([ActualCost])

Hope that gets you started.
Sprinks