Results 1 to 2 of 2
  1. #1
    ojc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    1

    Parameter to create records in sub-report query

    I am creating a bill of lading in Access as a report. It pulls in sales data from an outside program and stores it in two tables: a sales order table and a sales order line item table in an one-to-many relationship where one sales order can correspond to many line items.



    So for imaginary sales order #3000, we pull the line items and fill in the lot numbers manually as parameters.

    Line item Shipped quantity Lot #
    152 250 2007,2009
    162 200 2060






    My problem is that I would like to be able to further split up the line items, i.e. so that one line item could correspond to several production lots, each with a certain quantity of product. For the example below, I would like to specify the number of lot numbers (2) for line item 152. I would first enter how many lot numbers that line item has and then enter the shipped quantity and lot number for each.

    Line item Shipped quantity Lot #
    152 125 2007
    152 125 2009
    162 200 2060








    So I am looking to design the report and related queries/tables to take into account three parameters:
    1. how many lot numbers each sales order line item will use
    2. shipped quantity per lot
    3. actual lot numbers

    The sales order data is pulled at the report level and the line item data is within a sub-report.

    Would this require VBA or is there a way to do it with purely with queries and user-entered parameters?

    Thanks in advance and sorry if I am not describing the problem well.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The Lot numbers are just strings in a text field? This won't work. Use a junction table to associate lot number with each line item.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-18-2012, 03:48 PM
  2. Replies: 1
    Last Post: 01-09-2012, 05:06 PM
  3. Parameter Query to report
    By kathi2005 in forum Reports
    Replies: 7
    Last Post: 11-02-2011, 02:00 PM
  4. Counting records of a parameter query
    By JonathanT in forum Reports
    Replies: 5
    Last Post: 10-31-2011, 04:33 AM
  5. Replies: 15
    Last Post: 08-12-2011, 09:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums