Help Transforming Table

[GrexP]

I have a many table with list of dates showing an active and inactive status of people. The table has 3 columns like this…

Person ID	Status		StatusDate
14589		Active		06/01/2007
14589		Inactive	10/14/2007
14589		Active		09/22/2008
78569		Active		05/02/2006
78569		Inactive	07/04/2007
78569		Active		02/12/2008
78569		Inactive	01/22/2009

In the sample data above, the first person, 14589, is currently active as of 9/22/2008, and they were inactive between 10/14/2007 and 9/22/2008. The second person is currently inactive. They we inactivated on 01/22/2009. They were active between 05/02/2006 and 07/04/2007, and then again between 02/12/2008 and 01/22/2009.

What I want to be able to do is to take a list if Person Ids and find out who was active on a certain date, or even better, in a date range.

One thought was to take the data as it currently is and change it in to a table like the one below, but I was having troubles writing the queries to do it.

Person ID	Active			Inactive
14589		06/012/007		10/14/2007
14589		09/22/2008		<NULL>
78569		05/02/2006		07/04/2007
75869		02/12/2008		01/22/2009

With this I could see if a certain date was Between [Active] And [Inactive] Or Date Is >[Active] and [Inactive] Is Null. I tried to build the table with a crosstab query (PersonID = Row, Status = Column, StatusDate = Value), but it would only give me on row for each person ID because it was looking at the Max or Min of StatusDate.

Any help is appreciated.


Greg

 

[RainLover]

Greg

You will need to create a User Defined Function to do this.

Have a look at this link.

http://allenbrowne.com/AppInventory.html

Although this has little to do with your situation it does hold the answer to your problem.

 If Not IsNull(vProductID) Then
        'Initialize: Validate and convert parameters.
        Set db = CurrentDb()
        lngProduct = vProductID
        If IsDate(vAsOfDate) Then
            strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
        End If

        'Get the last stocktake date and quantity for this product.
        If Len(strAsOf) > 0 Then
            strDateClause = " AND (StockTakeDate <= " & strAsOf & ")"
        End If
        strSQL = "SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake " & _
            "WHERE ((ProductID = " & lngProduct & ")" & strDateClause & _
            ") ORDER BY StockTakeDate DESC;"

        Set rs = db.OpenRecordset(strSQL)
        With rs
            If .RecordCount > 0 Then
                strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/yyyy") & "#"
                lngQtyLast = Nz(!Quantity, 0)
            End If
        End With
        rs.Close

This part of the code is what you need to look at. This returns the Stocktake value at a certain date. You could change it to return the Status at a certain date.

 

[RainLover]

Greg

Relooking at what you have I think you need to change your Table design. You only need one field for Data and another for Status. Like

ID Date Status
1 1/01/09 Active
1 5/01/09 Inactive.

By looking at this you can see that ID 1 was Active between Jan 1 and Jan 5 otherwise Inactive.

 

[GrexP]

RainLover,

Thanks for the feedback. Your first post does give me some ideas. The tblStockTake used in the example is structured differently, which is my problem. Mine is a poorly structured table. Restructuring the table is obviously what I need to do, unless someone more clever than I can come up with a way to query the table the way it is.

If I do restructure it, I need to do it with queries or code because this information is supplied to me in a CSV file, which gets updated regularly. It gets imported and then I either need to be able to query it the way it is or transform into a more useful structure.

I must admit I'm a little confused about your second post. Your "new" proposed table is identical to what I have now. You write that, "By looking at this you can see that ID 1 was Active between Jan 1 and Jan 5 otherwise Inactive". Sure, I can look at the data and see that, but I need to be able to query it. Each ID can 1 or dozens of Active and Inactive records. I need to be able to take 5000 Person IDs and return only those that are active on a given date or in a date range.

 

[RainLover]

I need to get a better understanding of your table. I understand that you receive this in CSV so that makes life difficult.
You have 3 Fields.

PersonID
Active which is a Date
Inactive which is a Date

Mine Table is also 3 fields
PersonID
Date which is date of Change
Status which is Active or Inactive

If you can't change the structure then we must find a way around it.

Please tell me, what is the Status of 75869 on Jan 1 2008. I am assuming Inactive as they became Inactive on 07/04/2007 and did not become Active again until 02/12/2008.
Is this right.
I am also thinking that my first post to you is not the correct approach. If you want a list then we will have to create an Array or write to a Temp Table.

 

