Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Help (2003)

    I have a query that has a "client number" field and a "Region" field. I want the query to show me client numbers that are in more than 1 region. What would I put in the criteria or would I have to make 2 queries. Clients should be unique but when you add the Region field it is possible for a client to be in two Regions.

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

    Re: Query Help (2003)

    Create a query based on the table containing those fields.
    Add client number and region to the query grid.
    Set the Unique Values property of the query to Yes.
    Save this query.
    Create a new query based on the one you just saved.
    Add the client number and region fields to the query grid.
    Select View | Totals.
    Change the Total option for region to Count.
    Set the Criteria for the region column to >1.
    This query should return the client numbers that are in more than one region.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2003)

    Thanks Hans. I have another question. Sometimes this query is not going to show any records. I am using this query in a report. When there aren't any records, I want the report to show a message "no records". I know this has been asked before and I am searching but can't seem to find anything.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2003)

    I found the code:
    Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There are no duplicate Clients", vbInformation
    Cancel = True
    End Sub

Posting Permissions

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