Calculating percentage of events that result in a record change

[PRMiller2]

Hi gurus,

I need to design a report that provides the percentage of consultations that result in the prospect becoming a client. Consultations are recorded in an events table, but, when a prospect becomes a client, that event is NOT recorded. Instead, a value is changed in the client table (where both prospects and clients are stored). Here is my table structure:

[b]eventOccurrence[/b]
pk_EventOccurrenceID
fk_EventID
eventDate
fk_ClientID

[b]client[/b]
pk_ClientID
intClient
intProspect

When someone becomes a prospect, their record is added to the client table, and the value "-1" is set in intProspect.

When the prospect has a consultation, an event (fk_EventID of 7) is recorded in the events table along with, of course, the foreign key for their client record. Then, the "-1" value is removed from the intProspect field in the client's record, and a "-1" value is added for the intClient field.

Here's the SQL I currently have:

SELECT eventOccurrence.eventDate, eventOccurrence.fk_EventID, client.intClient
FROM eventOccurrence 
LEFT JOIN client ON eventOccurrence.fk_ClientID = client.pk_ClientID
WHERE eventOccurrence.fk_EventID)=7;

And here's an example of the results of that query:

[b]eventDate     fk_EventID     intClient[/b]
7/23/2010        7
12/21/2010       7              -1
12/16/2010       7              -1
11/9/2010        7              -1
10/29/2010       7              -1
12/26/2010       7              -1
7/31/2010        7
1/7/2011         7
1/18/2011        7              -1

As you can see, not all consultations result in clients. What's the best way to generate a percentage? This will be done in a report, so I'm not adverse to more than one query, even running them in VBA.

If worst comes to worst, we can always modify the database to have a "became client" event in addition to the intClient flag, though that may necessitate updating a number of records.

Thoughts?

 

[PRMiller2]

I'm thinking within the report, perhaps I should run the query, populate a variable programmatically with a count of the number of events, then run it again and populate a second variable/text box with the number of intClients...?

I guess another question is this: what would be the best practice in terms of data storage to report on these kinds of figures?

 

[PRMiller2]

Here's the solution I came up with, programmatically:

Private Sub Report_Load()

    Dim db As dao.Database
    Dim rs As dao.Recordset
    Dim strSQL As String
    
    'Populate % Buyer Appointments to Contracts
    strSQL = "SELECT Count(eventOccurrence.fk_EventID) AS Appointments" & _
             " FROM eventOccurrence" & _
             " WHERE (((eventOccurrence.eventDate) Between #" & g_datStartDate & "# AND #" & g_datEndDate & "#)" & _
             " AND ((eventOccurrence.fk_EventID)=7));"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
        Me.txtBuyerAppointments.Value = Nz(rs("Appointments"))
    End If
    
    Set rs = Nothing
    
    strSQL = "SELECT Count(client.intClient) AS Contracts" & _
             " FROM eventOccurrence" & _
             " LEFT JOIN client ON eventOccurrence.fk_ClientID = client.pk_ClientID" & _
             " WHERE (((eventOccurrence.eventDate) Between #" & g_datStartDate & "# AND #" & g_datEndDate & "#)" & _
             " AND ((eventOccurrence.fk_EventID)=7) AND ((client.intClient)=-1));"

    Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
        If Nz(rs("Contracts")) < 1 Then
            Me.txtBuyerContracts.Value = "0"
            Me.txtBuyerContracts2.Value = "0"
            Me.txtBuyerPercentage.Value = "0%"
        Else
            Me.txtBuyerContracts.Value = Nz(rs("Contracts"))
            Me.txtBuyerContracts2.Value = Nz(rs("Contracts"))
            Me.txtBuyerPercentage.Value = Me.txtBuyerContracts / Me.txtBuyerAppointments
        End If
    End If
    
    Set rs = Nothing
    
    'Populate % Buyer Contracts to Sales
    strSQL = "SELECT Count(client.intClient) AS Sales" & _
             " FROM (eventOccurrence" & _
             " LEFT JOIN client ON eventOccurrence.fk_ClientID = client.pk_ClientID)" & _
             " LEFT JOIN reTransaction ON client.pk_ClientID = reTransaction.fk_ClientID" & _
             " WHERE (((eventOccurrence.eventDate) Between #" & g_datStartDate & "# AND #" & g_datEndDate & "#)" & _
             " AND ((eventOccurrence.fk_EventID)=7)" & _
             " AND ((client.intClient)=-1)" & _
             " AND ((reTransaction.fk_ClientID) Is Not Null));"

    Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
        If Nz(rs("Sales")) < 1 Then
            Me.txtBuyerSales.Value = "0"
            Me.txtBuyersSoldPercentage.Value = "0%"
        Else
            Me.txtBuyerSales.Value = Nz(rs("Sales"))
            Me.txtBuyersSoldPercentage.Value = Me.txtBuyerSales / Me.txtBuyerContracts2
        End If
    End If
    
    Set rs = Nothing
    Set db = Nothing

End Sub