using records from different table

[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

 

[tina]

first of all, i'd say redesign your tables to follow relational design
principles.

I have Three tables with different data with
similiar structure.

multiple tables with a "similar" structure (in this case, replace "similar"
with "identical") often indicates that you are storing data in tables names
or field names, which breaks the rules of relational design. since the two
tables in your example have identical fieldnames, my guess is you're storing
data in the table names. instead, use one table and add a field to define
the "group" or "category" that each record belongs to.

once you do that, you only need one query to get the information you need.
just set a parameter on the "category" field in the query, and choose the
category you want each time you run the query.

hth