Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count records with same surname (2002)

    Hi folks, I have a continuous form which shows clients who did not show up for their appointment. I would like to create an unbound text box with a function that would count the number of times, for each client (likely based on surname and given), they didn't show. I've been trying using DCount w/o much luck. The form is based on a table, not a query. thanks, Van

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

    Re: count records with same surname (2002)

    You should have a separate table tblClients, with a unique ClientID (primary key, preferably an AutoNumber).
    The table you are using then only needs a ClientID field; use a combo box bound to ClientID on the form that displays the name, so that the user can select the client from a dropdown list.
    The text box you want could have control source

    =DCount("*","NameOfYourTable","ClientID=" & [ClientID])

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count records with same surname (2002)

    Thanks Hans. The no-show clients on my form are populated from our appointment book. The secretaries only take down name, date of birth, referring doctor and tel number. Each new record has an AutoNumber called ID (hidden on app't book form). If the client doesn't show for app't, they use a check box which then triggers an append query event, which appends the record to another table (tblNoShowPSGFston). It's this table that the no-show form is based on. What i'm getting, for example, is the same client who failed to show for 3 appt's, on the form, but with different ID numbers. In other words, each time the same client is booked, he/she receives a new ID AutoNumber. We see hundreds of clients, so i want to avoid having our secretaries look up names in a pull-down list for previous bookings, although if i read your help correct, that may be the only way...is that right? thanks, Van

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

    Re: count records with same surname (2002)

    It's a trade-off. Entering the names each time increases the probability that the same patient will be listed with slightly different names, such as "Alonso" and "Alonzo", or David Williams" and "Dave Williams". Using a combo box doesn't prevent this entirely, but it avoids most of this problem. If the secretary types the first characters of a name, the combo box automatically completes it, adjusting it as the secretary keeps on typing.

    But is is possible to match on names:
    <code>
    =DCount("*","tblNoShowPSGFston","[Surname] = " & Chr(34) & [Surname] & Chr(34))
    </code>
    or on surname and birth date:
    <code>
    =DCount("*","tblNoShowPSGFston","[Surname] = " & Chr(34) & [Surname] & Chr(34) & " And [BirthDate] = #" & Format([BirthDate],"mm/dd/yyyy") & "#")
    </code>
    Performing a "fuzzy" match to allow for slight differences would be much more work.

  5. #5
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count records with same surname (2002)

    Thanks muchly Hans, that works really well. just out of curiosity, what does Chr(34) do? thanks, Van :-)

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

    Re: count records with same surname (2002)

    Chr(34) is the double quote ", it is used to include quotes in a quoted string. You must enclose a string value such as a surname in quotes, just like you must enclose a literal date value in # characters.

Posting Permissions

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