Dynamically fill in a field

[Clare]

I have a database with 3 tables -

CustomerID
Name...

Product ID
Product
Price...

QuotationID
CustomerID
ProductID

I want to be able to pick a number of items from the product list and the
price is then dynamically filled in so that I can then print off a quotation
letter. I currently have a lookup field which shows the product and price.
How do I design the quotation table so I can choose the product in one field
and it then fills in the price in another field in the quotation table so
that I can show the prices and the total cost on the quotation letter. Also
I can only have one ProductID field so does this mean I need a junction table
between product and quotation?

Thanks,

 

[Jeff Boyce]

Clare

See comments in-line below ...

I have a database with 3 tables -

CustomerID
Name...

Product ID
Product
Price...

QuotationID
CustomerID
ProductID

I want to be able to pick a number of items from the product list and the
price is then dynamically filled in

You've posted in tablesdbdesign, so the implication is that you are trying
to "fill in" values in a table. You've already designed your tables (per
your description above) to resolve the m:m relationship, so why would you
want to copy (fill in) values from one table (Product) to another
(Quotation)?

so that I can then print off a quotation
letter.

You can prepare a quote (letter) without copying (duplicating) the field
info -- use a query to join the tables.

I currently have a lookup field which shows the product and price.

If you are still describing a table (and not a form), using the "lookup"
data type field can cause you severe headaches when you try to query the
data in this field. Check this newsgroup on the topic of lookup fields.
You are better off using a combo box in a form. In fact, you are better off
using a form to display data (and enter it), and leave your tables for
storing the data.

How do I design the quotation table so I can choose the product in one field
and it then fills in the price in another field in the quotation table so
that I can show the prices and the total cost on the quotation letter.

Again, don't! Use the relational database (Access) relationally. Create a
join that gives you price info for a product, where said product is part of
a quote, where said quote is for a specific customer. (and by the way, if
you don't have date/time info on that quote, each customer apparently only
gets one quote!)

Also
I can only have one ProductID field so does this mean I need a junction table
between product and quotation?

You only have one ProductID field in your Quotation table, just as you only
have one CustomerID. To show more than one product for a customer, use more
than one row! A "total" quote is all the product IDs (rows) for the same
customer ID (see previous comment).

Thanks,

Good luck!

Jeff Boyce
<Access MVP>

 

[Clare]

Jeff,

I don't really know what I was thinking, you're right and I don't want to be
in table design, I want to be doing this through forms based on queries. I've
downloaded the northwind database now though so I'm copying that step by step.

Cheers,

Clare