Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2012
    Posts
    8

    Unanswered: New to VB..need help asap...

    Hi,

    Suppose if I have a table in MS access with the following fields:

    ID Status

    1001 Show

    1001 No Show

    1002 Show

    1002 Show

    1003 No Show

    I have to add in a new column say Status_History to each row, The value of Status_History should be as follows:

    ID Status Status_History

    1001 Show 0

    1001 No Show 1

    1002 Show 0

    1002 Show 1

    1002 No Show 1

    1002 Show .33

    i.e.
    it should reflect out of the previous visits how many times the customer(as percentage) did not show up i.e. "no show".

    Can anyone help in writing the VB code macro for this?

    Thanks....

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    First I would say that the initial table design should have been using a show/no show ID rather than text. It's easier to maintain and safer from a data integrity perspective.

    Secondly, the new design isn't logical. The table can't be both a history of their visits and a cumulative average. You should have a table structure that contains the history of the visits and use a query to calculate the average.

    If you add a Status table with ID 1 for SHOW and ID 0 for NO SHOW and link this to your main table then you could do something like this: CustomerID, StatusID, VisitDate.

    Since the table is now storing 0 or 1 for the show history, you could easily create a query that calculates the average Show/No Show by customer.

    Steve

  3. #3
    Join Date
    Jul 2012
    Posts
    8
    Hi Steve,

    I agree with your points but the problem is, in this case I cannot have a single row per customer since a single customer can have multiple visits and I need to maintain this granularity to preserve visit level info.

    For each visit I want to have his/her status history till that visit.

    Seemanta

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    I'm not suggesting that you have a single row per customer, you need the visit level info granularity. I'm suggesting that you store the visit details as show/no show and create a query to calculate the averages.

    The table as you described it is not logical. If you do a select query from the table where ID = 1002 you will end up with multiple records without any way of distinguishing what the status_history values mean.

    Your table needs to store the visit history. The history status till that visit is a calculation and doesn't belong in the table. If you really feel that you need it in the table than add a fourth column, but don't try to use the Status_History column for two purposes.

    Steve

  5. #5
    Join Date
    Jul 2012
    Posts
    8
    Let me explain again because I made a mistake in putting the numbers. The new table should be like this:

    ID Status Status_History

    1001 Show 0

    1001 No Show 0

    1002 Show 0

    1002 Show 0

    1002 No Show 0

    1002 Show .33

    For the customer with ID '1002' the third visit was a 'no show', so the status_history column for the fourth visit should be .33(1/3) i.e. 1 ' no show' out of the previous 3 visits.

    The formula for status_history should be something like:
    no. of "no show" visits/no. of total previous visits(show or no show)
    and this should be calculated for each row and added to the column status_history.

    Also the records will not be arranged or grouped by ID. So for each record I need to first pick the customer ID and then loop through the previous records for that ID and update the 'status_history'(using the formula) in the current record.

    I need to write a VB code to do this.

  6. #6
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    I understood the problem from the initial post, and I still maintain that the table design is illogical. I'm also not sure why you feel that you need VB code to do something that is better handled with a proper table design and a query.

    However, if you really want to do this in VBA I'll help you. What do you have so far?

    Steve

  7. #7
    Join Date
    Jul 2012
    Posts
    8
    Hi Steve,

    I understand from database point of view the table design is illogical but at the end of the day I want a single table (as I have shown in the previous post,one record per visit) to do some further analysis(it is appropriate to have the data in this format to perform the analysis).

    The reason I want to do this using VB is because the data will keep changing, so at later point of time I can just run the VB macro.

    Well I am new to VB, so I have not done anything right now but I believe it can be done through recordset in VB but not sure how.

    Thanks for helping..

  8. #8
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Ok, so the absolute easiest way to accomplish the end result you desire is to store only the history of show/no show in the table. A query can be easily written to do the desired calculation whenever you want to see it. Or you can just add another column to the table so you can store the visit history and also the overall visit calculation. You don't need to use any code to accomplish your needs but it could be a decent learning experience, so....

    If you care to, you can get a lot of opinions on the best way to connect to a database and there are much more effective ways of accomplishing this, but for this purpose I think that this the basic logic flow is the simplest to follow. You can always improve it later if desired:

    Declare a Database variable and set it to the current database

    Dim db as DAO.Database
    set db = currentDb()

    This will give you the connections that you need to work with the data. You will then need a recordset to hold the results of your queries for processing so...

    Dim rs as DAO.Recordset

    Now you need to get the show and no show visit counts for the person in question so declare two variables to hold these counts...

    dim showCount, noShowCount as Integer

    Then set the results of the query to the recordset for processing...

    set rs = db.OpenRecordset("select count(*) as visitCount from tblVisitStatus where ID = 1002 and Status = 'Show'")

    and pull the value out into the appropriate variable
    showCount = CInt(rs![visitCount])

    Do the same thing for the no show count.


    add 1 to the showCount since the current visit is a show and the record isn't in the database yet.

    Now you can enter the new visit record with the appropriate calculation

    db.Execute ("Insert into tblVisitStatus Values(1002,'Show'," & noShowCount/ShowCount & ")"

    Which will essentially create the fourth record in your example: 1002, Show, .33

    You can the create a form to kick off this process. You'll have to do a bit of research on the basics of VBA in MS Access to know where to put all of this.

    Steve

  9. #9
    Join Date
    Jul 2012
    Posts
    8
    Thanks for the explanation.
    But I have just written the below code(my first Vb code). Not sure if it is correct. Can you verify and rectify any mistakes. For the line marked in red, I need a condition to stop the loop(for the second record set) at the current record of the first record set pointer(not sure how to do this).

    Sub NoShowStatusHistory()

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim Scount As Integer
    Dim NScount As Integer
    Dim StatHistory As Integer
    Dim Show As String
    Dim NoShow As String

    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("Appt_Analytic_Record")
    Set rs2 = db.OpenRecordset("Appt_Analytic_Record")

    Show = "Show"
    NoShow = "No Show"

    If rs1.RecordCount = 0 Then Exit Sub

    rs1.MoveFirst

    Do Until rs1.EOF

    If rs2.RecordCount = 0 Then Exit Sub

    rs2.MoveFirst

    Scount = 0
    NScount = 0
    StatHistory = 0

    Do Until rs2.EOF

    If rs1![Ext Pat ID] = rs2![Ext Pat ID] Then

    If rs2![Status] = NoShow Then

    NScount = NScount + 1

    ElseIf rs2![Status] = Show Then

    Scount = Scount + 1

    End If

    rs2.MoveNext
    Loop

    StatHistory = Int((NScount / (NScount + Scount)) * 100)

    rs1.Edit

    rs1![StatusHistory] = StatHistory

    rs1.Update

    rs1.MoveNext
    Loop
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing

    Exit Sub

  10. #10
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Not really sure what you are trying to accomplish with the nested do while loops on your recordsets, but I can tell you that it's probably not the best way to get there!

    Also, if you make StatHistory an Integer you will never get an answer of .33, use a Double instead.

    Steve

  11. #11
    Join Date
    Jul 2012
    Posts
    8
    The first recordset pointer is to traverse through the records one by one, but then I need another pointer to check for previous cases with the same ID(the current record ID as pointed by the first recordset) starting from the first record till the current record as pointed by the first recordset pointer.

    When I update the recordset will it be also be reflected in the access table or do I have to use an explicit update query inside the code?

  12. #12
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by Maveavr666 View Post
    The first recordset pointer is to traverse through the records one by one, but then I need another pointer to check for previous cases with the same ID(the current record ID as pointed by the first recordset) starting from the first record till the current record as pointed by the first recordset pointer.
    The real question was why are you doing this? You really are making this way more difficult than it needs to be. All you need to do this is the show/no show counts for this person which you could easily get with a aggregate query or two simple count queries.

    In addition, the code you have written will update the status_history for every row in your table which makes your visit history pointless. Every row for 1002 will have .33 for a status history so you might as well just have a single row.

    Quote Originally Posted by Maveavr666 View Post
    the status_history column for the fourth visit should be .33(1/3) i.e. 1 ' no show' out of the previous 3 visits.
    Quote Originally Posted by Maveavr666 View Post
    Code:
    StatHistory = Int((NScount / (NScount + Scount)) * 100)
    Ignoring the fact that you can't convert a decimal into an integer, this is still bad math.
    Based on the data you provided earlier, when looking at visitor 1002 you would have NScount = 1 and Scount = 3

    so....
    ((1 / (1 + 3)) * 100)
    ((1 / (4)) * 100)
    ((.25) * 100)
    (25)

    Now, if your logic was correct you could use
    Code:
    StatHistory = (NScount / (NScount + Scount))
    but based on how you are counting things that's going to give you .25 not .33 like you said you wanted.

    Think back to what you are trying to accomplish and fix the logic to match.

    Quote Originally Posted by sps View Post
    I still maintain that the table design is illogical. I'm also not sure why you feel that you need VB code to do something that is better handled with a proper table design and a query.
    I still stand by this original statement.

    Steve

  13. #13
    Join Date
    Jul 2012
    Posts
    8
    All you need to do this is the show/no show counts for this person which you could easily get with a aggregate query or two simple count queries.
    I do not want for this person, I want for this appointment visit. A single person can have multiple appointment visits. For each appointment I need to have the status history(how many 'no shows' out of all the prior visits).

    Every row for 1002 will have .33 for a status history so you might as well just have a single row
    That is why I said in my previous post
    For the line marked in red, I need a condition to stop the loop(for the second record set) at the current record of the first record set pointer(not sure how to do this)
    In simple words, calculate the status history of the current record based on the prior visits(excluding the current one).

    Ignoring the fact that you can't convert a decimal into an integer, this is still bad math.
    Based on the data you provided earlier, when looking at visitor 1002 you would have NScount = 1 and Scount = 3
    Yes, I realized the type miss match, so I removed the INT later.
    How will Scount be 3? Scount(Show count) will be incremented only when status = "show".

    so NScount = 1 and Scount = 2 i.e. 1/3 =.33 (do not include the current record,again I need a logic to stop the inner pointer one record prior to the current one).

  14. #14
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by Maveavr666 View Post
    Quote:
    Every row for 1002 will have .33 for a status history so you might as well just have a single row
    That is why I said in my previous post
    but, unless you find a way to update only the row in question your vba code will update the status_history for every instance of that ID. So this time through every row for 1002 will end up with .33. The next time ID 1002 has a visit it will update every row with the new value.

    Since you don't have a way to uniquely identify a row in the database you really don't have a way to specify an update to a specific row. The only safe way I see of doing this is to insert a new row with the proper history_status already calcuated.

    If you do the calculation before inserting the row you won't need to stop the inner loop. After getting the new history_status value simply insert a new row for that visitor. The new row will have the updated value and all the previous rows will be left alone.

    Quote Originally Posted by Maveavr666 View Post
    again I need a logic to stop the inner pointer one record prior to the current one)
    I'm not sure if there is a safe way to do this if you are looking to update that value. You could check for a null status_history but since your database would allow null values, other rows could be null too and you still can't guarantee that you are on the correct record.
    If it's just the counts that concern you then simply do a minus 1 from the calculated counts.

    Quote Originally Posted by Maveavr666 View Post
    The formula for status_history should be something like:
    no. of "no show" visits/no. of total previous visits(show or no show)
    and this should be calculated for each row and added to the column status_history.
    You don't have any fields in your table that would define the order of the records so you can't know which visits came first. The only reliable calculation you can make is what the current status_history is. To do what you are asking you need an ordered unique id or a visit date.

    Steve

  15. #15
    Join Date
    Jul 2012
    Posts
    8
    To do what you are asking you need an ordered unique id or a visit date
    Thanks for the idea. I have the visit date. So now my code is:


    Sub NoShowStatusHistory()

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim Scount As Integer
    Dim NScount As Integer
    Dim StatHistory As Double
    Dim Show As String
    Dim NoShow As String

    On Error GoTo ErrorHandler

    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("Appt_Analytic_Record")
    Set rs2 = db.OpenRecordset("Appt_Analytic_Record")

    Show = "Show"
    NoShow = "No Show"

    If rs1.RecordCount = 0 Then Exit Sub

    rs1.MoveFirst
    ' loop through each record in the first recordset

    Do While Not rs1.EOF
    ' If matching record is found then update field in
    ' second recordset to value you determine

    If rs2.RecordCount = 0 Then Exit Sub

    rs2.MoveFirst

    Scount = 0
    NScount = 0
    StatHistory = 0

    Do While Not (DateDiff("d", rs1![VisitDate], rs2![VisitDate])>0)

    If rs1![Ext Pat ID] = rs2![Ext Pat ID] Then

    If rs2![Status] = NoShow Then

    NScount = NScount + 1

    ElseIf rs2![Status] = Show Then

    Scount = Scount + 1

    End If

    End If

    rs2.MoveNext
    Loop

    StatHistory = (NScount / (NScount + Scount)) * 100

    rs1.Edit

    rs1![StatusHistory] = StatHistory

    rs1.Update

    rs1.MoveNext
    Loop
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing

    Exit Sub

    ErrorHandler:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    End Sub

    When I am running this code in MS access as a VB module it is throwing an error (error 3265 Item not found in collection). I have checked the field names all looks fine

    I am using access 2010 and there is a .accdb database inside the table Appt_Analytic_Record is present on which the above code should run.

    What am I doing wrong?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •