A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dynamically fill in a field



 
 
Thread Tools Display Modes
  #1  
Old February 3rd, 2005, 11:57 AM
Clare
external usenet poster
 
Posts: n/a
Default 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  
Old February 3rd, 2005, 12:34 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

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  
Old February 3rd, 2005, 03:27 PM
Clare
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 10:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.