Getting Record Status from a History Table

[Baapi]

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

Many Thanks
Baapi

 

[ByteMyzer]

Try:

SELECT COUNT(*)
FROM history T1
INNER JOIN
 (SELECT T2.HistoryID, MAX(T2.DateStamp) AS DateStamp
  FROM history T2
  WHERE T2.DateStamp<=[ParamDateStamp]
  GROUP BY T2.HistoryID) T2
 ON T1.HistoryID=T2.HistoryID
 AND T1.DateStamp=T2.DateStamp
WHERE T1.Status<3;

...where ParamDateStamp is your time parameter.

 

[Baapi]

Thanks but, not working... your SQL

SELECT COUNT(*)
FROM history T1
INNER JOIN
 (SELECT T2.HistoryID, MAX(T2.DateStamp) AS DateStamp
  FROM history T2
  WHERE T2.DateStamp<=[ParamDateStamp]
  GROUP BY T2.HistoryID) T2
 ON T1.HistoryID=T2.HistoryID
 AND [COLOR="Red"]T1.DateStamp=T2.DateStamp[/COLOR]
WHERE T1.Status<3;

the condition marked above in red is not in my tables. Also, the last clause "WHERE T1.Status<3" too cannot be right as I've to base the query on HIST(T2) Table and not MAIN(T1) table...

Just to reiterate my problem,

• MAIN has 1 record and HIST has multiple records.
• MAIN stores the latest status and HIST stores one record per every change in the status with the date and time it was changed.
• I want to query the HIST and know the number of records which are not in Status 3.... say as of 10:00 AM yesterday or last week (I cannot get this from MAIN table)

Thanks for your help

Cheers!
Baapi

 

[ByteMyzer]

First, had you read my SQL carefully, you'd have noticed that at no time did I ever make reference to the MAIN table. T1 and T2 are table aliases of the same table: history.

Second, you said:

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

You said you had a table called history; you did not say that you had a table called HIST.

Finally, assuming that your history table is named HIST, and the field names are HistoryID, Status and DateStamp, then the following SQL should work:

SELECT COUNT(*)
FROM [b][i]HIST[/i][/b] T1
INNER JOIN
 (SELECT T2.[b][i]HistoryID[/i][/b], MAX(T2.[b][i]DateStamp[/i][/b]) AS [b][i]DateStamp[/i][/b]
  FROM HIST T2
  WHERE T2.[b][i]DateStamp[/i][/b]<=[b][i][ParamDateStamp][/i][/b]
  GROUP BY T2.[b][i]HistoryID[/i][/b]) T2
 ON T1.[b][i]HistoryID[/i][/b]=T2.[b][i]HistoryID[/i][/b]
 AND T1.[b][i]DateStamp[/i][/b]=T2.[b][i]DateStamp[/i][/b]
WHERE T1.[b][i]Status[/i][/b]<3;

 

[Baapi]

That works great for me. Thank you so much.

Is there a web link/ source where I can learn this from? I have done a lot of queries (mostly simple) before but was unaware of using the same table (alias) for results like this.

Thanks you again,
Baapi