Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    London, Gtr London, England
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Suggestion needed (Access 2000)

    Hello all,

    Please help with a pointer in the right direction. I have something working, but I am sure there is a better (quicker) way to do this.

    I have two tables, People and Event.
    People contains an ID and a date range.
    Events contains the ID, a date for an event, and about 30 flags regarding what happened at the event.

    What I need to do is summarise what happened for each person on all 30 flags for the date range,
    ie ID1, Date1 - Date2, 3 phone calls, 4 visits, 0 emails, etc etc

    At the minute I am using recordsets to query the Event table and then rowcount properties for the number of times each of the 30 separate things happened.
    This works fine, but for each person I am running 30 SELECT queries, which is obviously not speedy.

    Can someone please suggest another way that I can do this? There must be one, and I don't want to leave this as "its not broken, so don't fix it".
    Thanks in advance for any suggestions.

    j

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suggestion needed (Access 2000)

    It would seem possible to use one or more totals queries to gather the information you need, but perhaps a change in table structure would be more efficient.. Could attach a small sample of what you're working with? See <post#=401925>post 401925</post#> for instructions on posting a stripped down copy of a database.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestion needed (Access 2000)

    What I did in a similiar situation was open a recordset and declare integer variables for each thing that needed counting. I then looped through the recordset, with a series of case select or if statements, adding one to the variable for each thing that needed counting. That can then be put into a form or report, or exported to excel for further analysis.

    Part of the code

    Dim rst As ADODB.Recordset

    Dim intCountAleph As Integer
    Dim intCountBet As Integer
    Dim intCountGimel As Integer
    Dim intCount112 As Integer
    Dim intCount122 As Integer
    Dim intCount221 As Integer


    With rst



    .MoveFirst

    Do Until .EOF
    Select Case .Fields(8).Value
    Case 112
    intCount112 = intCount112 + 1
    Case 122
    intCount122 = intCount122 + 1
    Case 221
    intCount221 = intCount221 + 1
    End Select


    If IsNull(.Fields(3)) = True Then
    intCountNoBirth = intCountNoBirth + 1
    Else

    Select Case Age(.Fields(3), .Fields(5))
    Case "

Posting Permissions

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