Hi John, thanks for your reply.
I wish to generate a line chart that summarises the progress of a project by
monitoring the number of tools completed per week. Two of the columns,
'targetDate' & 'actualDate' in 'tblSummary' that user inputs, indicate the
targetDate of completion and actualDate of completion respectively. After
which, the dates by user input will get processed and generates two values
'targetWeek' and 'actualWeek'. With these two values, 'targetWeek' &
'actualWeek' obtained, I will then query the number of tools completed each
week as I thought I need to do up a query in order to create such a line
chart based on that query.
In 'tblSummary', a tool is considered completed if one of its attributes,
'Percent' turns 100.
The following SELECT queries are what make up 'qryCumulative'.
'qryTargetPerWeek': This query returns the targeted number of tools
completed per week.
SQL is as follows:
SELECT [tblSummary].[targetWeek], Count(*) AS [targetNoOfTools]
FROM [SELECT * FROM [tblSummary] WHERE ((([tblSummary].Percent)=100))]. AS
[%$##@_Alias]
GROUP BY [tblSummary].[targetWeek];
An example of the results:
targetWeek [targetNoOfTools]
1 3
2 2
3 4
The same I did for actualWeek.
qryActualPerWeek: This query returns the actual number of tools completed
per week.
SQL is as follows:
SELECT [tblSummary].[actualWeek], Count(*) AS [actualNoOfTools]
FROM [SELECT * FROM [tblSummary] WHERE ((([tblSummary].Percent)=100))]. AS
[%$##@_Alias]
GROUP BY [tblSummary].[actualWeek];
An example of the results:
actualWeek actualNoOfTools
1 3
3 1
4 2
qryTargetCumulativeTotal: This query returns the targeted number of tools
completed on a cumulative basis. It basically does a running sum.
SQL:
SELECT T1.[targetWeek], (SELECT Sum([qryTargetPerWeek].[No Of Tools]) AS
targetTotal
FROM [qryTargetPerWeek]
WHERE [qryTargetPerWeek].[targetWeek] <= T1.[targetWeek]) AS targetTotal
FROM [targetWeek] AS T1;
An example of the results:
targetWeek targetTotal
1 3
2 5
3 9
Same applies for the actualWeek.
qryActualCumulativeTotal: This query returns the actual number of tools
completed on a cumulative basis.
SQL:
SELECT T1.[actualWeek], (SELECT Sum([qryActualPerWeek].[No Of Tools]) AS
actualTotal
FROM [qryActualPerWeek]
WHERE [qryActualPerWeek].[actualWeek] <= T1.[actualWeek]) AS actualTotal
FROM [actualWeek] AS T1;
An example of the results:
actualWeek actualTotal
1 3
3 4
4 6
Having qryTargetCumulativeTotal & qryActualCumulativeTotal, I did UNION and
JOINs and have the following query returned.
qryCumulative
Week targetTotal actualTotal
1 3 3
2 5 nothing shown
3 9 4
4 nothing shown 6
However, I wish to obtain this set of query results.
Week targetTotal actualTotal
1 3 3
2 5 3
3 9 4
4 9 6
In your previous post you mentioned that such a query is never updateable,
does that mean that I can't obtain such a query set? Is it possible to code
using VBA or to create a stored procedure? I can't just perform SELECT
queries from two separate tables, 'qrytargetCumulativeTotal' &
'qryActualCumulativeTotal' as it involves UNION-ing their results together,
thus explains for the missing values in 'qryCumulative'.
As mentioned above, I'm trying to do up a line chart based on the above
'qryCumulative' but due to the "missing values", I can't generate a
continuous line chart. Instead, the lines have breaks in between. If this is
not a feasible method, is there any alternative? Many many thanks for your
help.
John Spencer said:
Your problem is not the update query. It appears that qryCumulative is
not updatable. I suggest you post it for diagnosis.
My guess is that qryCumulative is totaling (sum, group by, etc.) and
that type of query is never updatable.
What problem are you trying to solve. If you are just trying to SHOW
data in a report, then you need to build a select query based on
qryCumulative or revised qryCumulative itself.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
I have the following query, qryCumulative
Week targetTotal actualTotal
1 3 3
2 5 nothing shown
3 9 4
4 nothing shown 6
An example of the query results that I wish to attain is this:
Week targetTotal actualTotal
1 3 3
2 5 3
3 9 4
4 9 6
I've tried creating an UPDATE query but Access shows me an error message
indicating that 'Operation must use an updateable query.'
The UPDATE query is as follows:
UPDATE qryCumulative SET qryCumulative.[targetTotal] =
DLookUp("[targetTotal)]","qryCumulative","[Week] = [Week]-1"),
qryCumulative.[actualTotal)] =
DLookUp("[actualTotal]","qryCumulative","[Week] = [Week]-1")
WHERE (((qryCumulative.[targetTotal]) Is Null)) OR
(((qryCumulativeTest.[actualTotal]) Is Null));
How can I insert/update the values from the previous row to the null cells
so that I can generate a continuous line chart and not have breaks in between
due to the null values?
