How to get using Query

P

Prabhat

Help need to get records from this Trans Table:

TransID CustomerName LicenseNo ProductID Price DtOfSale
1 Prabhat 0123456781 10 125
2 Prabhat 0123456782 20 123
3 Prabhat 0123456783 30 23
4 Prabhat 0123456784 10 234
5 Prabhat 0123456785 10 234
6 Raja 0123456786 10 234
7 Raja 0123456787 40 234
8 Raja 0123456788 30 345
9 Pramod 0123456789 40 234
10 Rajesh 0123456791 20 234
11 Rajesh 0123456792 30 2423
12 Aravinda 0123456793 10 123
13 Aravinda 0123456794 10 234
14 Aslam 0123456795 10 123
15 Sudheer 0123456796 20 123
16 Mohan 0123456797 30 1234
17 Mohan 0123456798 10 1234
18 Mohan 0123456790 10 234
19 Rakesh 0123456799 10 234
20 Susil 0123456711 20 234
21 Mohan 0123456712 40 123
-----------------------------------------------------------------------

How can I get the records with detals: "CustomerName, LicenseNo, DtOfSale"
those are having Multiple Copies (>1) of Product# 10 But Does not have
Product# 40.

Thanks
Prabhat
 
D

Douglas J Steele

One way is through a number of connected queries.

For the first query, return details of how many copies each customer has of
each product:

SELECT CustomerName, ProductId, Count(*) AS NumberOfCopies
FROM TransTable
GROUP BY CustomerName, ProductId

Base a second query on that first query to select all rows where the
customer has more than one copy of Product 10, or the customer has at least
one copy of Product 40:

SELECT CustomerName, ProductID, NumberOfCopies
FROM Query1
WHERE (ProductID = 10 And NumberOfCopies > 1)
OR ProductID = 40

Finally, select those customers that only appear once in the second query:

SELECT CustomerName
FROM Query2
GROUP BY CustomerName
HAVING Count(*) = 1

Now, it's possible to combine all that into a single query, but I'm afraid
I've only got Access 97 here, and you can't do it in Access 97. Off the top
of my head, I'd expect the single query to look something like:

SELECT Q2.CustomerName
FROM
(SELECT Q1.CustomerName, Q1.ProductID, Q1.NumberOfCopies
FROM
(SELECT CustomerName, ProductID, Count(*) AS NumberOfCopies
FROM TransTable
GROUP BY CustomerName, ProductiD) AS Q1
WHERE (Q1.ProductID = 10 And Q1.NumberOfCopies > 1)
OR (Q1.ProductID = 40)) AS Q2
GROUP BY Q2.CustomerName
HAVING Count(*) = 1
 
P

Prabhat

Hi Douglas,

Thanks for the reply. I liked the concept of the Multiple query and one
referring other, But the query that you suggested does not give the qctual
output. I will try to get using multiple query. And Also I need multiple
columns as Final Output not only the CustName.

Thanks
Prabhat
 
D

Douglas J Steele

Join the results of the query back to your main table. There's no other
alternative.
 
Top