how to use where clause in my query

[cliff]

I have following query to count n1 in last 2 sr's

Select a.sr, B.n1
FROM [sloto Union] AS a INNER JOIN [sloto union] AS B on (b.sr<a.sr) AND
(b.sr-a.sr<=2) AND (b.SR+2>=a.SR)
GROUP BY a.sr, B.n1
ORDER BY a.sr, b.n1


suppose I want to use where clause in above query to count n1 in say last 3
sr's or 4 sr's upto 50 sr's instead of writing changing the query every time

my data table is something like :-

sr n1
1 8
1 16
1 23
2 16
2 23
2 44
3 14
3 18
3 19
4 28
4 35
4 37
5 16
5 35
5 44

please help me

thanks

 

[Bob Barrows [MVP]]

I have following query to count n1 in last 2 sr's

Select a.sr, B.n1
FROM [sloto Union] AS a INNER JOIN [sloto union] AS B on (b.sr<a.sr)
AND (b.sr-a.sr<=2) AND (b.SR+2>=a.SR)
GROUP BY a.sr, B.n1
ORDER BY a.sr, b.n1


suppose I want to use where clause in above query to count n1 in say
last 3 sr's or 4 sr's upto 50 sr's instead of writing changing the
query every time

A WHERE clause will not help. You need to parameterize the differential,
like this:
AND (b.sr-a.sr<=[p1]) AND (b.SR+[p1]>=a.SR)

 

[cliff]

Hi Bob Barrows,
thanks for your help. I have added parameter in query . but it gives
wrong result. can guide me what is wrong in my query.

Select a.sr, B.n1

FROM [sloto Union] AS a INNER JOIN [sloto union] AS B on (b.sr<a.sr)
AND (b.sr-a.sr<=[p1]) AND (b.SR+[p1]>=a.SR)
GROUP BY a.sr, B.n1
ORDER BY a.sr, b.n1

thanks

 

[Bob Barrows [MVP]]

Sorry, I can't. I have no idea what the right results would be, or what
wrong results you are getting.

Hi Bob Barrows,
thanks for your help. I have added parameter in query . but it
gives wrong result. can guide me what is wrong in my query.

Select a.sr, B.n1

FROM [sloto Union] AS a INNER JOIN [sloto union] AS B on
(b.sr<a.sr) AND (b.sr-a.sr<=[p1]) AND (b.SR+[p1]>=a.SR)
GROUP BY a.sr, B.n1
ORDER BY a.sr, b.n1

thanks

 

[cliff]

Hi Bob Barrows,

thanks for your help. I have added parameter in query and changed my query and working perfectly.

Select a.sr, B.n1

FROM [sloto Union] AS a INNER JOIN [sloto union] AS B on (b.sr<a.sr)
AND (b.sr-a.sr<=[p1])
GROUP BY a.sr, B.n1
ORDER BY a.sr, b.n1

but if i use crosstab query on above, it gives error " The Microsoft jet
database engine does not recognise [p1] as valid field name or expression"
please guide me

 

[cliff]

Hi Bob Barrows,

thanks for your help. I have added parameter in query and changed my query and working perfectly.

Select a.sr, B.n1
FROM [sloto Union] AS a INNER JOIN [sloto union] AS B on (b.sr<a.sr)
AND (b.sr-a.sr<=[p1])
GROUP BY a.sr, B.n1
ORDER BY a.sr, b.n1

but if i use crosstab query on above, it gives error " The Microsoft jet
database engine does not recognise [p1] as valid field name or expression"
please guide me

thanks