Aggregate Query Results not Showing

Hi, just been trying to sort out a quick query in Data glass Access, i have written a simple query that should sum the value of a field based on a grouping for example

SELECT Table1.Group1, Table1.Group2, Table2.Group1, Sum(Table1.myVals)
FROM Table1 INNER JOIN Table2 ON Table1.LookupField = Table2.LookupFiled
GROUP BY Table1.Group1, Table1.Group2, Table2.Group1

Firstly the results of the query are very slow it takes around 40 seconds and table1 contains only 440 rows

however the main issue is that if i use data glass to amend / add data to table1 for the field myVals when i run the query the Sum of myVals is not shown in the query results. If I then quit the app and then open it again and re-run the query the results are then shown as expected ? why is this ? this would indicate that the query is not being run against the underlying table or that this is somehow cached ?
1 person has
this problem
+1
Reply
  • Walter (Customer Support Representative) April 22, 2012 21:55
    Regarding the speed of the query, are all the query and grouping fields indexed? That will be the most important component of ensuring the query is fast enough. Other than that, the SQL statement looks pretty simple, so it shouldn't be too slow (note, though, that the speed will be affected by the number of rows in both Table1 and Table2).

    Regarding the changes not showing up, that is very odd. Does this affect any table that you edit/ammend, or just some of them? Also, are you sure you are re-executing the query after you make the change? And lastly, if instead of quitting the app, you just back all the way out to the root screen and then open the database again, is that sufficient to make the changes show up?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly