Results 1 to 8 of 8
  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108

    Suggestion for db design

    Hi,



    I want to design a report that will tell me what item will be running out of stock and when in sequence.

    I have a table that tells me when an item is going to be used and how much of it.

    I have another table that has the current stock of all the items.

    What I would like to design is a report grouped by month then by item, that will show me when the item will run out of stock in the correct date sequence and by how much such as this table below. I am not sure how to build this however. Later one I will group that report by vendor as well.

    Click image for larger version. 

Name:	Capture.JPG 
Views:	20 
Size:	36.3 KB 
ID:	38899

  2. #2
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    current stock quantity should not be stored, it should be calculated. You can store qty purchased, received and used, etc., but not qty on hand, that should be calculated. When you need the qty on hand, you perform the calculation of "qtyReceived-qtyUsed"=qtyOnHand.

    Dave

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Dave14867 View Post
    When you need the qty on hand, you perform the calculation of "qtyReceived-qtyUsed"=qtyOnHand.
    And to get the balance in future QtyAtDate = (QtyRecieved +QtyArrivngPlannedBeforeDate) - (QtyUsed +QtyPlannedToUseBeforeDate). When the result is negative, you'll be out of stock at this date.

    And you must not have this saved to table. You must have a report instead. User enters date into textbox, clicks button, and a report for this date is printed/displayed. Or a report with e.g. columns of stock balance for some number o weeks starting with entered date.

  5. #5
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Qty in stock is calculated in another database and stored in a table on that other database which I have linked to access. I do not have access to sufficient data and data access to calculate that stock myself, it comes from multiple stand alone systems which are mapped together, very complex and out of my league, so I must use that table which is updated live.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you (or your management) considered developing the required report on the system where all of the pertinent information is mapped together? Perhaps I'm missing key details, but developing a report piece meal where there are multiple disjoint sources of data seems somewhat fruitless. Can you give us some details to clarify your set up and intended approach?

    Good luck with your project.

  7. #7
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Quote Originally Posted by orange View Post
    Have you (or your management) considered developing the required report on the system where all of the pertinent information is mapped together? Perhaps I'm missing key details, but developing a report piece meal where there are multiple disjoint sources of data seems somewhat fruitless. Can you give us some details to clarify your set up and intended approach?

    Good luck with your project.
    This is a fortune 500 company with many very complex disjoint sources. I was lucky enough to be able to actually access these databases and I'm trying to build tools for myself to make my day to day life more efficient than the front end tools we are using.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As others have suggested, the Allen Browne article/approach is a well-used approach, so the concepts illustrated there should be considered. Also, since you are working with a "table which is updated live", the reporting accuracy of that data will be questionable (by some at least). Your extract will only be relevant until the next transaction --and that may be fine for your needs. I had a similar situation with an online system where clients/user areas needed to do some batch reporting and analysis--- the solution was to provide month-end, quarter-end and year-end "management reporting databases". This was the method to get some consistent data for a variety of recurrent reporting and analytical purposes.
    Good luck with your project.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-02-2015, 09:15 AM
  2. Looking for db design suggestion
    By Jamescdawson in forum Database Design
    Replies: 2
    Last Post: 08-14-2012, 10:37 AM
  3. Form Suggestion
    By kc1 in forum Forms
    Replies: 3
    Last Post: 12-09-2011, 03:51 AM
  4. I need suggestion in SQL
    By genesis in forum Access
    Replies: 0
    Last Post: 07-31-2009, 08:17 PM
  5. Need suggestion or help please
    By KLynch0803 in forum Programming
    Replies: 14
    Last Post: 06-11-2009, 10:21 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