New to Access - Trying to create a pricing sheet using materials list

[evanurb]

Hi all,

I'm fairly new to access and we are trying to implement a database to create quotes for our products. I work at a sign business so we have a large number of different materials that get added into our jobs.

So far, I created a master table that includes the following fields: Material ID, Material Name, Material Category, and Material Cost. I also created individual tables for each material category with the material names and costs.

Ideally, I want to create a form that allows users to add in various materials and the necessary quantities that would then calculate the overall cost of the sign. I am unsure of how to go about this because I tried added in combo boxes for each material category but that only allows for a singular material to be entered where in many cases we use multiple materials of the same category in our products.

We currently use two Access databases that were created by our production manager to track jobs and create work orders but it recently became my job to start improving our current databases and implement a new one that we can use to price out jobs. As of now, we are just using a simple Excel spreadsheet that lists all the materials to create quotes but implementing this into a database would allow us to track the data more efficiently as well as track inventory.

Like I said, I'm by no means an expert in Access or databases in general but I tend to pick up these kind of things fairly quickly so any help or advice would be greatly appreciated.

Thanks!

[ranman256]

no, you want 1 table for ALL Materials, with the MatType field to say what kind of material so they are all in 1 table.

[Homegrownandy]

Just to add to what ranman said,

you could link the category or "type" to another table that has the description or anything that is specific to just that category. Then in your table that lists all of the materials you would just link to it using the ID fields.

In your example you don’t seem to have IDs you have material name in each table. The idea is data should exist only in one place not multiple. You then use relationships between the data to show what you want.

I’m sure someone will link you examples about data normalisation.

[ArviLaanemets]

You also need a table where you register every job.
And a table where you register materials ant their amounts needed for every job.


Another thing to be aware of!

You have material unit cost in your table. It assumes the cost of material for a job is calculated, as sum of sum of amount of every material spent multiplied with material unit cost. OK. You did a job, and after couple of weeks weeks a price for some material does change. With all new jobs is currently all OK, but the cost of past jobs does change too. I.e. all your monthly, yearly, ... reports will be corrupted.

There a 2 ways to cope with this.
1. You save current unit price for materials in table for job materials. The downside is, unless you make some additional coding for your job materials entry form, you can save only current price read from materials table (NB! The current price at moment you are entering data!);
2. You have a materials prices table, where for every material the price history (material, price, and date from which the price was valid - until next price is estabilished at next date) is stored. You need a function, which calculates a valid price certain material at certain date from materials prices table with this setup.

[evanurb]

Thanks for the response

I have all of the materials entered in a table with another table just for material types. I just want to create a simple form that allows me to add as many different materials that I need and returns the overall cost. If possible, I would like to be able to generate a report of just the required materials for a job, the quantities, and overall cost.

[ssanfu]

Maybe you would post a pic of you relationship window?

Better yet, attach you dB.
Delete/change any sensitive data, leave a couple of quotes/jobs, do a "Compact & Repair", then zip it before attaching to a post. We can see where you are at in the development.



Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.
Do not begin object names with a number.

------------------------------------------------------------------------------
What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html

[evanurb]

Here's where I'm at right now, I know its not much but I'm not sure where to go from here.

Also, I attached a little snippet of the excel spreadsheet that I'm trying to make this database from for reference.

[ssanfu]

I would suggest you step back a little and read the links in Post #6. Then use a whiteboard, pencil & paper , crayon & cardboard, sticky notes, etc to design your tables and relationships.

I want to create a form that allows users to add in various materials and the necessary quantities that would then calculate the overall cost of the sign. I am unsure of how to go about this because I tried added in combo boxes for each material category but that only allows for a singular material to be entered where in many cases we use multiple materials of the same category in our products.

You really need to change your table structures.
I would think you need (to start with):
tblCustomers
tblOrders
tblOrderdetails
tblMaterials

Consider these 2 data models at Barry Williams' site:
http://databaseanswers.org/data_mode...erce/index.htm
http://databaseanswers.org/data_mode...ders/index.htm


The form could be a main form for the order and a sub form for the order details (the materials for the order/quote)
Remember - Do not use look FIELDS in table.


What is the difference between a Quote/Order and an Invoice?
You could have a date field for when a quote/order becomes an invoice. (don't know you system/requirements so just spit balling here)

Think about what Arvil described in Post #4 when designing your tables......