Results 1 to 9 of 9

Update a table from another table

  1. #1
    Bill Walsh is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    4

    Smile Update a table from another table

    I have two tables that contain data. The first table contains summary records. The second table will contain detail records.

    When I setup a scoring form, Table 1 has the daily values. For instance Score, Award and Birdies. Before I update Table 1 summary records, I would like to add the records to Table 2. Everything is based on the Paid field in Table 1 equal to "y".

    Table 1 fields:
    Paid
    Lastname
    Initial
    Score
    Money
    Birdies


    Membernumber
    and a lot of YTD and Past stats on rounds played, money, average score, total birdies etc.)

    Table 2 fields: (Obviously this table would have many records for the same player.)
    Date Played
    Name
    Score
    Money
    Birdies
    Membernumber

    I hope my question makes sense.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,707
    use an append query to add records to the persons subtable,
    use update query to alter values that exist.

  3. #3
    JoeM is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,832
    Typically, you should not "store" anything at the table level which can easily be calculated (that is what queries are for!).
    It actually goes against the principles of relational databases, and can undermine the data integrity and the dynamic nature of databases.

    So "Summary/Total" records should just be calculated in queries from the existing data in your one table.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  4. #4
    Bill Walsh is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    4
    Quote Originally Posted by ranman256 View Post
    use an append query to add records to the persons subtable,
    use update query to alter values that exist.

    Thank you.


    Using the term summary was a bad choice of words. Cumulative would have been better.

  5. #5
    JoeM is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,832
    Using the term summary was a bad choice of words. Cumulative would have been better.
    Same thing as what I said. If it is something that can be calculated from the data currently in the table, there is usually not need to hard-code it in another table. Just calculate it "on-the-fly".
    There is usually no reason to have to write it back to a table, as anything you would use a Table for you can also use a Query for.
    Is there a reason why you think it needs to be written back to a Table instead of just being in a Query?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  6. #6
    Bill Walsh is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    4
    I average the last 10 scores of each player and that becomes his or hers new quota. I also use last 20 scores to decide whther or not they are improving or not and reflect that on their scorecard.

  7. #7
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,360
    You can do most of that in a couple of queries, as JoeM said you wouldn't normally store this data, as any edit you make would need you to re run the calculations again.
    As a hacker myself, if you uploaded some sample data and the required outputs I could probably knock something up for you?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    Join Date
    Apr 2017
    Posts
    984
    Quote Originally Posted by Bill Walsh View Post
    I average the last 10 scores of each player and that becomes his or hers new quota. I also use last 20 scores to decide whther or not they are improving or not and reflect that on their scorecard.
    You create an User Defined Function (UDF) with table name, table field, aggregate function code, number of rows, date field name, player ID field name, and player ID as parameters (In case you don't plan to use the function elsewhere, you can hardcode some of them into function instead).
    You can use the function then in form control, query or in VBA code to calculate the aggregate value of any given number of last records for player, e.g.:
    Code:
    SELECT a.PlayerD, YourUDF("YourTable","Score","AVG","DateField", 10,"PlayerID",3) AS AggregateValue FROM (SELCT DISTINCT PlayerID FROM YourTable) AS a
    UDF uses a query constructed with passed parameters taken into account - like:
    Code:
    SELECT b2.PlayerID, (SELET AVG(b1.Score) FROM (SELECT TOP NumberOfRows b0.Score FROM YourTable 0 WHERE b0.PlayerID = b2.PlayeID ORDER BY b0.DateField DESC) As b1) FROM (SELCT DISTINCT PlayerID FROM YourTable) AS b2

  9. #9
    Bill Walsh is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    4
    Quote Originally Posted by ranman256 View Post
    use an append query to add records to the persons subtable,
    use update query to alter values that exist.
    The append worked great! Thanks........

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

Similar Threads

  1. Replies: 4
    Last Post: 11-27-2014, 10:20 AM
  2. Replies: 1
    Last Post: 02-08-2012, 04:50 PM
  3. Replies: 2
    Last Post: 12-22-2010, 01:46 PM
  4. Replies: 0
    Last Post: 11-30-2010, 12:23 PM
  5. Replies: 3
    Last Post: 01-21-2010, 08:10 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
  •  
Tech Forums: Microsoft Office Forums