Results 1 to 2 of 2

What sequence to update multiple tables from a query or form

  1. #1
    dbh is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    1

    Question What sequence to update multiple tables from a query or form

    I have a single database where a regular routine is to query outstanding amounts from a purchases table (purchases table cross referenced against purchase payments table). I have a query that shows the records required as I adjust the date range of the query.


    What I'm looking to achieve is to be able to run a process where I can identify the records in the purchases table for payment then allocate the necessary outstanding amounts into new records in the purchases table and also create a new record in the bank table with the date, reference, notes (text input) and amount automatically calculated from the query.
    I have read up on APPEND and it doesn't seem to fit my need.
    I have a form which displays the data from my query, automatically displays the subtotal required and has input fields for the data and transaction reference but can't find any support on creating SQL from a submit button (macro) as I thought I would just right the APPEND for all the tables required in SQL.
    Thanks to anyone who can point me in the right direction.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,715
    youd want a form , put in the 2 text boxes: txtStartDate, txtEndDate
    your tPurchase query, qsPurchsDteRng, would use these as criteria: select * from tPurchase where [purchDate] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

    Q2, would use
    qsPurchsDteRng and join to tPayments query that has the data needed.

    depending on your db design, you could use APPEND to write the data to a 'working' table for you to set values/etc...then another query would take this table and UPDATE the main tables when you are finished with the update.

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

Similar Threads

  1. Update Query - Multiple Tables
    By gutenberg in forum Queries
    Replies: 2
    Last Post: 04-18-2017, 09:29 AM
  2. Update Query to multiple tables
    By Gregory23 in forum Queries
    Replies: 3
    Last Post: 04-10-2017, 02:20 AM
  3. Update Query from multiple Tables
    By nkhashab in forum Queries
    Replies: 3
    Last Post: 09-02-2014, 10:44 AM
  4. Update Query for multiple tables VBA
    By WickidWe in forum Queries
    Replies: 3
    Last Post: 12-18-2013, 05:50 PM
  5. Update Multiple tables from one form
    By KenK in forum Forms
    Replies: 0
    Last Post: 10-30-2009, 08:44 PM

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
  •  
Tech Forums: Microsoft Office Forums