Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query question (2003 SP 2)

    I still am using Access just enough to be stupid. I am trying to trim a list of prescriptions down from 2-6 or more per patient to 1. The list is too big to fit on an Excel 2003 sheet. The ID number field would seem to be perfect - limit the query to showing only one of each number no matter how many prescriptions the patient has. Does anyone have a suggestion how that can be done?
    Jim Whitt
    Pharmacist
    Temple, Texas

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

    Re: Query question (2003 SP 2)

    Without having more specific information it's hard to recommend anything, but you could try a Totals query (select View | Totals or click the Totals button on the toolbar to activate the Total row in the query design window.
    Add the patient ID and prescription ID (or whatever) to the query grid. The Total option for the patient ID should be Group By (the default) and that for the prescription First (or Last).

  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2003 SP 2)

    I'm sorry I couldn't be more specific. We're trying very hard to safeguard private data that it takes a lot of time to mock up fake data. I couldn't get the suggestion to work, probably because I couldn't fully explain the problem. What I think I'll do now is go back to the original file from the mainframe and cut it in two so I can find them into Excel sheets. I have some Excel tools that can get rid of the duplicate SSNs. Thanks for the help.
    Jim Whitt
    Pharmacist
    Temple, Texas

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Query question (2003 SP 2)

    Jim,

    If I understood you correctly, you are trying to show unique prescriptions by person. So, if Person A is prescribed aspirin 20 times per year, you would only want to see Person A and Aspirin once or perhaps, Person A, Aspirin, 20 (showing how many times person A needed aspirin)

    Each person will need some type of Unique ID which is typically the SS No. or other number. I assume there is a table that has prescriptions associated with the person such that one person can be associated with many prescriptions. Run a query that extracts the Persons ID and related Prescriptions for the time period wanted. You wil get a lot of results. In the query you can then select View Totals to group the data or use Select Distinct in Sql to get what you want. If you use View Totals, you could also add a counter as a third column such as Counter:1 and then sum on the Counter field to see how many of the particular prescriptoins the person had over the time period. HTH.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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