Results 1 to 10 of 10
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065

    Duplication even with Group by

    I have a query similar to



    Select a.name, b.appt_date,c.provider
    from dbo.patient a inner join dbo.appointments b on a.memberid = b.memberid
    inner join dbo.providers c on b.providerid = c.providerid
    where b.appt_date between '03/01/2019' and '02/29/2020'
    and b.status = 'Missed'
    Group by a.name, b.appt_date, c.provider


    In the results I'm getting what I asked for but I'm also getting something like this

    a.name b.appt_date c.provider
    John smith 01/03/2020 Dr Jones
    John Smith 01/03/2020 Dr Jones


    What I'm finding is that in the appointments table There may be multiple records for John Smith for that Appointment date with that provider but the Appt_id may be different. Probably someone entered the same appointment twice. I would think that would not be allowed as this is a commercial Application but that's what I'm finding. The question is since Appt_ID is not part of my query why aren't those records grouped into 1 Record?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    where is the data coming from? Access? SQL Server? I suspect the latter since a) your from clause wont work in access and b) access is not case sensitive whereas sql server is - and your example result shows a difference in case on the name.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Also make sure there's not a time component in your date values.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    The query is in access. The data is on 2 separate SQLServers. There is not the time is in another field called appt_time. I wondered if that could be a cause but I'm not using the time field in my query. Per the users instructions.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    re the time field, you can test the hypothesis by in your query changing the format property to general date.

    Otherwise, back to my suggestion - is the query a passthrough query or 'normal' one one working on linked access tables? I suspect the former

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    What about:
    Code:
    ;WITH UniqueApp AS
    (SELECT DISTINCT appt_date, memberid, status FROM dbo.Appointments WHERE appt_date between '03/01/2019' and '02/29/2020 AND status= 'Missed')
    SELECT a.name, b.appt_date,c.provider
    from dbo.patient a inner join UniqueApp b on a.memberid = b.memberid
    inner join dbo.providers c on b.providerid = c.providerid
    Group by a.name, b.appt_date, c.provider
    
    Of-course there remains the question, what about case where from 2 entries for appointments on same day one is set 'Missed', and another not! When there is no way to have 2 appointments on same day, then I advice to set an unique index for table Appointments composed from fields appt_date and memberid. Otherwise at least let entry form to check for double entry, and when found, then give a warning (and both entries must be then visible in entry form). Or - e.g. in case you can't edit SQL database - you periodically run a query, which returns a list of multiple entries, and you somehow handle this in SQL database (manually deleting abundant entries, or running a delete query based on data you get from 1st one).

  7. #7
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    It is a normal one using linked SQL Server Tables

  8. #8
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    The SQL I posted was a sample not the complete query the Database is a Commercial Database and due to company policy I cannot post the actual query. What I posted was just a sample based on the request. It unheard of for a Patient to have 2 separate Appointments with Doctor's in our clinic. We only have PCP's in our clinic. That's why the time is irrelevant for this query

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    How complex the final query is is moot unless you are at limits for perfomance.

    With clause creates a subquery which is run at start. You use the recordset returned by this subquery like any regular table. As I moved all WHERE clauses for appointments table into subquery, then returned recordset is limited only to essential records - i.e. it is minimized.

    Of course, WITH clause doesn't exist in MS Access, so you have to use pass-through query to use it. In Access, you can use instead the syntax like
    Code:
    SELECT a.name, b.appt_date,c.provider
    from dbo.patient a inner join (SELECT DISTINCT b0.appt_date, b0.memberid, b0.status FROM dbo.Appointments b0 WHERE b0.appt_date between '03/01/2019' and '02/29/2020 AND b0.status= 'Missed') b on a.memberid = b.memberid
    inner join dbo.providers c on b.providerid = c.providerid 
    Group by a.name, b.appt_date, c.provider
    , but I'm afraid this will be slower (optimizing complex queries is not a strong point for MS Office).

  10. #10
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    This is solved. Found the problem. There was one field that caused the duplicates. Took some time but I went through the data and removed any field that had no data in the results. Even though they specifically requested those fields. Did it one at a time so if it wasn't a problem I could put it back immediately I found 1 field that sometimes was blank and sometimes was null. After removing that field all of the duplicates were eliminated. Thanks for all your help.

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

Similar Threads

  1. Duplication in listbox
    By Deepak.Doddagoudar in forum Forms
    Replies: 9
    Last Post: 04-10-2018, 07:35 PM
  2. Query Duplication
    By dancoe2004 in forum Queries
    Replies: 2
    Last Post: 08-24-2017, 06:44 PM
  3. duplication problem
    By pdcc in forum Reports
    Replies: 1
    Last Post: 07-16-2011, 09:57 AM
  4. Duplication in a report
    By newtoAccess in forum Reports
    Replies: 9
    Last Post: 03-05-2011, 05:41 AM
  5. duplication
    By noidea in forum Access
    Replies: 1
    Last Post: 07-31-2009, 06:22 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