Results 1 to 4 of 4
  1. #1
    cardgage is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    50

    Creating a modified count query

    Good evening all,

    I am currently trying to developing a database for a research project that will keep track of discharge medications, and will flag if a patient is discharged on more than one medication. However, the same medication given by different routes should only be counted as one. I have attached the database, with an example test patient.



    In the attached database, frmOpening contains an unbound combo box (cmbPatientSelect) that lists all included patients. Once a patient is selected, clicking on the "Enter Data" button will bring up the form where data is entered; the table tblDCDrugs contains the data entered into the subform "Discharge medications" on frmPatientData.

    In the example in the attached database, there are 5 records with 3 individual medications. I would like to create a query that returns "3" in this case (i.e., only counts unique values). Would someone be able to help me create something like this?

    Thanks in advance for any help!
    Attached Files Attached Files

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Till some one comes along,

    Assuming you are referring to
    Discharge medications

    Code:
    Loxapine 50 PO daily
    Risperidone 
    30 IM q6h
    Risperidone 
    100 SQ qmonth
    Haloperidol 
    50 PO q2h
    Haloperidol 
    50 IM q2h 
    do you want the sub-form Discharge medications to display

    Loxapine
    Risperidone
    Haloperidol

    without any other details ?

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My solution is to use a UDF. You could put it in a form module or a standard module. The code looks like:
    Code:
    Option Compare Database
    Option Explicit
    
    
    Public Function GetUniqueDCMeds(pP_ID As Long)
        Dim r As DAO.Recordset
        Dim sSQL As String
    
        'set default return value
        GetUniqueDCMeds = 0
    
        sSQL = "SELECT DISTINCT tblDCDrugs.Drug"
        sSQL = sSQL & " FROM tblDCDrugs"
        sSQL = sSQL & " WHERE tblDCDrugs.PatientID=" & pP_ID & ";"
    
        Set r = CurrentDb.OpenRecordset(sSQL)
        If Not (r.BOF And r.EOF) Then
            r.MoveLast
            GetUniqueDCMeds = r.RecordCount
        End If
        r.Close
        Set r = Nothing
    End Function
    Add an unbound text box on the form "frmPatientData".
    Set the "Control Source" property to "=GetUniqueDCMeds([PatientID])" (without quotes)


    Attachment 10377

    I see a problem in that there are no dates to limit the medications. If Patient 1 is admitted after 12/14/2012, the current visit drugs will be included with the count of drugs from the previous visits.

    ----------------
    I would strongly suggest you stop where you are at and read up on Normalization. You have many problems with your current structure.
    For instance, you have 6 tables dealing with disorders. (I think that is what DO means). What happens if you decide to add another disorder type. You would have to add another table and modify queries, forms and reports.
    You should have 1 table for all disorders, with an additional field to specify DO type.

    The two tables "tblAdmitDrugs" and "tblDCDrugs" also have the identical structures. They could be merged, with an additional field to specify "Admit" or "DC".

    You also are using multi-value fields. Not a good idea. Really a pain when designing reports or trying to do aggregates. (counts, sums)
    You have look-up fields; also not a good idea. You type in the SQL in the field, then have to type the SQL again in a combo box. The only time look-up fields *might* have a use is if you are the only one entering data and you are entering the data using datasheet view.

    See http://access.mvps.org/access/tencommandments.htm and http://access.mvps.org/access/lookupfields.htm


    Just my $0.02

  4. #4
    cardgage is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    50
    Thank you so much for the help ssanfu, that worked like a charm!

    I also appreciate your insight into the design of my database. Fortunately, this database is only for short-term use for a research project (i.e., 3 months); however, I create 2-3 of these every year, so I'll be sure to read up and improve my database design.

    Take care, and enjoy the holidays!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-10-2012, 09:16 AM
  2. Replies: 10
    Last Post: 05-08-2012, 09:17 AM
  3. Modified Date
    By PatCollins in forum Import/Export Data
    Replies: 3
    Last Post: 04-24-2012, 01:13 PM
  4. creating a report to count by month
    By kwooten in forum Reports
    Replies: 13
    Last Post: 09-12-2011, 01:29 PM
  5. Replies: 2
    Last Post: 05-09-2011, 06:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums