 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / July 2007Customer Product List - Quantity
Is there a way to make a form display customer info and a list of all products available with price and an empty quantity field. I have 3 tables, customer, products and order. I am trying to create a check out form that will sub total prices and quantity, any info you could provide will be greatly appreciated Put the customer in a main form and products in a datasheet view subform. Link Master and Child on the CustomerID. The CustomerID should be a primary key field so as to set a one-to-many relationship to Orders table. Also have a a one-to-many relationship from product table, ProductID, to Orders table.  SignatureKARL DEWEY Build a little - Test a little > Is there a way to make a form display customer info and a list of all > products available with price and an empty quantity field. > > I have 3 tables, customer, products and order. > I am trying to create a check out form that will sub total prices and > quantity, any info you could provide will be greatly appreciated Thanks Karl > Put the customer in a main form and products in a datasheet view subform. > Link Master and Child on the CustomerID. The CustomerID should be a primary [quoted text clipped - 8 lines] > > I am trying to create a check out form that will sub total prices and > > quantity, any info you could provide will be greatly appreciated To start you need a different design of your tables: TblCustomer CustomerID CustomerName <<Other customer contact fields>> TblProduct ProductID ProductName ProductPrice <<Other product descriptive fields if needed>> TblOrder OrderID CustomerID OrderDate <<Other order fields needed by the database>> TblOrderDetail OrderDetailID OrderID ProductID Quantity Price You need a form/subform where the main form is based on a query named QryFrmOrder and the subform is based on a query named QrySFrmOrderDetail. QryFrmOrder needs to include TblCustomer and TblOrder with the appropriate fields in the query. QrySFrmOrderDetail needs to be based on TblOrderDetail. Besides the appropriate fields from TblOrderDetail, QrySFrmOrderDetail needs a calculated field that looks like: ExtendedPrice:[Quantity]*[Price] To enter the Product (ProductID) in the subform, use a combobox with TblProduct as the rowsource property. Set the Bound Column property = 1, Column count = 3 and Column Width = 0;1.5";0. Put the following code in the AfterUpdate event of the combobox: Me!Price = Me!NameOfYourComboBox.Column(2) To get total order, add a text box to the footer of the subform and put the following expression in the control source property of the textbox: =Sum([Quantity] * [Price]) PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com > Is there a way to make a form display customer info and a list of all > products available with price and an empty quantity field. > > I have 3 tables, customer, products and order. > I am trying to create a check out form that will sub total prices and > quantity, any info you could provide will be greatly appreciated Thanks Steve, the 4th table and the 2 queries did the trick, I was also able to look at the Northwind db for insite. > To start you need a different design of your tables: > TblCustomer [quoted text clipped - 48 lines] > > I am trying to create a check out form that will sub total prices and > > quantity, any info you could provide will be greatly appreciated >Is there a way to make a form display customer info and a list of all >products available with price and an empty quantity field. > >I have 3 tables, customer, products and order. >I am trying to create a check out form that will sub total prices and >quantity, any info you could provide will be greatly appreciated You need a fourth table: OrderDetail, with fields for OrderID, ProductID and Quantity. Take a look at the Northwind sample database. It does exactly what you're asking. John W. Vinson [MVP] Thanks for pointing me in the right direction John, The Northwing db gave me the insite that I needed. > >Is there a way to make a form display customer info and a list of all > >products available with price and an empty quantity field. [quoted text clipped - 10 lines] > > John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.