Results 1 to 8 of 8
  1. #1
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23

    Post How to update a cell data according to a new record?

    Hi, I am new to Access so I'm not sure if my question is gonna be with an obvious answer. I have created two tables - "Delivery Note" and "Inventory". I am wondering if there is a way to, short of making a really complicated macro(?), update data in "Inventory" after making a new record in "Delivery Note". Like, when I make a new record (ID=3) down here in "Delivery Note" with a data of Water Barrier equals to 10, then the Current Quantity of Water Barrier down in the "Inventory" table will decrease by 10 automatically. Is that possible?


    In Excel, I know how to use equations connect the value updates between cells. But here in Access, I'm not sure how to type in equations when the column is already defined as Short Text/Number, etc. since it seems we gotta make the column Calculated Field to type equations. Plus, formatting a new record seems different than formatting an existing cell. And let's for now ignore the fact that the Parts Name in the 2 pages do not fit except for Water Barrier. I will have more complicated relationships defined soon. Thanks for the help!
    Attached Thumbnails Attached Thumbnails access_1.png  

  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
    It's possible but not advisable. Before you commit to this path, 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
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    First of all the design of your delivery table is wrong. You do not want to redesign your database when ever a new part is introduced. Two tables are needed here, Delivery (DeliveryID, reference, date, project) and DeliveredPart (DeliveryID, PartID, number). Remember we are talking Access now, not Excel.

    Secondly, you do not need a inventory table (just a parts table). Inventory can be derived from all parts received (!) minus delivered.
    Groeten,

    Peter

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Take care when not using an inventory table:
    - after 3 years the calculations can become very slow
    - you cannot archive old delivery/receival records
    - inventory does not only change on delivery or receival of parts, but also when parts get broken/lost/obsolete ...
    - it's easier to have a stock table when you need to make a stock correction on inventory day

    I've seen this solution in software training examples, but all working warehouse/storage applications in the real world I've seen work with a separate inventory table.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by NoellaG View Post
    TI've seen this solution in software training examples, but all working warehouse/storage applications in the real world I've seen work with a separate inventory table.
    Those inventory tables are inventories at certain date, e.g. at end of year. The stock balance is calculated whenever it is needed, as last inventory value + all incomings after the date of this inventory - all outgoings after the date of this inventory.

    OP is calculating the running inventory - what is a big NO!

  6. #6
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23
    Thanks all for the advice! I will sure think about it! And Peter, sorry Im not following what you said too well. Perhaps my mindset is still quite "Excel-ly". Would you mind going a bit deeper in that?

    What is the advantage of splitting the Delivery table into those 2 tables you mentioned? Did you mean that the AutoNumber of DeliverPart table should be Parts ID and its Deliver ID is where it links to the Deliver table? And then the Inventory can be a query/report generated with the help of a third table Parts instead of being a table itself? Thanks so much!

  7. #7
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23
    Hi Peter, did you mean we should keep track of, for example, the stock of wheels, plates, fiber... instead of car A, car B? And then Deliver table should hold info of how many cars i delivered and DeliverPart table should hold info of how many wheels, plates, fiber... I delivered?

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Ian120 View Post
    ... did you mean we should keep track of, for example, the stock of wheels, plates, fiber... instead of car A, car B? And then Deliver table should hold info of how many cars i delivered and DeliverPart table should hold info of how many wheels, plates, fiber... I delivered?
    You need deliveries table (but better name it as orders table, where you store all info about this delivery/order as whole - like reference number, delivery date, project number, etc. Like tblOrders: OrderID, ReferenceNo, ProjectNo, OrderDate, ...;
    You need delivery/order details/rows table, where you store the info about all articles (cars, wheels, plates, etc.) needed for certain delivery/order, and quantities of them. Like tblOrderRows: OrderRowID, OrderID, ArticleID, ArticleQty, ...;
    You need a table where you register all various articles (cars, various parts, etc.) you have, or used for any delivery/order. Like tblArticles: ArticleID, ArticleType, ProducerID, ArticleName, [ArticlePrice], ... (you can include the articles sale prices here in case they remain fixed, otherwise you must have a separate ArticlePrices table, or you register the price together with some other info - e.g in order rows table);
    You need a table to register all movements of all articles (purchases, sales, using articles for orders, returning unused articles to stock, writing articles off, etc.). Like tblStockTransactions: TransactionID, TransactionDate, TransactionType, ArticleID, ArticleQty, ... (TransactionType determines the way, this transaction affects stock balance for this article (positive, or negative[, or neutral] effect);
    In case various car parts aren't simply delivered together with car(s), but there is some additional work (like assembly) done, you need some additional tables to register all this properly too.

    You can create a saved query, which returns the current balance of any article in stock, calculated from tblStockTransactions. When registering an order, this query can be used to check the available quantity of any article in stock.

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

Similar Threads

  1. Import Excel data cell by cell into an Access table.
    By russmann2000 in forum Import/Export Data
    Replies: 3
    Last Post: 03-21-2018, 07:18 PM
  2. Clear Cell Range Based on Text in Another Cell
    By Oxygen Potassium in forum Access
    Replies: 3
    Last Post: 08-20-2017, 08:12 PM
  3. Replies: 10
    Last Post: 06-26-2017, 11:35 AM
  4. Replies: 12
    Last Post: 07-07-2015, 08:12 PM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 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