Results 1 to 6 of 6
  1. #1
    Pretorian is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    4

    Report Don't Show all stock and place

    Hello
    I'm trying to create a data base to my club were we recieved things for charity like food, clothes, furniture and other stuff
    So I've created something basic to control the stock we have, were and the movements when it goes out.
    It happends that on the report If I have just entry de products they don't list on the report.
    on this example just shows bananas but I also have Orange and Chocolat but just entry no exit on the product.

    And when I chekout a product it takes the product from all stores I have.

    What I want to do is for example:

    IN

    10 BANANA TO STORE1
    6 BANANA TO STORE2
    4 BANANA TO STORE3


    OUT 11 BANANA were

    5 from STRORE1
    2 from STORE2
    4 from STORE3

    The Report shows in the end
    5 BANANA STORE1


    4 BANANA STORE2

    Any help on this?

    Thank you
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Query conStock should not have cndAgrupadaSaida join to both tblPRODUTOS and cnsAgrupaEntrada. However, changing that will not solve issue.

    How do you know a record is for an entry or exit? If Destino field is blank, the record is entry?

    Consider this query as report RecordSource:

    SELECT tblPRODUTOS.IDPRODUTO, tblPRODUTOS.NomeProduto, tblMOVIMENTOS.DataMovimento, tblMOVIMENTOS.LocalMovimento, tblMOVIMENTOS.Destino, IIf([Destino] Is Null,[QTDMovimento],Null) AS Entry, IIf(Not [Destino] Is Null,[QTDMovimento],Null) AS Exit
    FROM tblPRODUTOS LEFT JOIN tblMOVIMENTOS ON tblPRODUTOS.IDPRODUTO = tblMOVIMENTOS.IDProduto;

    Use report Sorting & Grouping with aggregate calcs. This will allow display of detail data as well as summary calcs.

    Calculate balance in textbox: =[Entry]-Nz([Exit],0)

    Users don't need to see ID fields.

    Review http://allenbrowne.com/AppInventory.html
    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.

  3. #3
    Pretorian is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    4
    How do you know a record is for an entry or exit?
    For the entry I use form
    frmProdutoEntrada1 and for exit frmProdutoSaida2 and TipoMovimento says what it is; 1 for entry 2 for exit.

    The ID's and
    TipoMovimento is only there for control after finishing I take it out..

    If Destino field is blank, the record is entry?

    Destino only exists on the exit of a product and its only a note to know to were the product was delivered.

    If I use cnsAgrupaEntrada and cndAgrupadaSaida to create the total balance..


    something with the grouped consult cnsAgrupaEntrada and cndAgrupadaSaida

    Balance =

    IF on (cnsAgrupaEntradaLocal, the IDPRODUTO & LocalMovimento) its the same on ( cndAgrupadaSaidaLocal, o IDPRODUTO & LocalMovimento)

    do (QTen - Qsai)

    Else... shows the value on cnsAgrupaEntradaLocal

    I don't know if I have exaplained well..


  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Did you try suggested query and report design?
    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.

  5. #5
    Pretorian is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    4
    Quote Originally Posted by June7 View Post
    Did you try suggested query and report design?
    At the beginning I didn't realise how to use the Grouping with aggregate calcs.

    But after some videos on youtube and experiments I think I have done it.
    Let me finish better then I show the result.

    You also asked about
    How do you know a record is for an entry or exit? If Destino field is blank, the record is entry?

    If the Source is In or Out I have defined in the forms
    frmProdutoSaida2 and frmProdutoEntrada1
    where they put on tblMOVIMENTOS the TipoMovimento where 1 is IN and 2 is OUT.

    Destino is only to know were the products where delivered like, Church, Firefighters etc...

    Thanks for the help.

  6. #6
    Pretorian is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    4
    Quote Originally Posted by June7 View Post
    Did you try suggested query and report design?
    Here Is what I have done..
    Still lot work to go.. but the main goal was achieved.
    Thank you for the tip..
    Attached Files Attached Files

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

Similar Threads

  1. Any way to show Zero in Place of Null in a query
    By deepakg27 in forum Queries
    Replies: 21
    Last Post: 06-24-2018, 02:00 AM
  2. Replies: 4
    Last Post: 12-16-2016, 09:57 AM
  3. Replies: 9
    Last Post: 01-12-2015, 10:41 AM
  4. Replies: 11
    Last Post: 09-12-2013, 04:56 AM
  5. Replies: 1
    Last Post: 05-21-2009, 08:13 AM

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