Newbie to Access

[dosneau]

Need some help please, new to access. Question, need to make a database for customer quotes. THe following is what i need to do:


- Customer Name
- Quote ID / Number
- Calculations for the quote, about 8 calculations before you can get the cost

My question is I can set the primary key to be Quote ID since that is the unique identifier but what do I relationship it with?

I have one table and it has the following titles
- Quote ID
- Customer Name
- Length
- Width
- Waste Percentage
- Cost
- Final Cost

Thanks..

[Datagopherdan]

You can set a one to many relationship between the customer and quote table. You can create a customer id field in the customers table and make it an "Auto-ID" field and set it as your primary key. In you quotes table, you would addd a CustomerID field and set it to numeric data type. You would then set a one to many relationship between customers and quotes on that field.

Also, any calculated fields should not be in a table. Those should be done in query.

[dosneau]

Thanks Dan,
One question, so I have made a query very long one..it has 8 calculations, to come up with a final price. However when I make a form from my original table where all the entries will be made. How do i set it where, the customer enters all the values on the form and then the calculation spits out, i.e. the final calculation on that one query...

THanks in advance...

[Datagopherdan]

You would use the query that you perform the calculations in as your form's record source.

Thanks Dan,
One question, so I have made a query very long one..it has 8 calculations, to come up with a final price. However when I make a form from my original table where all the entries will be made. How do i set it where, the customer enters all the values on the form and then the calculation spits out, i.e. the final calculation on that one query...

THanks in advance...

[dosneau]

Thanks Dan,
That is exactly what i did, I only forgot to add the table so my form looked a little weird...but it works great...now I need to add a box where the user can pick type of material. However here is my dilemma.

I added a new table called Material. In this table are 4 columns,
- 1. Material Type
- 2. Material Adhesive
- 3. Material Cost
- 4. Material Sheet

I then made a new column in my input table and labeled it Material and made it a lookup column.

Now the dillema:
When I use form wizard, everything is perfect, except the material box. It shows only the first column from the Material Table, as opposed to all 4 columns. I tried a combo box to pull data from Material table, but again only the first column shows..

Any help would be greatly appreciated.

Thanks!

[dosneau]

In case I forgot to mention...The user picks the material type and the adeshive and cost and sheet correlate to that type...

[Datagopherdan]

You need a junction table to tie the materials and customer together. You would have a customerid field to tie to customers and a materialid field to tie to the materials table. You could then have a setup where customers is your main form and in your sub-form, you would use the junction table and tie it by customer id. For the materialid field in the subform, you would make it a combo box with 2 columns and set materials table as the record source using the id and material name. Set the column widths to something like 0,1, so the id is not shown only the name.

In case I forgot to mention...The user picks the material type and the adeshive and cost and sheet correlate to that type...

[dosneau]

Thank you so much! Dan, I know this is just a volunteer service you do..but I greatly appreciate it!...thanks again, I will definitely try to do what you said...after I break it down and really understand it...THANKYOU!!!!tons...