Historical Stats

Ok folks,
Need your help once again.

I have 4 Tables that need to get accurate stats from...

2 are historical, 2 are current

the 2 historical are identical to the 2 present ones, just the historical is everything up to a cutoff date, the the 2 present is everything from that cutoff date on...

They contain login information, access times, install counts etc.

eg. OldTable1 = serialNo, ID, Install Date, NoOfInstalls, etc..
OldTable2 = SerailNo, Accessdate, Installid, UserID etc.
NewTable1 = serialNo, ID, Install Date, NoOfInstalls, etc..
NewTable2 = SerailNo, Accessdate, Installid, UserID etc.
I need to get total installs (gathered from both tables), total accesses after given dates etc...

the records are created at each login, so records may be in both sets of tables, (ie, if accessed after cutoff, a new record is created in the new tables...)

Any ideas?

Thanks in advanceLook up the use of the UNION operator in Books Online. It can be used to effectively combine two tables for processing, or to combine the results of two select statements.

blindman|||And if you post some DDL, DML and sample data, I bet we can help alot more..

CREATE TABEL myTable99 (col1 int, ect

INSERT INTO myTable99 (col1, ect
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect

SELECT * FROM myTable99 WHERE ect -- or whatever you've tried som far...

might be worth your time...|||This is what I am trying, but it has not been working..I get this is not permitted when the subquery follows ... (or when the subquery follows and expression)

I am trying to do this with out a new table, but a view is fine...

I am just trying to count version 4s from old, version 4s from new, then the union that combines (just as a test audit for now)

should be minimal overhead this way...

CREATE VIEW dbo.FV_Serial_Reg
AS

SELECT COUNT(SerialNumber) AS Ver4_Old,
(

SELECT COUNT(SerialNumber)
FROM FRV.dbo.WFSerialNumber
WHERE left(Version,1) = '4'
) AS Ver4_New,
(
SELECT Version4 = COUNT(SerialNumber) FROM FRV.dbo.WFSerialNumber WHERE left(Version,1) = '4'
UNION
SELECT COUNT(SerialNumber) FROM FRV.dbo.WFSerialNumber_PREV WHERE left(Version,1) = '4'
) AS Version4


FROM FRV.dbo.WFSerialNumber_Prev
WHERE left(Version,1) = '4'|||DUDE.....

ya lost me...is there a question here?

why are you doing what you're doing?

Does it work|||I mentioned the error in the previous post...

This is what I am trying, but it has not been working..

I get this is not permitted when the subquery follows ... (or when the subquery follows and expression)

I need these stats in a view so that we access them via a data access page created with MS Access.