Results 1 to 12 of 12
  1. #1
    DAOpsMatt is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    11

    Updating one table from a count query from another table

    Hello all,



    I am new to Access and reading and trying as many things as I can find because i love it.

    I have at table that has Trips and Departures in it. The second table I have has Guest Information in it. They are linked together by the Trip and Departure. Each trips has 4 Sub Trips (A, B, C, D) and each guest is on one of those Sub Trips.

    I have created a query that counts the total number of guests on each sub trip by a specific trip an departure. For example, Trip (Hawaii) Departure (2) has 8 guests on part A, 10 guests on part B, 4 guests on part C and 5 guests on part D.

    Now I need to put those total back in the Trip Departure Table, so how do I update that table from the query?

    Hope that makes sense,

    Matt

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    access cannot sum and update in the same query. (sum or count or Max, etc)

    youd need 2 queries:
    1. to write the sums to a temp table, then
    2. update from the temp table

    key, sum

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Saving aggregate data is usually a bad idea. Calculate when needed.
    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.

  4. #4
    DAOpsMatt is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    11
    Thank you very much for your help,

    I am having a little problem understanding this one still. I have the the first query set up as a Count Query that displays the count. I don't understand how to write a second update query to pull form this. I have 4 rows of data as they are grouped by the data type that is displayed.

  5. #5
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    A query that links the two tables by trip and departure and Count([GuestName]) would do it, right?

    Don't store this result in the main table. Otherwise, if you change any guest information, the totals will be wrong. The query will always be right.

    The main table has data. A guest count is not data, it is a calculation.

  6. #6
    DAOpsMatt is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    11
    Quote Originally Posted by June7 View Post
    Saving aggregate data is usually a bad idea. Calculate when needed.
    So how would you do it without aggregate data?

  7. #7
    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
    I recommend you post a copy of your database with a few records and highlight in plain English what you want to do with that specific data --- the input and the desired output and format.
    As others have said - storing calculated values is not a good strategy (usually). Better to work with queries and calculate if and when needed.

    Good luck.
    Last edited by orange; 02-09-2019 at 06:33 AM. Reason: spelling

  8. #8
    DAOpsMatt is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    11
    Ok, Sorry for the delay, been a really busy winter.

    Here is what I am looking for, not sure how to post a database, so Going to try and explain it.

    Table A - guest data - Guest are entered in this table and each guest has a letter code assigned to them K, L, M, or S and a date. 1/01/2019 or 2/02/2019, etc.
    Table B - Date table - Other information in this table, but also a section to tell us, how many of each letter there on are on that date. How many Ks, Ls, Ms, Ss.

    So I need to count the name of each from Table A and put that number in Table B field.

    I currently have a qry written to count the number of each on the specific date. Then I have to manual enter that number into the other table. don't want to do the manual work.

    Hope that helps,

  9. #9
    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
    You can get the count by group(letter code) via query whenever needed.
    This would be a typical way to do this in Access(or any RDBMS ).
    Storing the count in a separate table is not a standard approach.

    Can you tell us why you need these counts in a table?

    Can you overview the WHAT this whole database is about and to be used for? Not HOW you think it should be st up in MS Access.
    Good luck.

  10. #10
    DAOpsMatt is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    11
    The Database is being used to keep track of individual events, Each date is an event. On that event day, we have to book extras for the guests, these letters let us know who is doing what extras so we know how many to book and who is going. This data is being displayed on a form we created as well as total number of guests for the date. So adding all the codes together for that date.

    The form is like a Dashboard of information/ data for that given date.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    There are ways to display aggregate data on forms and reports without saving to table.

    Cannot run an UPDATE action with an aggregate query. Just won't work. Period.
    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.

  12. #12
    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
    As said previously
    post a copy of your database with a few records
    Draft based on your posts
    Click image for larger version. 

Name:	GuestEventExtras.PNG 
Views:	5 
Size:	10.2 KB 
ID:	37368
    Last edited by orange; 02-09-2019 at 08:47 AM.

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

Similar Threads

  1. Replies: 2
    Last Post: 03-09-2017, 04:50 PM
  2. Replies: 2
    Last Post: 11-11-2016, 09:07 AM
  3. Replies: 3
    Last Post: 06-24-2015, 12:57 PM
  4. Replies: 6
    Last Post: 05-10-2012, 08:20 PM
  5. Updating a Table from a second Query.
    By israellawndale in forum Access
    Replies: 4
    Last Post: 09-22-2011, 02:51 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
  •  
Other Forums: Microsoft Office Forums