Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    48

    Unanswered: Non-standard database assistance needed

    Hello all.
    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.

    Any and all help is appreciated!!!

    Andrew
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    What are Unit, Field3 and Field4 used for?

    In the layout of of your Main Menu form you have a field for each product currently 5, you will need to change the layout to fit your 100 products.

    I suggest you use a subform with a continuous layout and have a combo box for the user to select the product. This will require a change to the structure your OrderDetails table

    Report - make the detail section large enough just to display the 8 records and when the number is greater than 8 the remining records will will be displayed on the following pages.

  3. #3
    Join Date
    May 2009
    Posts
    48
    Hello Poppa Smurf.

    Field3 and Field4 are just placeholders for actual fields on the live database. When I make changes, I want to ensure everything still works as intended.

    Could you please explain how to restructure my OrderDetails table?

    Is the subform just the product name with the combo box or do I need to have other columns?

    Since the products selected are variable, how do I get the report to display them on different lines?

    I will search for some examples online but if you could provide further assistance, it would be appreciated.

    Thanks,
    Andrew

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Andrew
    Could you please explain how to restructure my OrderDetails table?
    You will need the following fields
    ProductID
    - 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.

  5. #5
    Join Date
    May 2009
    Posts
    48
    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.

    Thanks for your assistance!!!

    Andrew
    Attached Files Attached Files

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Andrew

    Attached is an updated version. I have changed the layout of your main menu form. the fields without a headings are only displayed for testing purposes but should be hidden in the "live version".

    I have changed your OrderID to Order Number as your OrderID is an autonumber. You will need to enter an order number.

    When you select a product all the other fields except the quantity are updated.

    I noticed on your report that you were grouping on Unit, do you have more than one Unit per Order?
    Attached Files Attached Files

  7. #7
    Join Date
    May 2009
    Posts
    48
    Thanks Poppa Smurf!! This looks promising!!

    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.

    Thanks again!!!!!!!!!!

    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •