Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How to get using Query

Discussion in 'Information Technology' started by Prabhat, Jul 28, 2006.

Tags:
  1. Prabhat

    Prabhat
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Prabhat" <not_a_mail@hotmail.com> wrote in message
    news:ergeAM6jGHA.4512@TK2MSFTNGP04.phx.gbl...
    > 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
    >
    >
    >
     
  4. Prabhat

    Prabhat
    Expand Collapse
    Guest

    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

    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:uYqnxb6jGHA.3780@TK2MSFTNGP03.phx.gbl...
    > 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
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

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

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Prabhat" <not_a_mail@hotmail.com> wrote in message
    news:ejt2dr6jGHA.4104@TK2MSFTNGP04.phx.gbl...
    > 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
    >
    > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:uYqnxb6jGHA.3780@TK2MSFTNGP03.phx.gbl...
    > > 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
    > >

    >
    >
     
  6. Prabhat

    Prabhat
    Expand Collapse
    Guest

    OK. I will try to get it done.
    Thanks
    Prabhat

    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:#y5ZNQ7jGHA.3816@TK2MSFTNGP02.phx.gbl...
    > Join the results of the query back to your main table. There's no other
    > alternative.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
     

Share This Page