Results 1 to 13 of 13
  1. #1
    paddington is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2017
    Posts
    11

    Query To Subtract Stock Which Is Sold


    Hi, Please forgive me as I'm a novice with Access. However, I've got a database where I'm trying to subtract the stock field in a 'Product Table' based on the quantity entered for that item in the 'Form'. I've tried a query but keep getting an error with it. Also, I would like the stock to subtract for all records relating to that item that are entered.

    Also, I'm trying to ensure that records 1 and 2 on the form have data from the drop down box, product ID etc saved but it comes blank every time I reopen the form. The other thing I would like the form to do is go to a new record which would be 3 in this instance when the form is reopened every time.

    Any help would be greatly appreciated.

    Please see attached. Thank you, Paddington.
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Review http://allenbrowne.com/AppInventory.html

    Options for new record

    1. set the form DataEntry property to Yes

    2. use code to open the form in DataEntry mode
    Docmd.OpenForm "form name", , , , acFormAdd

    3. use code to move to new record row
    Private Sub Form_Load()
    DoCmd.GoToRecord acDataForm, "form name", acNewRec
    End Sub
    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
    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
    In addition to June's comments, here are a few observations to consider.

    You have ProductID as the Primary key of both tables.
    Each table should have a specific purpose and a unique Primary Key.

    In a typical Inventory system you would have the following tables or similar:

    Customer
    Order
    OrderDetail/LineItem
    Product/Item
    and often include Supplier and Purchases you make from the Supplier(s).
    with rules along these lines

    A Customer can make 1 or Many Orders
    An Order relates to a specific Customer
    An Order may contain 1 or Many LineItems/Products.
    An Item/Product may be referred to on 0,1 or Many Orders

    Also, with Inventory/Stock control you typically deal with Transactions.
    All incoming Supplies/Products/Items are added to your Stock as positive transactions.
    All outgoing Supplies/Products/Items are subtracted from stock as negative transactions.

    Transactions, counts are usually done via queries against Orders and Purchase from Suppliers.
    And, as you will notice in the Allen Browne article June referenced, from time to time you do a physical stock taking/Inventory count to ensure you know the physical inventory. This reduces the affect of spoilage, theft, loss, breakage...etc. The physical inventory shows what you really have.

  4. #4
    paddington is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2017
    Posts
    11

    Query to Calculate Stock

    Hi June7, Thank you so much for your tips with going to a new record. I was able to get this working well. Thank you.

    Hi Orange, You're completely correct and a better structured database is required. I did have one in mind as this is a snippet of a larger database. I'm more trying to get the format of queries and how they work. Despite having a broader database, I'm extremely grateful that you described how a database can be structured and will definitely implement the tips you pointed out as your way is more logical and organised than mine. Thank you.

    I read Allen Browne's article and see what you mean. OK, so I need to calculate stock when I need to do so. I've created a database and what I'm trying to do is a DSUM function then. I think this is the best way. I'm basically trying to sum the 'quantity' of a product ordered and minus this from the 'stock' of a product. Then I'd like to display the results in the 'hand' field.

    A database is attached. Any help with how I can structure this would be appreciated. Thank you, Paddington.
    Attached Files Attached Files

  5. #5
    paddington is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2017
    Posts
    11
    Hi, I'm sorry if my database and explanations are unclear as I'm really a novice at this. However, does anybody have any suggestions with this query or is it confusing? Is 'DSum' the best function to use for this purpose?

    Just looking for a query which will minus the total quantity of a particular product from the total stock of a particular product and display this in a certain field.

    Thank you, Paddington

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    DSum is one approach but domain aggregate functions can perform slowly and if you want to filter table records the same filter criteria must be repeated in the DSum(). Use DSum() on a form if you want to display real-time data as records are entered. But for a report output, a better approach would use aggregate (GROUP BY) queries.

    First, you need a table of all products.

    Build an aggregate query that sums inventory grouping by product.

    Build an aggregate query that sums orders grouping by product.

    Build a query that joins the aggregate queries to the Products table.
    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.

  7. #7
    paddington is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2017
    Posts
    11

    Query To Subtract Stock Which Is Sold

    Hi June7, Thank you for your help and advice. I've created a 'Products' Table but have hit a few obstacles. These are the problems:

    1.I've noticed my form isn't recording the data in the table properly. The Primary Key isn't working when attached to the 'Orders' table either as it says 'can't be null'.

    2. I created two aggregate queries but they're not summing properly as a result of the above.

    3. In relation to this,
    "Build a query that joins the aggregate queries to the Products table."
    I'm not sure how to accomplish this.

    I've attached what I tried.

    Thank you for your help and I'm sorry as I'm really an amateur when it comes to this. Any further help would be great. Thank you, Paddington
    Attached Files Attached Files

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    ProductID should not be a primary key in table Inventory.

    In table Inventory the ProductID is saved into ProductID field, in Orders ProductID is saved into Product field.

    Do not save Product description into Orders and Inventory, only ProductID.

    Might change field names so they aren't identical.

    Advise not to use spaces in names. Orders table has Product ID - with space.

    The aggregate queries are summing properly. Now build another query that uses the aggregate queries and joins to Products table on fields containing product ID. Use query designer. Construct calculated field in this query that subtracts the Orders sum from Inventory sum.

    This is all basic Access functionality. Have you completed an introductory tutorial?
    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.

  9. #9
    paddington is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2017
    Posts
    11

    Query To Subtract Stock Which Is Sold

    Hi June7, Thank you for your response. In relation to the tutorials, I've done a few and even emulated step by step processes. However, I struggle to get my database to work. Do you have any suggestions for tutorials? I know it's basic but I struggle with it even though I've been working on it four hours today.

    I tried a 'Test' query and while I could list the products, it lists products twice instead of summing them together. For example for Apples, there will be two entries when there should be one consolidated entry for Apples.

    In respect to the forms, I've applied some code from a tutorial I learned. However, the problem I'm having is that it doesn't save the information stored in previous records accurately on the form and when I go to a new record, it prepopulates the combo box field when I would prefer it blank. E.G. New record has last record selected for Product on form and previous records have products with incorrect product ids.

    I'm really sorry about this but any further help would be great. I've attached a database which is in a zip file. Thank you, Paddington.
    Attached Files Attached Files

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No need to include Products table in Stockinventory query.

    In the Test query, each of the aggregate queries must join to the Products table. Do not use the Inventory and Orders tables.

    Bring the queries into the query design same as would for tables. Set the links between ProductID PK/FK fields and choose "Include all records from Products ..." join type.

    Then expression to calculate Hand: [SumOfStock]-[SumOfQuantity].

    Do not do GROUP BY in the Test query.
    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.

  11. #11
    paddington is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2017
    Posts
    11
    Hi June7, Thank you so much for your excellent explanation. I got the query working and I'm very appreciative of your patience and instructions.

    In relation to the calculated field, is it possible to have '0' as the default value for calculated fields rather than have them blank? Lets just say both query fields equal 0 then Hand equals 0 rather than blank?

    Also, with my form, the Product and ProductID are not saved for previous records like Name for example. Is this possible? I'm trying to save the previous records on the form.

    I did manage to blank out the fields on a new record using the 'current' event but by doing that I may have made the answer to the question above impossible.

    I've attached a database. Thank you again for all of your assistance. Paddington
    Attached Files Attached Files

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Ooops, I should have thought of possibility of Null quantity (I assume cannot be product sold if no product inventory). So why is there a negative? Just bad example data?

    [SumOfStock]-Nz([SumOfQuantity],0)

    If you want to allow each order to have multiple products, you need another table - OrderDetails. This will be a dependent 'child' table. Likewise for inventory transactions. If each acquisition invoice can have multiple products, need Invoice and InvoiceDetails tables. Take another look at the model in the Allen Browne link. Or the Northwind database template he references. That db might even be adaptable for your needs.
    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.

  13. #13
    paddington is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2017
    Posts
    11

    Query To Subtract Stock Which Is Sold

    Hi June7, Thank you for your response and I'm sorry I'm late getting back to you. Thank you for the syntax regarding the '0' value and you're correct as the negative number wasn't the best example data to use.

    As Orange detailed earlier, I think I need to sit down and restructure all of my tables by mapping them out before diving in. I'll take a look at the Northwind Database as well to see if I can model off that. However, for now the query aspect is resolved.

    I'd just like to thank you and Orange for all of your help and teaching me how to utilise Access. Your patience and extensive responses have been greatly appreciated and I feel much more confident in utilising databases now.

    Thank you for all of your help. Paddington

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

Similar Threads

  1. Replies: 4
    Last Post: 12-16-2016, 09:57 AM
  2. Replies: 3
    Last Post: 02-26-2013, 05:07 AM
  3. Replies: 3
    Last Post: 03-28-2012, 06:00 PM
  4. How to do subtract in Query
    By NoOoN in forum Queries
    Replies: 6
    Last Post: 04-07-2011, 01:15 PM
  5. Subtract Rows in Query Results
    By Sengenbe in forum Queries
    Replies: 5
    Last Post: 02-08-2010, 06:05 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