Results 1 to 4 of 4
  1. #1
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184

    Union and Group by/Distinct

    Morning all,



    So as the title suggests, I have a query (below) which should from all my tired eyes can see, work.
    However the error message i keep recieving is:
    Column 'Medicala6744.patid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Now before you ask, i have indeed tried as many combinations of GROUP BY, Distinct, Union, Union all etc.
    However i haven't found the answer to it yet...

    The query itself is as follows


    Code:
    /* Index date = date of first diagnosis of AF (cases) Controls will have the same
    index date as its matched case patient */
    Select * from
    (
    select distinct m.patid, m.medcode,MIN(m.eventdate)as index_date
    from Medicala6744 as m 
    inner join TargetPat as a 
    on a.patid=m.patid 
    where '20060501'<= m.eventdate and'20071130'>= m.eventdate
    Union all
    select distinct m.patid, m.medcode,MIN(m.eventdate)as index_date
    from Ahda6744 as m 
    inner join TargetPat as a 
    on a.patid=m.patid 
    where '20060501'<= m.eventdate and'20071130'>= m.eventdate
    ) z
    inner join AF as f on f.Field1=z.medcode
    Group by z.patid, z.medcode, z.index_date

    The code is supposed to:
    create an index_date field which is the date of the first eventdate medcode that matches AF.

    As far as im aware this is the best way to make this work...
    but obviously it doesn't

    If you have any questions, please ask.

    Many thanks

    Rixxe
    Last edited by Rixxe; 11-10-2010 at 09:46 AM.

  2. #2
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    As far as i am aware, this is the criteria for using UNION query:

    "The SQL UNION is used to combine the results of two or more SELECT SQL statements into a single result. All the statements concatenated with UNION must have the same structure. This means that they need to have the same number of columns, and corresponding columns must have the same or compatible data types (implicitly convertible to the same type or explicitly converted to the same type). The columns in each SELECT statement must be in exactly the same order too.

    The UNION operator removes by default duplicate rows from the result set. You have the option to use the ALL keyword after the UNION keyword, which will force all rows including duplicates to be returned in your result set."

    I've attempted to run the query with just UNION, and i still have the same problem. And as far as i can see, my query fits the criteria stated above (?)

  3. #3
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    You use Min() but you don't have GROUP BY, that's the error come from.

  4. #4
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    Ah, Thank you very much.

    Was doing my head in looking at that stupid query!

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

Similar Threads

  1. Access DB distinct count
    By anziga in forum Queries
    Replies: 3
    Last Post: 10-12-2010, 02:20 PM
  2. Need help for distinct...
    By gunapriyan in forum Queries
    Replies: 2
    Last Post: 05-28-2010, 12:18 AM
  3. Replies: 2
    Last Post: 11-18-2009, 06:49 PM
  4. columns for group detail but not group header?
    By Coolpapabell in forum Reports
    Replies: 0
    Last Post: 08-21-2009, 08:53 AM
  5. Distinct Values
    By Acramer8 in forum Reports
    Replies: 1
    Last Post: 06-15-2009, 08:37 AM

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