[RainLover]

I must be having a bad day.

Adapt this SQL

SELECT MyTable.PersonID, MyTable.Active, MyTable.Inactive
FROM MyTable
WHERE (((MyTable.Active)<=[Forms]![Form]![txtDate]) AND ((MyTable.Inactive)>=[Forms]![Form]![txtDate] Or (MyTable.Inactive) Is Null));

 

[GrexP]

I must be having a bad day.

Adapt this SQL

SELECT MyTable.PersonID, MyTable.Active, MyTable.Inactive
FROM MyTable
WHERE (((MyTable.Active)<=[Forms]![Form]![txtDate]) AND ((MyTable.Inactive)>=[Forms]![Form]![txtDate] Or (MyTable.Inactive) Is Null));

I guess I'm not explaining this properly. THE PROBLEM is that the data is not structured in a way that the query you wrote above would work. That is THE PROBLEM. There are no fields called "Active" and "Inactive".

If it was structured that way then the query you wrote would work. In fact, that is what I said in the last paragraph of my original post. Since the data is not structured in such a way that the query would work I need one of two things:

1) A query that will work with the way the data is structured.
2) Restructure the data with code and/or queries in to a table that will work with that query.

 

[RainLover]

I guess I'm not explaining this properly. THE PROBLEM is that the data is not structured in a way that the query you wrote above would work

No the problem is all mine.

I now understand, my sincere appologies.

Let me have another think.

Could you post a Database with just that table. It will save me the time creating my own. Then I will have a fiddle.

 

[GregD]

I think I got it. The TableStockDate example gave me an idea.

Public Function NewActiveDates()
    Dim ActRec As Recordset
    Dim iFreeFile As Integer
    Dim sTemp As String
    Dim iDatePairs As Integer
    Dim k As Integer
    Dim bAddNull As Boolean
    
    iFreeFile = FreeFile
    Open "C:\effectivedate.txt" For Output As iFreeFile
    
    Print #iFreeFile, "PersonID, FromDate, ToDate"
    
    'Get a list of unique ids
    With CurrentDb.OpenRecordset("SELECT DISTINCT tblActiveStatus.PersonID FROM tblActiveStatus", dbOpenDynaset)
        If Not .EOF And Not .BOF Then
            Do While Not .EOF
                'select all dates for a single id
                Set ActRec = CurrentDb.OpenRecordset("SELECT * FROM tblActiveStatus WHERE PersonID = " & .Fields("PersonID") & " ORDER BY EffectiveDate", dbOpenDynaset)
                If Not ActRec.EOF And Not ActRec.BOF Then
                    ActRec.MoveLast
                    'get a count of records
                    iDatePairs = ActRec.RecordCount
                    bAddNull = False
                    'make it an even number and note if it is odd
                    If iDatePairs Mod 2 = 1 Then
                        iDatePairs = iDatePairs - 1
                        bAddNull = True
                    End If
                    ActRec.MoveFirst
                    sTemp = ""
                    Do While Not ActRec.EOF
                        'step through 2 at a time and make the pair of dates
                        For k = 0 To iDatePairs - 1 Step 2
                            sTemp = sTemp & ActRec!PersonID & ", " & ActRec!EffectiveDate & ", "
                            ActRec.MoveNext
                            sTemp = sTemp & ActRec!EffectiveDate & vbCrLf
                            ActRec.MoveNext
                        Next
                        'if it is an odd number of dates add the last one plus the null
                        If bAddNull Then
                            sTemp = sTemp & ActRec!PersonID & ", " & ActRec!EffectiveDate & "," & vbCrLf
                            ActRec.MoveNext
                        End If
                    Loop
                End If
                'strip off the CRLF
                If Right(sTemp, 2) = vbCrLf Then
                    sTemp = Left(sTemp, Len(sTemp) - 2)
                End If
                'print to the file
                Print #iFreeFile, sTemp
                'close the record set and get the next person
                ActRec.Close
                .MoveNext
            Loop
        End If
        .Close
    End With

    Close iFreeFile
End Function

I can now use a query like...

SELECT MyTable.PersonID, MyTable.Active, MyTable.Inactive
FROM MyTable
WHERE (((MyTable.Active)<=[Forms]![Form]![txtDate]) AND ((MyTable.Inactive)>=[Forms]![Form]![txtDate] Or (MyTable.Inactive) Is Null));

 

[RainLover]

Good to see you got it.