Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Unanswered: Quick Access Query Question - I think?

    I am trying to design a query to identify instances when, for every instance of the data in Field1, the data in Field2 field varies. Both fields can contain duplicated data. For example with the following data:

    Data_Ref......Field1.......Field 2
    12345.............1.............1
    45725.............1.............2
    15252.............1.............2
    25128.............2.............1
    25684.............2.............2
    10528.............2.............1
    ...
    45635.............31.............2
    56745.............31.............2
    67856.............31.............2

    25125.............35.............1
    34562.............35.............1
    56453.............35.............2
    56746.............35.............3

    Data_Ref field is a primary field - no duplicates.

    I would like the query to return the records containing 1,2 and 35 in Field1 but not 31 (because for all values of 31 in Field1, the values in Field2 are identical).

    I would be very grateful for any pointers as to how I could do this as I have thousands of records in a table to run this on.

    Thank you in advance

    MC1-1

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT Data_Ref, Field1
    FROM (SELECT DISTINCT Data_Ref, Field1, Field2 FROM myTable) AS T
    GROUP BY Data_Ref, Field1
    HAVING COUNT(*) > 1
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2010
    Posts
    3

    Error in Access 2003

    Thank you for your very prompt response. I copied the code into Access 2003 which changed it to the following:

    SELECT Data_Ref, Field1
    FROM [SELECT DISTINCT Data_Ref, Field1, Field2 FROM myTable; ] AS T
    GROUP BY Data_Ref, Field1
    HAVING COUNT(*)>1;

    This gives an error as Access is looking for the table named between the square brackets.

    Also, I think this might give no records as Data_Ref is a unique number in all records?

    I have tried to adapt the query but with no success

    It might help to explain further - I require a report of all the records where Field1 has varying Field2's and ideally the report would give all the records where this occurs - i.e the report would produce 10 records in my example (total 13 records less the 3 records with 31 in Field1).

    Thank you again

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why did you replace parentheses by square brackets in the code pootle flump provided?
    Have a nice day!

  5. #5
    Join Date
    Jul 2010
    Posts
    3

    Access changed the barckets

    Access changed the brackets in Query SQL window when I exited. Have I misunderstood the instructions?

Posting Permissions

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