MS query functions count

I am trying to write a query that will count and query the following table. Select only records where either the customer has more than 2 transaction or transaction greater than 1000.  I am using MS query and Access.

Data

 Cust #      Cust name       trans amt
1      cust1       (34.86)
1      cust1       (174.23)
2      cust2       (876.84)
2      cust2       (508.99)
2      cust2       (447.36)
2      cust2       (719.60)
2      cust2       (248.97)
2      cust2       (16.50)
3      cust3       (541.62)
4      cust4       (1,266.00)
4      cust4       (508.99)
4      cust4       (396.21)
4      cust4       (1,081.14)
4      cust4       (158.63)
4      cust4       (1,306.21)
5      cust5       (282.96)
6      cust6       (650.17)


expected result :.      

Cust #      Cust name       trans amt
1      cust1       (34.86)
1      cust1       (174.23)
2      cust2       (876.84)
2      cust2       (508.99)
2      cust2       (447.36)
2      cust2       (719.60)
2      cust2       (248.97)
2      cust2       (16.50)
4      cust4       (1,266.00)
4      cust4       (508.99)
4      cust4       (396.21)
4      cust4       (1,081.14)
4      cust4       (158.63)
4      cust4       (1,306.21)

pennyb54Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
typo

SELECT [Cust #], [cust name], [trans Amt]
FROM TableX
WHERE (((TableX.CustName) In (SELECT [Cust #] FROM [TableX] As Tmp GROUP BY [Cust #] HAVING Count(*)>1 ))) OR (((TableX.[trans Amt])>1000))
ORDER BY [Cust #];
0
 
mrihmCommented:
This should give you the desired result.

select [cust #], [cust name], [trans amt]
   from table
  where [cust #] IN
             (select [cust #] from table
              group by [cust #]
             having COUNT([cust #] >= 2
             UNION
             select [cust #] from table
             where [trans amt] > 1000)

The first query of the union query gives you all the cust # that have two or more transactions and the second query of the union query gives you all cust # that have a transaction > 1000. You can then use the result of that union query as the input for the IN clause to get the final result.
0
 
Rey Obrero (Capricorn1)Commented:
SELECT [Cust #], [cust name], [trans Amt]
FROM TableX
WHERE (((TableX.CustName) In (SELECT [Cust #] FROM [TableX] As Tmp GROUP BY [Cust #] HAVING Count(*)>1 ))) OR (((TableX.transAmt)>1000))
ORDER BY [Cust #];
0
 
mrihmCommented:
Sorry, forgot to add the closing parenthesis for the COUNT function:

select [cust #], [cust name], [trans amt]
   from table
  where [cust #] IN
             (select [cust #] from table
              group by [cust #]
             having COUNT([cust #]) >= 2
             UNION
             select [cust #] from table
             where [trans amt] > 1000)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.