Results 1 to 3 of 3
  1. #1
    axg275 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    17

    Double Query Help

    Hey Guys.

    I am having huge problem with trying to execute a query it just does not make a table its stuck on running the query mode and does not move. What i did was found an article on how to create a query online and modified it for my table. I wanted to see if its possible for me to write this query in the design mode as opposed to the sql code. Here is the code that I wrote that does not run to make the table:

    SELECT



    E.Wells, E.Tops_AZPU_AG,
    E.Tops_AZPU, E.EVENT_DATE,
    E.KH_Logged_,
    (SELECT SUM(E1.KH_Logged_) FROM tblEvent AS E1 WHERE E1.Tops_AZPU_AG=E.Tops_AZPU_AG AND E1.EVENT_DATE=E.EVENT_DATE) AS TOTAL_KH_Logged_,
    E.KH_Logged_/TOTAL_KH_Logged_*100 AS PCT_KH_Logged_ INTO tbl_Res_byPercent
    FROM tblEvent AS E;

    I followed this thread to create my query :&nbsp;https://stackoverflow.com/questions/62446895/calculate-percentage-of-total-by-group-in-access-2016<br><br>I was trying to figure out how to get this query made in the two steps in the design mode. I am really stuck. Can someone help me out?

    Thank you

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did you verify the SELECT will open dataset without the INTO clause?

    Did you already mark this question as solved? Is it?
    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.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    I am having huge problem with trying to execute a query it just does not make a table...

    What exactly are you trying to do?
    Insert results of query into NEW table (SELECT ... Into NewTable ...)?
    Insert results of query into EXISTING table (INSERT INTO ExistingTable SELECT ...)?
    Anything other?

    Anyway, storing aggregate values (SUM and Summary Percentage) in some table is a way for disaster! Let's assume you did this. And later you added a new row to table tblEvent, or edited an entry there, or deleted a row - i.e. did anything in this table. Your data in table where you stored those aggregate values will be off unless you clear affected part or all info (it works in both ways, you have to use the one which works faster) from this table, and fill deleted info anew (or fill the table anew). And when ttblEvent has many rows, this may take a lot of time (and this will happen after any change in tblEvent).

    This database's slowing down (plus a possibilty to get wrong aggregate values stored anyway, as nothing is 100% sure), it the reason tis in not recommended. Instead you calculate those aggregates whenever they are needed. E.g. you can create a saved query which does it, and you can anytime get them from there. Something like
    Code:
    SELECT Tops_AZPU_AG, EVENT_DATE, SUM(KH_Logged) AS KH_SUM
    FROM tblEvent
    GROUP BY Tops_AZPU_AG, EVENT_DATE

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

Similar Threads

  1. Double Query Help
    By axg275 in forum Access
    Replies: 1
    Last Post: 12-05-2022, 08:09 AM
  2. Double Value Shoes in query
    By nhkhurum in forum Queries
    Replies: 14
    Last Post: 11-16-2013, 03:55 AM
  3. how to solve this double query
    By gunterhoflack in forum Access
    Replies: 11
    Last Post: 01-28-2013, 07:58 AM
  4. double update query problem
    By tss in forum Queries
    Replies: 9
    Last Post: 11-04-2011, 08:50 AM
  5. Append query double criteria
    By Gerry in forum Queries
    Replies: 0
    Last Post: 03-23-2010, 03:55 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