Sequence of Query Calculation

[Valhalla]

Hello,


I am interested in developing a Microsoft Access 2003 form which displays in the form a graph of revenue over time for several different categories of sales selected from the same form. The first attachment, which in the interest of accessibility uses Northwind Trader data, illustrates my objective.

The form that I have developed to date has error(s) in that the graph always displays the total revenue of all the sales categories and does not reflect the category selected in the combobox in the top left hand corner. The list of sales does update correctly to the category selected in the combobox. I believe that in order for Access to calculate this graph the sales which are associated with the selected category need to be identified before the cumulative sum is calculated. I have read recently that DoCmd.OpenQuery and DoEvents commands can be used to calculate queries in a particular sequence.

Further I am using a make table query to find the sales details from four Northwind Traders order details tables. Access crashes on my machine when I tried to run a cumulative sum directly from the four Northwind tables. Access still crashed trying to calculate a cumulative sum of 35 sales without the make table query but easily calculates the cumulative sum of 2100 sales using the make table query. The second attachment represents the current make table query which I had originally hoped could be a select query.

How should the sequence of query calculation be specified; I am not sure if I am calling the query correctly from the form or which event should have a procedure;

As an indication of my Access competency I stumbled upon these cumulative sum and nested combo box methods some time ago. I have written a limited amount of VBA for event procedures but not a macro or a module. I would greatly appreciate advice on how to force Access to process queries in a particular sequence. In my extension of the Northwind Traders database the fsales form in the first attachment receives data from the qqtshipped cumulative sum query below. I thought the qtsales query in the cumulative sum query below might be necessary to select all the sales associated with a category in the table written by the make table query in the second attachment.

SQL statement for query named “qqtshipped”:

SELECT tbl_Alias.ShippedDate, tbl_Alias.CategoryName, (SELECT Sum([qtsales].[ProductSales]) AS vlu FROM [qtsales] WHERE ((([qtsales].[ShippedDate])<=[tbl_Alias].[ShippedDate])) AS vlu
FROM qtsales AS tbl_Alias;

Today I rewrote the cumulative sum formulae in this database without needing a make table query by using the DSum function. Although the query calculates data correctly the corresponding form returns “Syntax error (missing operator) in query expression” at the moment.

Thanks,
Ross

[orange]

You do not have a matching closing bracket for
......tbl_Alias.CategoryName, (SELECT Sum([qtsales].....

[Valhalla]

I have checked the result of cumulative sum calculation and it is working correctly with the following code. As this is my first post a smiley face was substituted for code;

SELECT tbl_Alias.ShippedDate, tbl_Alias.CategoryName, (SELECT Sum([qtsales].[ProductSales]) AS vlu FROM [qtsales] WHERE ((([qtsales].[ShippedDate])<=[tbl_Alias].[ShippedDate])) semi-colon close bracket AS vlu
FROM qtsales AS tbl_Alias;