How to get the historic data from a linked table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Back
Top