Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Melbourne, Australia
    Posts
    3

    Unanswered: Removing duplicates of multiple groupings

    Hi,

    I'm currently developing a database of a large survey group. There are 5 different forms with each participant possibly completing more than one of each form at different dates.

    I'm trying to build a query to list the dates that each form was completed, and have had some success in doing this with just one form at a time. However, whenever I try to get a list of more than one form, I get duplicate entries of the form dates, depending on the number of other forms types completed. As more forms are included, the greater the number of duplicates (ie. permutations of different date combinations).

    I've included a sample of the query results with the duplicates that I don't want included highlighted with an asterisk (*) (or in the attachment as red, in case the formatting below didn't work). Some forms were completed at the same time and some participants have yet to fill out any forms.

    Patient Form A Dates Form B Dates Form C Dates
    008006 23/06/2003 3/09/2003 23/06/2003
    008006 23/06/2003* 3/09/2003* 9/10/2003
    008006 9/10/2003 3/09/2003* 23/06/2003*
    008006 9/10/2003* 3/09/2003* 9/10/2003*
    008007
    008008 29/05/2003 26/08/2003 26/05/2003
    008008 29/05/2003* 26/08/2003* 26/08/2003
    008008 29/08/2003 26/08/2003* 26/05/2003*
    008008 29/08/2003* 26/08/2003* 26/08/2003*
    008009 5/09/2003 5/09/2003
    008010 31/10/2003 31/10/2003


    Cheers,
    epig
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I strongly recommend that your table be redesigned.

    select t.patient, t.form1, 'Form1' As "T"
    from table t
    group by t.patient, t.form1
    having count(*) = 1
    UNION ALL
    select t.patient, t.form2, 'Form2' As "T"
    from table t
    group by t.patient, t.form2
    having count(*) = 1;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Location
    Melbourne, Australia
    Posts
    3
    Thanks for the reply - maybe I didn't word it properly because yours was the only one I got.

    What/why should the table(s) be redesigned? I've got six, one for the patient details, and five for each of the forms which are related via the Patient ID.

    Anyway, I've used your code to create a UNION query and it works somewhat as it removes the duplicates (excuse the proper table names):

    SELECT Patients.[Patient ID], AQoL.Date
    FROM Patients LEFT JOIN AQoL ON Patients.[Patient ID] = AQoL.[Patient ID];
    UNION SELECT Patients.[Patient ID], HSU.Date
    FROM Patients LEFT JOIN HSU ON Patients.[Patient ID] = HSU.[Patient ID];
    UNION SELECT Patients.[Patient ID], KDQOL.Date
    FROM Patients LEFT JOIN KDQOL ON Patients.[Patient ID] = KDQOL.[Patient ID];
    UNION SELECT Patients.[Patient ID], HUI.Date
    FROM Patients LEFT JOIN HUI ON Patients.[Patient ID] = HUI.[Patient ID];
    UNION SELECT Patients.[Patient ID], [Patient Diary].Date
    FROM Patients LEFT JOIN [Patient Diary] ON Patients.[Patient ID] = [Patient Diary].[Patient ID];

    but now the query returns only one column for all the dates, although they come from different forms:

    Patient ID Date
    8006 23/06/2003
    8006 1/07/2003
    8006 1/08/2003
    8006 1/09/2003
    8006 3/09/2003
    8006 1/10/2003
    8006 9/10/2003
    8007
    8008 1/05/2003
    8008 26/05/2003

    whereas I'm trying to get each form's dates' into one column each. Sounds like it should be really easy, but I can't seem to code it properly.

    Thanks again for the help.

  4. #4
    Join Date
    Feb 2004
    Location
    Binary Universe
    Posts
    57
    Try 'SELECT DISTINCT ...'
    I won't byte... hard!

  5. #5
    Join Date
    Feb 2004
    Location
    Melbourne, Australia
    Posts
    3
    no luck on the SELECT DISTINCT idea - exactly the same output.

  6. #6
    Join Date
    Oct 2002
    Location
    Florida
    Posts
    7

    Lightbulb

    your table needs to be redesigned. Consider this;

    Three columns and your data would look like this;

    PatientID Form_Type Date_Completed

    008006 AQoL 23/06/2003
    008006 HSU 3/09/2003
    008006 HUI 23/06/2003
    008007 AQoL 29/05/2003
    008009 AQoL 5/09/2003
    008009 HUI 31/10/2003

    with the data stored this way I could run very simple aggregrate queries and give you any count or grouping you would possibly need.

    Also, since you are only storing data for events that have actually happened you cut down on database bloat and data integrity issues.

  7. #7
    Join Date
    Mar 2005
    Posts
    93
    It can be done with some code programming using a temporary table without a need of redesign the tables.

  8. #8
    Join Date
    Oct 2002
    Location
    Florida
    Posts
    7

    Post

    Quote Originally Posted by ThiNhan
    It can be done with some code programming using a temporary table without a need of redesign the tables.

    True, but they are in the development stage of this database, they might as well get it right...........now.

  9. #9
    Join Date
    Mar 2005
    Posts
    93
    It's over a year ago though!

Posting Permissions

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