Solved

# MS query functions count

Posted on 2007-10-03
1,204 Views
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
Question by:pennyb54

LVL 5

Expert Comment

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

LVL 119

Expert Comment

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

LVL 119

Accepted Solution

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

LVL 5

Assisted Solution

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…