Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Query (2007)

    I want to show records with duplicate invoices numbers that have certain transaction codes. Say I have 3 invoices with different transaction codes of "I", "AE" and "AL'. I only want duplicates to show if "AE" and "AL" have the same invoice number not if "I" and "AE" are duplicates. Right now my code allows the "AE" to show because it is a duplicate of "I" even though "I" doesn't show because of the filter. Hope I'm making sense. Here is my current code.

    SELECT [EDI Data].[Invoice Number], [EDI Data].[Supplier ID], [EDI Data].[Supplier Name], [EDI Data].[Dealer ID], [EDI Data].[Dealer Name], [EDI Data].[Invoice Date Month], [EDI Data].[Invoice Date Day], [EDI Data].[Invoice Date Year], [EDI Data].[Dealer PO#], [EDI Data].<transaction Type>, [EDI Data].[Gross Purchases], [EDI Data].[Freight Parts and Warranties], [EDI Data].[Unqualified Purchases], [EDI Data].[Rebatable Purchases], [EDI Data].[Member Rebate], [EDI Data].Time_Stamp
    FROM [EDI Data]
    WHERE ((([EDI Data].[Invoice Number]) In (SELECT [Invoice Number] FROM [EDI Data] As Tmp GROUP BY [Invoice Number] HAVING Count(*)>1 )) AND (([EDI Data].<!t>[Transaction Type])<>"I"))
    ORDER BY [EDI Data].[Invoice Number];

    Thanks for any help on this.

    Kent

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Query (2007)

    Are I, AE and AL the only possible transaction codes? If not, are there other, similar "rules" that should be taken into account?

  3. #3
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Query (2007)

    The only other transaction code would be "C"

    Thanks,

    Kent

  4. #4
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Query (2007)

    And yes, "C" should be treated like "I". Sorry for not including that.

    Thanks,

    Kent

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Query (2007)

    Try changing the WHERE clause to

    WHERE [EDI Data].[Invoice Number] In (SELECT [Invoice Number] FROM [EDI Data] WHERE [EDI Data].<!t>[Transaction Type] In ("AE","AL") GROUP BY [Invoice Number] HAVING Count(*)>1) AND [EDI Data].<!t>[Transaction Type] In ("AE","AL")

    See attached demo. I replaced all other fields with a single one named Other.
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Query (2007)

    That is exactly what I needed. Works great!

    Thanks Hans!

    Kent

Posting Permissions

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