Perhaps the problem is that month is a reserved word and therefore should be in
brackets. Perhaps HourToLiter (Function?) does not exist, is being improperly
called, or ???). A possibility is that you have some null values in Reading
that are causing the HourToLiter function to malfunction.
SELECT A.[Customer ID], A.[Year], A.[Month],
MLProcessed: IIF( A.Unit='L', A.[Reading] - B.[Reading], HourToLiter(A.[Reading]-B.[Reading]))
FROM [Monthly Reading] AS A INNER JOIN [Monthly Reading] AS B ON
(A.[Year]*12 + A.[Month] -1 = B.[Year]*12 + B.[Month])
AND (A.[Customer ID] = B.[Customer ID])
ORDER BY A.[Customer ID], a.[Month];
Try breaking this down to trouble shoot
Remove the entire MLProcessed line
If the query works then introduce
MLProcessed: IIF( A.Unit='L', "Good", "Bad")
If that works then
MLProcessed: IIF( A.Unit='L', A.[Reading] - B.[Reading],"BAD")
If that works then
MLProcessed: IIF( A.Unit='L', "Good", HourToLiter(A.[Reading]-B.[Reading]))
If that works then the problem exists elsewhere in the query
Minnow said:
Hi John,
Can you tell me what the syntax error with the following SQL statement:
SELECT A.[Customer ID], A.[Year], A.[Month], MLProcessed: iif( A.Unit='L',
A.[Reading] - B.[Reading], HourToLiter(A.[Reading]-B.[Reading]))
FROM [Monthly Reading] AS A INNER JOIN [Monthly Reading] AS B ON
(A.[Year]*12 + A.Month -1 = B.[Year]*12 + B.Month) AND (A.[Customer ID] =
B.[Customer ID])
ORDER BY A.[Customer ID], a.Month;
John Spencer (MVP) said:
One method would use a non-equi join.
SELECT A.[Customer Number], A.[Year], A.[Month], A.[Reading] - B.[Reading]
FROM [Monthly Reading] as A INNER JOIN [Monthly Reading] as B
I have a table called Monthly Reading, it has following fields, Customer
Number, Year, Month and Reading.
Now I want to know the amount processed in each month for each customer
which is the difference between current month and the month prior.
Is this table structure good to acheive this goal?
How to acheive this goal?
I am grateful of any suggestion.
