Results 1 to 5 of 5
  1. #1
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44

    How to append a copy of a record to another table based on a certain field's value

    I'm creating a database that will help users track supplies that they've ordered. Right now, they track the supplies they've ordered via a Purchases Table, and when supplies are received, they have to manually add those supplies to their "Inventory" table. Then, when they are expending items from their inventory, they have to go to remove the item from their inventory and log the expenditure in an Expenditure Table. All changes made to the tables are recorded via forms.

    Ultimately, how I would like to have it is when a user sets the "status" field of a record in their Purchases Table to "received", a copy of certain fields from that record are appended to the Inventory Table. And then when a user sets an item's "status" field in their Inventory Table to "expended", a copy of the record would be moved to the Expenditure Table.



    I'm not really well versed in relationships, but my assumption is that what I would like to do requires knowledge of them.

    I'm just looking for a starting point here but any help is greatly appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I think use of this "Inventory" table is a design flaw. Inventory balance should be calculated from transactions. 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
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    Thanks for the reply. I looked at the article and I'm not sure if it makes sense for what I'm trying to do, but then again I may not know any better.

    To add a little more context, users won't really be using the DB as a point-of-sale type process. It is more for helping them track purchases that they make for their organizations. These folks are essentially office administrators who carry out purchases for other departments.

    So there aren't really any transactions that the user will have to keep up with in real time. The DB is more so there for them to make entries as they are able and will be for their use only. No customers will be involved.

  4. #4
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    I've just read the link again and I think it's making more sense now. Not sure how to get from point A to point B but this is a good starting point. Thank you for your help!

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Doesn't matter if it's POS or not. You said you have records for purchases and records for expending. Those are still transactions. The difference of their totals is balance on hand. The concept can apply to many entities. Leave earned less leave used. Points earned less points used. Stuff comes in and stuff goes out. Good luck!
    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.

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

Similar Threads

  1. Replies: 2
    Last Post: 09-14-2017, 05:09 PM
  2. Replies: 9
    Last Post: 07-11-2017, 07:51 AM
  3. Replies: 2
    Last Post: 02-23-2016, 04:36 PM
  4. Replies: 4
    Last Post: 01-20-2015, 12:55 PM
  5. copy and append a record
    By erikl1 in forum Access
    Replies: 4
    Last Post: 03-02-2012, 03:47 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