Results 1 to 3 of 3
  1. #1
    Adonai is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2020
    Posts
    1

    Question How to program a report to only display a certain amount of data?

    Hello!

    I have a report in which I want to program a certain behaviour.

    I have a textfield "Liefermenge" in the detail section of the report which contains the Quantity of Product that has to be delivered to the customer. e.g. 100 Pieces
    In the Group Footer I have a subreport which contains a textfield called "Lagermenge". This data displays how much of this product is available at a particular space in the warehouse.


    There are several spaces in the warehouse which contain the product needed to fulfill the order.

    The subreport looks something like this:
    "Lagermenge" - "Space on which it is stored in the warehouse" - "Product Name"

    Right now, every single space on which the needed product is stored is displayed in the subreport.
    I want it to only display as many spaces as are needed to fulfill the order (+ 2 additional warehouse spaces as a "buffer")

    E.g.:
    Liefermenge = 100 Pieces of ProductA

    Lagermenge 20 - Space1 - ProductA
    Lagermenge 50 - Space2 - ProductA
    Lagermenge 10 - Space3 - ProductA
    Lagermenge 20 - Space4 - ProductA <- 100 Pieces fulfilled
    Lagermenge 30 - Space5 - ProductA
    Lagermenge 15 - Space6 - ProductA <- 2 additional warehouse spaces "Buffer"

    How would I go about doing that?
    I hope the explanation of the situation is good enough.

    Thank you!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you start by showing readers your tables and relationships (relationships window) with all tables extended to show all fields. The data resides in the tables, and getting the data from the table(s) to a report will require knowledge of the source(s) of the data.
    Last edited by orange; 03-18-2020 at 07:29 AM. Reason: spelling

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I agree with Orange that seeing the tables would help (getting a small db sample would be even better). Without those I have a few questions:
    Wouldn't you want to fulfill the order from the smaller number of spaces possible? Meaning you would sort descending by the available quantity and fulfill the order from space 2, 5 and 1. I think it can be achieved by creating a custom function with loop in VBA that adds up the quantities to fulfill the order, adds two extra records for buffer and returns a comma delimited string that you would hold your space namesor IDs to be included (https://accessexperts.com/blog/2013/...ou-out-not-in/). Or the loop could write the space ids in a small temporary table that you join to the records source of the subreport to limit it.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 4
    Last Post: 03-23-2018, 01:23 PM
  2. Normalization for large amount of data
    By kvollene in forum Database Design
    Replies: 8
    Last Post: 07-01-2016, 01:18 PM
  3. Replies: 4
    Last Post: 04-09-2015, 08:59 AM
  4. Replies: 12
    Last Post: 07-18-2014, 01:22 PM
  5. Replies: 6
    Last Post: 02-20-2009, 11:50 AM

Tags for this Thread

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