Results 1 to 3 of 3
  1. #1
    ACu is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2018
    Location
    Mississippi
    Posts
    8

    Question Capturing data from a calculated field in a query to calculate total results in a query/report

    I have a program in Access that handles reservations for our firm. I have a query/report that displays 3 fields (Tour Date, Tour Time, and Party Size). I want this query to display a field titled Available Seats. I want it to calculate the remaining seats from the field Party Size -40. 40 is the maximum seats allowed. It displays all of the info such as the date, time, and party size but it only returns from one Party Size entry. I may have up to 3 separate entries on one particular date at 10:00 AM. I want to be able to see all seats available so that if I want to fill a vacancy I'll know how many vacancies I have left. This will enable me to manage my seats more closely. I have tried several formula's but none work. Ex.; Available Seats: =Sum -40([Party Size]) If I have 25 seats filled on the first entry, then 3 on the next and 5 on the last, I'm only seeing 15. I'm not seeing the other entries. I should see a total of 7 seats remaining. I keep getting an error about amalgamated information on Tour Date or something along that line. Is there another formula that will capture that information?

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can't easily aggregate a query field in a non aggregate(totals) query, however there are a couple of ways around this.

    Best route would be to simply create a second query that sums your trips (based on trip_date I assume) and link that back to the original query. This can also be achieved using a subquery.
    The less than ideal route would be to use a DSum() but that could get slow and is cumbersome really.

    You will possibly need to do some fiddling with date criteria and grouping if your tripdate data includes a time value.
    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 ↓↓

  3. #3
    ACu is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2018
    Location
    Mississippi
    Posts
    8
    Minty,
    Thank you so much for the quick reply. I will definitely check it out.

    ACu

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

Similar Threads

  1. Replies: 6
    Last Post: 12-08-2016, 02:44 PM
  2. Replies: 5
    Last Post: 05-13-2016, 11:00 AM
  3. Replies: 1
    Last Post: 10-12-2013, 04:06 PM
  4. Calculated field on results in the same query
    By becka11 in forum Queries
    Replies: 1
    Last Post: 05-07-2013, 05:11 PM
  5. Replies: 1
    Last Post: 03-19-2012, 03:49 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
  •  
Other Forums: Microsoft Office Forums