C
cliff
Hi, I am new to access. I have Three tables with different data with
similiar structure.
My table1 is
sr cdate numvalue
1 1/1/2008 31
1 1/1/2008 14
2 1/2/2008 16
2 1/2/2008 18
2 1/3/2008 25
2 1/3/2008 29
table2 is
sr cdate numvalue
1 1/4/2008 34
1 1/4/2008 42
2 1/11/2008 5
2 1/11/2008 35
3 1/18/2008 41
3 1/18/2008 36
I have the following query to count number of times numvalue appeared in say
last 2 sr's or 3sr's fro table1
SELECT a.SR, B.NumValue, (count(a.Numvalue)/5) AS Numcount, max(b.cdate) AS
latest
FROM table1 AS a INNER JOIN table1 AS B ON (B.SR+2>=A.SR) AND (b.sr-a.sr<=2)
AND (b.sr<a.sr)
GROUP BY a.SR, B.NumValue
ORDER BY a.SR, (count(a.Numvalue)/5) DESC , max(b.cdate) DESC , b.numvalue;
now to get result from table2, instead of writing another query, how I can
get result from above query itself and what changes to be made. I need to
obtain result from both table very often.
canyou help me out please
thanks
cliff
similiar structure.
My table1 is
sr cdate numvalue
1 1/1/2008 31
1 1/1/2008 14
2 1/2/2008 16
2 1/2/2008 18
2 1/3/2008 25
2 1/3/2008 29
table2 is
sr cdate numvalue
1 1/4/2008 34
1 1/4/2008 42
2 1/11/2008 5
2 1/11/2008 35
3 1/18/2008 41
3 1/18/2008 36
I have the following query to count number of times numvalue appeared in say
last 2 sr's or 3sr's fro table1
SELECT a.SR, B.NumValue, (count(a.Numvalue)/5) AS Numcount, max(b.cdate) AS
latest
FROM table1 AS a INNER JOIN table1 AS B ON (B.SR+2>=A.SR) AND (b.sr-a.sr<=2)
AND (b.sr<a.sr)
GROUP BY a.SR, B.NumValue
ORDER BY a.SR, (count(a.Numvalue)/5) DESC , max(b.cdate) DESC , b.numvalue;
now to get result from table2, instead of writing another query, how I can
get result from above query itself and what changes to be made. I need to
obtain result from both table very often.
canyou help me out please
thanks
cliff