none
Count data from a table and update the result in another table

    Question

  • i have a query it correctly displays output as i require. i want to update/store the output query to a table named ustate. my query is as under

    SELECT Auth.nit, UC+UL+UC AS Aut, (select count(NO) from Pers where nit=Auth.nit and rOrd>4) AS Present, (SELECT COUNT(no) FROM pers WHERE nitFrom = auth.nit) AS DIn, (SELECT COUNT(no) FROM pers WHERE nitTo = auth.nit) AS DOut, Present-Dout+Din AS Held
    FROM Auth
    WHERE (((Auth.Type)='tata'))
    ORDER BY Auth.nit;


    I am a System Administrator at Vadodara

    Saturday, September 14, 2013 9:56 AM

Answers

  • More important is how many records in [Auth] that have [Type] = "tata"?

    However, if you only want to refresh this set of derived/calculated data only once a day by overnight job, I think it is better that you have the destination Table structure set up first and on the overnight job, you can run a DELETE Query to delete the old set and then the APPEND Query to add the set of up-to-date derived/calculated rows.

    You can set up a Macro in Access to run the DELETE Query and then the APPEND Query, both of which use the OpenQuery Macro action.  You also want to use the SetWarnings Macro option to temporarily disable the warnings/confirmations on deletion/insertion.  Thus the Macro actions you need in sequence

    SetWarnings (False/Off)

    OpenQuery (DELETE Query)

    OpenQuery (APPEND Query)

    SetWarnings (True/On)

    You can then create the Scheduled Task in the OS to open Access / database and run the Macro with the /X argument in the command-line in your OS Scheduled Task.

     


    Van Dinh

    Monday, September 16, 2013 3:19 AM

All replies

  • Design the query. Then change its type to "Make Table query".

    -Tom. Microsoft Access MVP

    Saturday, September 14, 2013 10:22 AM
  • Done as directed and a new table was created by that. how can i update it frequently now

    I am a System Administrator at Vadodara

    Saturday, September 14, 2013 1:23 PM
  • You have to use precise language. "Update" means something specific in Access. What you probably meant is you want to add data to it. That is done with an Append query.


    -Tom. Microsoft Access MVP

    Saturday, September 14, 2013 4:59 PM
  • when i run my query which i had already displayed above takes only 10 seconds to query and show data but when i add 'INTO ustate' (ustate is a table)  it takes 3-4 minutes to create a table with the data which is queried  

    I am a System Administrator at Vadodara


    • Edited by Baiju EP Sunday, September 15, 2013 2:42 AM
    Sunday, September 15, 2013 2:40 AM
  • Do you really want to store derived/calculated data?

    I would have though this type of derived/calculated values would be best derived/calculated whenever the values are needed since they are more accurate/up-to-date and consistent with current dataset.  As soon derived values are stored as Field values, there is a chance that it becomes out-of-date and inconsistent when the component values are changed.

     

      


    Van Dinh

    Sunday, September 15, 2013 5:42 AM
  • i planned to make a macro and add it to system startup. presently i am using this code in my asp.net web it takes 3-5 minutes to load the calculated page. so that i decided to calculate and store it . the data compilation done in evening and on next day system startup the calculated data will be seen  

    I am a System Administrator at Vadodara

    Sunday, September 15, 2013 8:21 AM
  • >>presently i am using this code in my asp.net web it takes 3-5 minutes to load the calculated page.<<

    ... and you think retrieving data is the problem?

    How many records do you have in Table [Auth] in total approx.? How many for each Auth Type approx.? Is the Field [Type] indexed?

    How many records do you have in Table [Pers] in total approx.? How many for each nit value approx.? Which Fields are indexed in this Table?

     


    Van Dinh

    Sunday, September 15, 2013 10:52 PM
  • AUTH IS HAVING 2330

    FOUR CALCULATION FOR EACH 2330

    PERS IS HAVING 3378, EACH PERSONS HAVING FIVE+FIVE+FIVE COUNTING  COMPARED WITH AUTH(2330)

     


    I am a System Administrator at Vadodara

    Monday, September 16, 2013 1:09 AM
  • More important is how many records in [Auth] that have [Type] = "tata"?

    However, if you only want to refresh this set of derived/calculated data only once a day by overnight job, I think it is better that you have the destination Table structure set up first and on the overnight job, you can run a DELETE Query to delete the old set and then the APPEND Query to add the set of up-to-date derived/calculated rows.

    You can set up a Macro in Access to run the DELETE Query and then the APPEND Query, both of which use the OpenQuery Macro action.  You also want to use the SetWarnings Macro option to temporarily disable the warnings/confirmations on deletion/insertion.  Thus the Macro actions you need in sequence

    SetWarnings (False/Off)

    OpenQuery (DELETE Query)

    OpenQuery (APPEND Query)

    SetWarnings (True/On)

    You can then create the Scheduled Task in the OS to open Access / database and run the Macro with the /X argument in the command-line in your OS Scheduled Task.

     


    Van Dinh

    Monday, September 16, 2013 3:19 AM