Simple VBA coding needed to extract data on specific timeframe

[Tamrak]

I have been testing on the queries and VBA and got a headache how to resolve this task. I have a table that contains thousand of records and would like to ask for a simple VBA code to accomplish the task. Since the real table has over 100 fields, I will use three fields as an example.

Project_Number, numeric
Status, text,
Status_Date, date

Sample data:

1001, Start, 07/01/2006
1001, Active, 07/07/2006
1001, On-Hold, 07/09/2006
1002, Start, 07/15/2006
1002, Active, 07/18/2006
1002, Cancelled, 08/17/2006
1003, Start, 09/01/2006
1003, Active, 09/05/2006
1003, On-Hold, 09/11/2006
1004, Start, 09/02/2006
1004, Active, 09/18/2006
And so on…

What I would like to do is to have the query that will provide:

1. The result of the project numbers that changes from “Active” status to “On-Hold” or “Cancelled” status, within the specific timeframe.
2. Time frame can be selected by using this type of example, status_Date >= “07/01/2006” and status_date <=”08/31/2006” (User can select the time frame.)

In the real table, each project might consist of 10 or more records with different statuses.

The query might provide the result like this: (Project_number, Status, Status_Date)

1001, On-hold, 07/09/2006
1002, Cancelled, 08/17/2006

(Project # 1003 will not show because it is outside the parameter range and it is “On-hold” afterward. Project # 1004 is “Active”. So, the program will skip. )

Note: Project might become “Active” again, after “On-hold” - using previous example:

1001, Start, 07/01/2006
1001, Active, 07/07/2006
1001, On-Hold, 07/09/2006
1001, Active, 08/15/2006
1001, Cancelled, 09/08/2006

In this case, the query will not generate the result of Project # 1001 because it turns “Active” before the end date (08/31/2006). However, if we change the end date to “09/30/2006”, it will show up in the query.

Thank you in advance.

 

[PHV]

Something like this (SQL code) ?
PARAMETERS [Start date] DateTime, [End date] DateTime;
SELECT A.Project_Number, A.Status, A.Status_Date
FROM yourTable AS A INNER JOIN (
SELECT Project_Number, Max(Status_Date) AS LastDate FROM yourTable
WHERE Status_Date Between [Start date] And [End date] GROUP BY Project_Number
) AS L ON A.Project_Number = L.Project_Number AND A.Status_Date = L.LastDate
WHERE A.Status IN ('On-Hold','Cancelled') AND EXISTS (
SELECT * FROM yourTable WHERE Status_Date Between [Start date] And [End date]
AND Project_Number = A.Project_Number AND Status ='Active')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tamrak]

PHV,

Thank you so much for your assistant. I made some minor modifications and it provided a result that I was looking. I used Microsoft Access to run this SQL.


PARAMETERS [Start date] DateTime, [End date] DateTime;
SELECT A.Project_Number, A.Status, A.Status_Date
FROM myTable AS A INNER JOIN [SELECT Project_Number, Max([Status_Date]) AS LastDate FROM myTable WHERE Status_Date Between [Start date] And [End date] GROUP BY Project_Number]. AS L ON (A.Status_Date = L.LastDate) AND (A.Project_Number = L.Project_Number)
WHERE (((A.Status) In ('On-Hold','Cancelled')) AND ((Exists (SELECT * FROM myTable WHERE Status_Date Between [Start date] And [End date] AND Project_Number = A.Project_Number AND Status ='Active'))<>False));