I am working on correcting this database but since it will take an almost total redesign and more time than we can afford right now, I need to modify the current one for use in in the short term. The live database has almost a hundred products so please keep that in mind when looking at my uploaded example.
Basically, I need to create a report using the 'Main Menu' form. The space alloted for the products that will be shipped is very limited with room for only 8 items on a report(remember I have almost 100 items but not all of them will be selected). The issues are that I cannot bind the product fields on the form to the report because I need to be able to report only the products that have a quantity greater than 0 on the 'Main Menu' form. Additionally, I need the report to include the applicable 'Unit Price' from the 'Products' table since this is used in subtotal and total cost calculations on the report.
I have a 'test' report that describes the locations and type of data (open it in design) that need to be reported. This may result in one page if the items with quantities>0 is 8 or less, but could be more pages if there are more than 8, so the report will need to be able to create another page of itself for each group of 8 items while keeping everything else the same.
This looks looks quite a challenge and I hope someone is up to the task. I am new to databases so I am not very successful in my attempts to figure this out myself. If this is a VB solution, that is fine with me but I am not much of a programmer.
Could you please explain how to restructure my OrderDetails table?
You will need the following fields
- this will store the productId and can be used to lookup the Product in the Products Table
OrderDate - the date of the order
ClientID - who order the product. Is the Unit field?
Order Number -the Order number used when the client submits an order
Qty - Quantity of the product
UnitPrice - the price of the product at the time of the order
Field3 - your requirement
Field4 - your requirement
there maybe other fields required by you.
Is the subform just the product name with the combo box or do I need to have other columns?
On the subform you will need to display a drop down for the product, a field to enter the quantity. The follwoing are required but will be hidden a field to store the Unit Price, a field to store the order date, a field to store the ClientID, field for Field3 and Field4.
how do I get the report to display them on different lines?
If you post a copy of your report, I will post a sample database on the forum.
I have attached the current database. I attempted to design it as you described but I know that I have missed some things since I do not fully understand how the interrelationships work.
Run the MainMenu form and fill it out. Press the Preview Report button and the report will display with some of the correct information and some that needs to be fixed. There is a description of what each field should contain for the fields I am having trouble with.
Additionally, I have only been able to figure out how to get the MainMenu entries to the report. I cannot get them all to save to the OrderDetails table.
To answer your question, each order goes to only one unit. The same unit may have another order at a later date though, so we need to see the products each unit has since they are mostly high value items. The items must be tracked and that is the purpose of the report. Each product sent to the unit must be listed and the unit will sign the report accepting the products and send it back for our records.
I am going to take a look and see if I can deconstruct what you provided to understand it better, but the report format we use requires that the products, unit price, and qty be printed in specific areas. That looks like the next challenge, especially since each report can only contain 8 products before another sheet needs to be printed.