Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    London, Gtr London, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Help needed (Access97)

    I am writing a Contacts database in Access97 for a Health Charity. I have tables for customer details, and how each user wants to be kept in contact with e.g. email, post, telephone, These are in order of priority and form the basis of the problem I am trying to solve, there are also various other tables. I currently have 200 records in the database

    I need to create a report that shows customer details by the highest priority of contact. For example if customer Derek White has said that he wants to be kept in contact by email and phone, then since email is a higher priority than phone the following should happen;

    1. Derek White should only appear on an email report.
    2. When a list of contacts by phone is generated Derek White does not appear.
    3. If Derek White decides to remove his email contact then his name should automatically appear on the contact by phone report

    Could anyone give me some assistance with writing the query or code that would create the above?
    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help needed (Access97)

    If Derek has chosen email and phone, how is that stored in the Customer Details table? And how are you storing the order of priorities (the fact that email is a higher priority of contact)?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query Help needed (Access97)

    I presume that you are putting all of the customer details in a single table - it makes it simpler but imposes some limits. Ignoring that issue, you simply have a field that indicates preferred contact method, and you make the data source for your report a query which restricts each report to phone or email (how about fax). And you can get creative and use an expression to display either the email or phone so you can show in a single report what the preferred method of contact is, and the address to be used.

    The primary constraint in this approach is that people are limited to a single phone and a single email address. We recently built a large system (100K contacts), and built a single table for all electronic addresses so people could have multiple phones, faxes, emails and even URLs. We still use a field in the primary contact details table to indicate which is the preferred type of communication, and for each type of address we mark one of the addresses as the preferred address. This makes the query you use to populate a report a bit more complex, as you must use table joins bases on some sort of person ID. Hope this helps.
    Wendell

  4. #4
    New Lounger
    Join Date
    May 2002
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help needed (Access97)

    The easiest way to do what you want is the following (This assumes that you have a table with separate records for each contact method for each person).

    1. Using the query wizard, create a query that contains all of the information that you want to include on your report. Include the "ContactPriority" field in the query grid. Leave the query open in design view.

    2. Using the query wizard, create a second query that contains two fields: ContactName (or ContactID) and ContactPriority. Under "View" on the menu bar, select "Totals". Uncheck the "Show" box for ContactName. In the "Total" row, select "GroupBy for ContactName and "Max" for ContactPriority. Switch to "SQL View" and eliminate all hard returns. Copy the entire SQL statement from the second query.

    3. Return to the first query. In the "Criteria" row for ContactPriority, enter "=( )" (leave out the double quotes). Place the cursor between the two parentheses, and paste the SQL statement from the second query. Save the query.

    4. Use the first query as the recordsource for your report. It will give you the contact information for only the highest priority contact for each person. If one contact is removed, then the next highest will be displayed.

    5. From this point, there are a number of alternatives for limiting the report to only one type of contact method. You could place a parameter in the criteria for contacttype in the first query, or you could create a form with a combo box on it with the contact types in it. Place a command button on the form that will generate the report after you select the type of report that you want. In the code for the command button, place the following:

    strWhere=buildcriteria("ContactType",dbText, Me.cboContactType) ' If the combo box returns a text string
    or
    strWhere=buildcriteria("ContactTypeID",dbLong,Me.c boContactTypeID) ' If the combo box returns a long.


    DoCmd.OpenReport "rptContactInformation", acViewPreview, , strWhere

    For more information about subqueries, look under MS Access help for "subqueries"

Posting Permissions

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