Create a Quotation Form

[annliang]

The current database has the following tables (fields):-

1. Salesman List (Salesman, Supervisor)
2. Customer List (Customer No, Customer Name, Salesman)
3. Product List (Product Name, Product No, Price)
4. Quotation list (Quotation No, Salesman, Customer No, Customer Name, Product Name, Product No, Price) - The data of this table is input by a quotation form.

I'm trying to create a quotation form (by using the quotation list) for others to input. My quesitons are:-

1. In the quotation form, there's a Supervisor field (which is not in the quotation list table). I want the value in the Supervisor field to show up according to the Salesman selected in the form. So, I'm using the sub-form method (Main form:Quotation form; Subform: Salesman List form). Am I doing correct?

2. While inputing data into the form, one should select Salesman first and then Customer Name (by doing so, only the customers of which the Salesman is responsible are shown in the list for selection - this will need to link up the customer list table). How can I do this?

3. For Product No and Product Name in the form, we assume one customer may ask for quotations of many items. When I just copy and paste the Product No and Product Name several times in order to create several fields, I find that when I input one field (let's say Product No), all other Product No fields will show up the same product no as well. They are not treated separately. How should I fix this problem?

4. For the product price, I want the price in the Product List will show up according to the product I choose in the form. But then, it will allow me to amend as needed.

5. Finally, I want to place the save and submission buttons at the end of the form. Once it's done, the form will be sent to the Supervisor. The supervior will review and approve if correct. The Salesman can look up the database and be able to see the status of the quotation as:new/ waiting for approval/ approved by.... by opening the submitted quotation form (the status will be shown on the top of the form).

Thanks in advance for the assistance. It would help me a LOT!!!!!

 

[Mihail]

Your tables should be:

tblSalemens
ID_Salesman - AutoNumber (PK)
SalesmanName
SupervisorName

tblCustomers
ID_Customer - AutoNumber (PK)
CustomerNo
CustomerName
ID_Salesman - Number (FK)

tblProducts
ID_Product - AutoNumber (PK)
ProductNo
ProductName
ProductPrice

tblQuotations
ID_Quatation - AutoNumber (PK)
QuotationNo
ID_Customer - Number (FK)
ID_Product - Number (FK)

If CustomerNo, ProductNo and QuotationNo are internal codes then should remain in the tables but, if you use this only as IDs then remove it; will make your life easier.

 

[spikepl]

Actually, you should have

tblQuotations
ID_Quotation - AutoNumber (PK)
QuotationNo
ID_Customer - Number (FK)

tblQuotationDetails
ID_QuotationDetail - AutoNumber (PK)
ID_Quotation - Number (FK)
ID_Product - Number (FK)
ProductPrice
ProductQuantity?

 

[annliang]

Thank you so much, spikepl!

I have followed your table design. But how can that solve my questions? Would be much appreciated if you can furnish me with more details!

 

[annliang]

For point 2, I can partly achieve. I have linked up the quotation details table with the salesman list table and customer list table. So, whenever I input the salesman info in the form, the list of customer no will show up according to the salesman selected.

But my problem is, when I input the salesman info, the filtered list of customer will only be shown up after I exit the form and re-enter it. How can I solve this?