Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Query List

    Hi,

    Is it possible to obtain certain records from a query without creating a
    query for each item?

    i.e.

    SELECT quoteprod_id, prod_id, descrip, cost AS info1,
    (SELECT quoteprod_id, prod_id, descrip, cost FROM products WHERE
    quoteprod_id = 1435) AS info2
    (SELECT quoteprod_id, prod_id, descrip, cost FROM products WHERE
    quoteprod_id = 1240 ) AS info3
    FROM products


    WHERE quoteprod_id = 1120

    instead of creating a record for each such as:

    SELECT quoteprod_id, prod_id, descrip, cost AS info1,
    FROM products
    WHERE quoteprod_id = 1120

    SELECT quoteprod_id, prod_id, descrip, cost AS info1,
    FROM products
    WHERE quoteprod_id = 1435

    SELECT quoteprod_id, prod_id, descrip, cost AS info1,
    FROM products
    WHERE quoteprod_id = 1240


    Thanks!

    Sanj

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Sanj,

    If you wanted to return records for just those three IDs:

    SELECT quoteprod_id, prod_id, descrip, cost AS info1,
    FROM products
    WHERE quoteprod_id = IN (1120, 1240, 1435)

    Or a range:

    SELECT quoteprod_id, prod_id, descrip, cost AS info1,
    FROM products
    WHERE quoteprod_id BETWEEN 1120 AND 1435

    Or meeting an inequality condition:

    SELECT quoteprod_id, prod_id, descrip, cost AS info1,
    FROM products
    WHERE quoteprod_id >= 1120

  3. #3
    Join Date
    Mar 2006
    Posts
    2
    Thanks Mathew for replying,

    My problem is with the results I need to conduct some VB sums to the result:

    i.e. for a single recordsets, I could:

    rsItemA
    SELECT quoteprod_id, prod_id, descrip, cost AS info1,
    FROM products
    WHERE quoteprod_id = 1120

    labcosts = Request.QueryString("bayqty") * (rsItemA.Fields.Item("cost").Value)


    rsItemB
    SELECT quoteprod_id, prod_id, descrip, cost AS info1,
    FROM products
    WHERE quoteprod_id = 1435

    matcosts = Request.QueryString("bayqty") * (rsItemB.Fields.Item("cost").Value)

    etc,

    I'm not sure whether it a better idea to use your idea and put the results into an array - is that possible?

    Regards,

    Sanj

  4. #4
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    sanj,

    What is it that you need to do? If you are doing sums in VB, why not let the SQL do the summations for you?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. I can't create a drop-down list box
    By cpuser in forum Access
    Replies: 4
    Last Post: 02-18-2008, 10:11 AM
  2. list box
    By lead27 in forum Forms
    Replies: 0
    Last Post: 07-21-2007, 04:09 AM
  3. adding list to a listbox
    By jetrow in forum Access
    Replies: 0
    Last Post: 08-15-2006, 03:36 AM
  4. saving a list box to access table
    By newguy357 in forum Forms
    Replies: 0
    Last Post: 05-05-2006, 12:22 PM
  5. Problems with a query in a list box
    By admaldo in forum Forms
    Replies: 1
    Last Post: 04-05-2006, 05:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums