If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dynamically fill in a field
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, |
#2
|
|||
|
|||
Clare
See comments in-line below ... "Clare" wrote in message ... 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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Switch an image dynamically based on mail merge field - possible? | Chris Hlusak | Mailmerge | 1 | January 25th, 2005 12:11 AM |
Syntax needed to get needed reports | Frank Lueder | New Users | 15 | January 6th, 2005 09:39 AM |
Query Criteria to fill in a new field. | G. Weiler | General Discussion | 1 | June 21st, 2004 05:01 PM |
Textbox field, fill using query based on value of id field on form | Michael Miller | Using Forms | 0 | June 8th, 2004 06:31 PM |
how to use the fill function (serial no)while we are in filtered field | sreenoo dhanpal | Worksheet Functions | 1 | May 28th, 2004 06:52 PM |