Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1229
  • Last Modified:

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)

0
pennyb54
Asked:
pennyb54
  • 2
  • 2
2 Solutions
 
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
 
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:
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now