Results 1 to 5 of 5
  1. #1
    Fish218 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    68

    Can one query run multiple calculations using data from within that query...

    Do I have to create a new seperate query everytime I want to calculate something? Why doesn't Access have a "query break" in query design where you can insert a hard break and continue a calculations based on the query in the previous section? Am I going to end up with 30,000 queries by the time I'm done calculating all the thing I need?

  2. #2
    mike02 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    245
    please give more information with what your Query is doing, How you have it set up. etc. I need more info to help you

  3. #3
    Fish218 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    68
    I have two tables I'm querying. tblCollections and tbl IndividualFish. I need to calculate the geometric mean "catch-per-unit-effort" (# of fish caught per km of net standardized to one night soak time) of clipped and unclipped fish. I have multiple calculations:

    query1 = Count number of clipped and unclipped fish for a specific assessment in a specific year grouped by collection. This one is written and functions well.
    query2 = Sum the number of clipped and unclipped fish per assessment. This one is written and functions well.

    Queries 3-7 not written...basis of my question
    query3 = Calculate CPUE based on query 2.
    query4 = calculate beta
    query5 = calculate CPN
    query6*7 = geometric means calculations

    Is there an easier way to do this?

  4. #4
    mike02 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    245
    so each calculation needs the other to work, (thats what im understanding). Now I have done it like you have planned, and i have also combined it into one query. The problem with one query is it will slow down, (what i found). Now you could use one query to set up the more complex equations. Meaning one query has the CPUE Beta, while the other has CPN and geometric mean. Im having a tough time explaining this, but hopefully that helps.

  5. #5
    jameslarsen is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Location
    Los Angeles, CA
    Posts
    20
    The answer to your question will vary depending on each type of calculation. For example, certain numbers can be calculated at the same time using the same query, such as if you needed a Min, Max, and Sum for a given data set, you could get all those with one query. However, some will need their own query, because they need a special group by function before they can be calculated. Also, you are correct, each time you need a calculation based on a previous calculation, you will need another query (You could use nested queries, but that can get very complex, and hard to keep track of, especially if you need to make changes).

    I would suggest using stored queries, and joining them together. For example, you could create a query called qry_Count_by_Year_Collection_Assessment, then another called qry_Sum_by_Assessment. You could then write a third query to calculate CPUE and join it to qry_Sum_by_Assessment. That would recalculate those numbers on the fly, and do your additional calculations at the same time.

    One important thing to note those is that Access is a limited database application, and not meant to do the heavy lifting of an Oracle or SQL Server product. If you start getting too complex with your calculations or your dataset starts getting too large, you will find that the queries will take an excessive amount of time to run, because they have to do so many calculations every time. If it starts getting too cumbersome, you could consider creating a macro or VBA function to run the queries in succession, and to save the results into temp tables (which each subsequent query would link to, instead of linking directly to the saved queries).

    Unfortunately the solution will depend heavily on what you need to calculate, so I can't give you a generic solution that will work for any data. Hopefully this provides you with a direction to consider when developing your solution though. Hope this helps!

    James A. Larsen
    Database Architect, Data Analyst, and Business Intelligence Specialist
    james.larsen42@gmail.com

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

Similar Threads

  1. Conditional calculations in a query?
    By Datech in forum Queries
    Replies: 3
    Last Post: 06-17-2012, 09:39 PM
  2. Form calculations in query
    By bkirsch in forum Queries
    Replies: 5
    Last Post: 02-24-2012, 04:45 PM
  3. Parameter Query Calculations
    By mrk7891 in forum Queries
    Replies: 2
    Last Post: 11-04-2011, 04:33 PM
  4. Calculations in Query
    By jdhaldane in forum Queries
    Replies: 5
    Last Post: 12-10-2010, 05:57 AM
  5. MSACCESS Query with calculations?
    By Masterfinn in forum Queries
    Replies: 10
    Last Post: 02-24-2010, 10:51 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