How to get the historic data from a linked table?

[Guest]

Dear All,

I have a main table which stores the most recent info of a record with
following details:

Unique_Ref_Num|Status|Dept
1 | 6 | 1

and a second table called history which records changes in the main table

HistoryID | Status | DateStamp
1 | 1 | #22/10/2007 09:00#
1 | 2 | #22/10/2007 09:01#
2| 1 | #22/10/2007 09:05#
2 | 2 | #22/10/2007 09:06#
1 | 2 | #22/10/2007 11:00#
2 | 3 | #22/10/2007 15:00#
1 | 3 | #22/10/2007 16:00#
2 | 2| #22/10/2007 16:10#


Where Status 1 = Open, 2 = Allocated and 3 = Closed.
----------------------------------------------------------------------------------------------
I want to get the count of number of queries which are not closed
(outstanding) at any point in time.

Example: (with a time parameter)
Input | Result
22/10/2007 17:00 | 1
22/10/2007 16:05 | 0
22/10/2007 14:00 | 2

I want to achieve this with just 1 query (not by using one query within the
other) b,coz I want to further use this query from Excel VBA (write through
Excel VBA and not store the query within Access)

Any help will be greatly appreciated

 

[Michel Walsh]

You can do it in ONE query, as you asked, with something like:


SELECT DateStamp, (SELECT COUNT(*)
FROM tableName AS b
WHERE b.DateStamp<= a.DateStamp
AND b.Status=1)
-
(SELECT COUNT(*)
FROM tableName AS b
WHERE b.DateStamp<= a.DateStamp
AND b.Status=3)
FROM tableName AS a



or with something like this (maybe faster):


SELECT a.dateStamp, COUNT(b.f1) - COUNT(c.f1)
FROM (tableName AS a LEFT JOIN
(SELECT dateStamp AS f1 FROM tableName WHERE status=1) AS b
ON a.dateStamp >= b.f1) LEFT JOIN
(SELECT dateStamp AS f1 FROM tableName WHER status=3) AS c
ON a.dateStamp >= c.f1
GROUO BY a.dateStamp





Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

some typo on the last query, should be:


SELECT a.dateStamp, COUNT(b.f1) - COUNT(c.f1)
FROM (tableName AS a LEFT JOIN
(SELECT dateStamp AS f1 FROM tableName WHERE status=1) AS b
ON a.dateStamp >= b.f1) LEFT JOIN
(SELECT dateStamp AS f1 FROM tableName WHERE status=3) AS c
ON a.dateStamp >= c.f1
GROUP BY a.dateStamp