Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prioritizing Objects in a Query (Access 2003)

    I have inherited a database from a previous administrator from which the report it was designed to satisfy has changed.

    Although the database itself is in the process of being re-designed I still need to pull data from it that will satisfy the new report.

    This report records the number of clients who attended for a specific advocacy service. For example a client attends our office for a family matter in the month of July. They may see the advocate 5 times for the family issue (it is possible that they also attend for another issue) but only counted as one family client. I am able to build a query ( a little difficult because the person who designed this intended the name to the unique identifier) to satisfy these requirements, however the problem arises that we also only count one level of service for each client.

    Continuing with the above example, the family law client received info/referral service and progressed up to full representation service. They can only be counted on time for the highest level of service. The order of service begins with Info/Referral, then advice/Summary, then full representation worked on, the full representation closed.

    How do I build a query that will “prioritize: the levels of service and only count the highest for that particular client in a particular Area of Law?

    I have included a picture of the report I am trying to generate as well I will post the database in a second post.

    Thank you in advance for your assistance.
    Attached Files Attached Files

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

    Re: Prioritizing Objects in a Query (Access 2003)

    Due to the way the database has been designed, the data are inconsistent.
    The "Area of Law" field contains values such as "Family", "Family/housing", "Family/ housing", "Housing" and "Housing/family". Apart from the superfluous space, how should these be treated?
    The "Level of Service" field contains values such as "Advice summary", "Advice summary / info referral" (with 1 space between info and referral but also with 2 spaces between them), "Advice summary/info ref", "file review" and "hasn't been at a app yet".
    I think the database needs to be cleaned up (and preferably redesigned) before you can do something useful with it.

    Suggestions:
    - Make the data consistent.
    - Use numeric key fields for Area of Law and Level of Service.
    - Replace the text fields for these in the Tracking Form table with the corresponding number fields.
    - Change the relationships between the tables over to the number fields.
    - Enforce referential integrity for the relationships.
    - Add a "Priority" field to the Level of Service table (or use the numeric key field for that).

    You can then create a totals query that returns the highest service level for each client.

  3. #3
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prioritizing Objects in a Query (Access 2003)

    Thank you for the quick response. I didn't realise how bad it was, so yes I am working on the redesign of it now.

    Im not exactly sure what you mean by "Use numeric key fields for Area of Law and Level of Service". Its possibly just terminology im not familiar with. Could you please tell me what those are or possibly give me an example

    Thank you

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

    Re: Prioritizing Objects in a Query (Access 2003)

    I have attached a version of your database (in Access 2000 format) in which I added numeric primary key fields to the Area of Law and Level of Service tables, with corresponding fields in the Tracking table.
    The queries used to fill in the fields in the Tracking table are included, as well as queries listing the invalid Area of Law and Level of Service fields.
    I've also added a query that shows the highest service level for each name. It ignores the invalid service levels.
    Attached Files Attached Files

Posting Permissions

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