Results 1 to 9 of 9
  1. #1
    Anti6 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    5

    Generate stock Report with data between dates or oldest.

    Hi



    I am relatively new to the coding world, I have created a few programs in access for my current company and thus far everything has been working fine. I recently stumbled onto a issue which I have not been able to find an answer to even with scouring pages, this is my first time posting any question on any forum.

    I have a stock management database set up.
    The idea is very easy in that the stock data is logged into one table and the changes to the quantity are logged into a different table along with the dates of the changes.
    Now I need to set up a report to get the correct data onto a summarized report that is based on dates.
    I previously set up a report that generates all movement items between 2 dates, groups them by stock code and sorts the dates from oldest to newest, this however generates a 62 page report due to the amount of movements per stock code.
    The only thing the report requires is the following: Stock code, "last stock quantity by date", price, description, supplier.
    My issue is getting the "last stock quantity by date field" to work.
    i.e. if you enter the 31/09/2017 as the date it should give you the last movement balance for that item on or before the date specified.

    Please help, I am at a dead end as to how to do this.
    I am sure I can somehow figure this out but my time on this project is running out.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Get the record source of the report, a final query, working prior to creating the report. How the data is collected depends entirely on how the data is stored on tables. A totals query will give you a MAX date prior to 31/09/2017 for an item, with that you can link those two fields to the table to get the movement balance for that item.

  3. #3
    Anti6 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    5
    Hi
    Thank you for the reply.

    This is what I got from playing around with your input, but it still gives a list of more than one order date, I think I might be making it more complicated than it should be.

    SELECT DISTINCTROW tblIndex.stockCode, tblIndex.unitPrice, Query1.balance, [tblIndex].[unitPrice]*[Query1].[balance] AS stockValue, tblIndex.description, tblIndex.supplier, tblIndex.bin, Max(Query1.orderDate) AS Expr1
    FROM (tblIndex INNER JOIN tblStockData ON tblIndex.stockCode = tblStockData.stockCode) INNER JOIN Query1 ON (tblStockData.balance = Query1.balance) AND (tblStockData.orderDate = Query1.orderDate) AND (tblIndex.stockCode = Query1.stockCode)
    GROUP BY tblIndex.stockCode, tblIndex.unitPrice, Query1.balance, [tblIndex].[unitPrice]*[Query1].[balance], tblIndex.description, tblIndex.supplier, tblIndex.bin
    ORDER BY tblIndex.stockCode;

    Query1 is

    SELECT tblStockData.balance, tblStockData.stockCode, tblStockData.orderDate
    FROM tblStockData
    WHERE (((tblStockData.orderDate)<=[End Date YYYY/MM/DD]));

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    MAX of the date is missing. Query 1 must be a totals query, group by item with max date, with date less than a given date.

  5. #5
    Anti6 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    5
    Hi

    Thanks for the reply, this is assuming that I am just pulling two items with query1?
    Because if I have more than two items in query1 then I do not get only the max value for orderDate.
    I need to get in this case, the max orderDate (<=[Enter Date]) of each stockCode, and the ID number for each of these transactions.

    If I do not get the ID number then it gives the date but only as a date it is useless as it creates duplicates in the second query in any case due to there being more than one stock transaction per stock code on any one date.

  6. #6
    Anti6 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    5
    I think I know what the issue is, its caused by the orderDate field only saving date values, not Date and Time values which means that it is not unique enough for the second query.

    So according to my results it works fine in one way but still shows all entries for the stockcode for the date of the first query which I only want the most recent one.

    As far as I know dates are saved with the time as a double value, but it is not handling it as such in this case.
    Might be very wrong in my assumption though.
    Any issues around this?

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    if you enter the 31/09/2017 as the date it should give you the last movement balance for that item on or before the date specified.
    Query1 will have two fields, "item" (GROUP BY) and "last movement" date (MAX- also this field repeated with a WHERE). This will give you one record per item with the latest date of movement.

    For query2, bring in query1 and the tables required. Join item and date to the stock table and get the "last movement balance". Also other fields as required.

    Ignore time and ID.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If you have multiple entries per item per date, that is when you will get duplicates. Let me know if this is the case and what you want to do about it.

  9. #9
    Anti6 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    5
    Hi

    My Query 1 and 2 are set up like that at the moment and still giving a duplicate on items that had movements on the same day, I tried to set it to a "General Date" from a "Short Date" format but it doesn't do anything to the values.
    My time is up, I recommended that they print a stock list on the day they require it.
    Thanks for all your help thus far.

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

Similar Threads

  1. Advice - getting data filtered to generate report
    By ss188 in forum Database Design
    Replies: 3
    Last Post: 03-24-2017, 08:21 AM
  2. Replies: 0
    Last Post: 07-18-2016, 10:41 AM
  3. Replies: 2
    Last Post: 09-16-2014, 02:25 PM
  4. Replies: 1
    Last Post: 05-13-2013, 05:01 AM
  5. Replies: 1
    Last Post: 01-02-2013, 01:30 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