Welcome to SPN

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

Sign Up Now!

Help - Trying to Combine 3 Queries into 1

Discussion in 'Information Technology' started by Anonymous, Oct 28, 2005.

  1. Anonymous

    Anonymous
    Expand Collapse
    Guest

    Hi everyone,

    This newsgroup has been very helpful in the past so I'm hoping one of the
    smart folks who hang out here can lend a hand. I have 2 tables:

    tOrders - a table with our order information, PO numbers, etc.
    tRefVendors - a reference table with some stats about our vendors, whether
    they are a finished maker or not, etc.

    I'm running a query to determine how many POs we have open with each vendor
    I'm sorting it in descending order. I also show whether that vendor is a
    finished maker by tieing into the tRefVendors table. Easy enough.
    Recently, I was asked to show for each vendor total, how many orders are for
    parts (determined by an 'OE' in the PO number) and how many are for finished
    goods (no OE in PO number). This forced me to start by making a subquery
    for Parts and a subquery for FinishedGoods. I got it working fine this way,
    but I'd really like to have these exist as 1 query. I've made it pretty
    far, but for some reason I can't seem to get the Parts and FinishedGoods
    columns to display the correct count for the associated vendor. The linking
    item is VendorID. In the code below, I've hard coded the vendor ID to 3.
    That means when I run the query, I see the results for vendor id 3 all the
    way down the page on the last 2 columns. I hard coded 3 on purposed for dev
    testing. Can't seem to get it to dynamically link the vendor ID for each
    row to get the correct corresponding value for Finished Goods and Parts. I
    need those 2 columns to show the correct totals for the vendor in each given
    row.

    Example of what I'm seeing now:

    VendorID Vendor Finished Maker? # of POs Finished Goods Parts

    1 JBL, Inc. Yes 9
    3 4
    2 CTJ, Inc. Yes 8
    3 4
    3 TTY, Inc. Yes 7
    3 4
    4 JXS Co. No 9
    3 4


    What I need to see:

    VendorID Vendor Finished Maker? # of POs Finished Goods Parts

    1 JBL, Inc. Yes 9
    6 3
    2 CTJ, Inc. Yes 8
    4 4
    3 TTY, Inc. Yes 7
    3 4
    4 JXS Co. No 9
    5 4

    Finished Goods + Parts = # of POs correctly on each row.

    Here's the current code. Any help would be greatly appreciated! :) Thank
    You!

    SELECT tOrders.VendorID, tRefVendors.Vendor, tRefVendors.FinishedMaker AS
    [Finished Maker?], Count(tRefVendors.Vendor) AS [# of POs], (SELECT
    Count(tOrders.PONum) AS FinishedGoods
    FROM tRefVendors INNER JOIN tOrders ON tRefVendors.VendorID =
    tOrders.VendorID
    WHERE (((tRefVendors.VendorID)=[tOrders].[VendorID]) AND ((tOrders.PONum)
    Not Like "*oe*") AND ((tOrders.OrderClosed)=False) AND
    ((tOrders.OrderClosedDate) Is Null))
    GROUP BY tOrders.VendorID, tRefVendors.Vendor
    HAVING (((tOrders.VendorID)=3));) AS FinishedGoods, (SELECT
    Count(tOrders.PONum) AS Parts
    FROM tRefVendors INNER JOIN tOrders ON tRefVendors.VendorID =
    tOrders.VendorID
    WHERE (((tRefVendors.VendorID)=[tOrders].[VendorID]) AND ((tOrders.PONum)
    Like "*oe*") AND ((tOrders.OrderClosed)=False) AND
    ((tOrders.OrderClosedDate) Is Null))
    GROUP BY tOrders.VendorID, tRefVendors.Vendor
    HAVING (((tOrders.VendorID)=3));) AS Parts
    FROM tRefVendors INNER JOIN tOrders ON tRefVendors.VendorID =
    tOrders.VendorID
    GROUP BY tOrders.VendorID, tRefVendors.Vendor, tRefVendors.FinishedMaker,
    tOrders.OrderClosed, tOrders.OrderClosedDate
    HAVING (((tOrders.OrderClosed)=False) AND ((tOrders.OrderClosedDate) Is
    Null))
    ORDER BY Count(tRefVendors.Vendor) DESC;
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Anonymous" <someone@somewhere.local> wrote in message
    news:Ws-dnVPOUvcIB_zeRVn-sA@comcast.com
    > Hi everyone,
    >
    > This newsgroup has been very helpful in the past so I'm hoping one of
    > the smart folks who hang out here can lend a hand. I have 2 tables:
    >
    > tOrders - a table with our order information, PO numbers, etc.
    > tRefVendors - a reference table with some stats about our vendors,
    > whether they are a finished maker or not, etc.
    >
    > I'm running a query to determine how many POs we have open with each
    > vendor I'm sorting it in descending order. I also show whether that
    > vendor is a finished maker by tieing into the tRefVendors table.
    > Easy enough. Recently, I was asked to show for each vendor total, how
    > many orders are for parts (determined by an 'OE' in the PO number)
    > and how many are for finished goods (no OE in PO number). This
    > forced me to start by making a subquery for Parts and a subquery for
    > FinishedGoods. I got it working fine this way, but I'd really like
    > to have these exist as 1 query. I've made it pretty far, but for
    > some reason I can't seem to get the Parts and FinishedGoods columns
    > to display the correct count for the associated vendor. The linking
    > item is VendorID. In the code below, I've hard coded the vendor ID
    > to 3. That means when I run the query, I see the results for vendor
    > id 3 all the way down the page on the last 2 columns. I hard coded 3
    > on purposed for dev testing. Can't seem to get it to dynamically
    > link the vendor ID for each row to get the correct corresponding
    > value for Finished Goods and Parts. I need those 2 columns to show
    > the correct totals for the vendor in each given row.
    >
    > Example of what I'm seeing now:
    >
    > VendorID Vendor Finished Maker? # of POs Finished Goods
    > Parts
    >
    > 1 JBL, Inc. Yes 9
    > 3 4
    > 2 CTJ, Inc. Yes 8
    > 3 4
    > 3 TTY, Inc. Yes 7
    > 3 4
    > 4 JXS Co. No 9
    > 3 4
    >
    >
    > What I need to see:
    >
    > VendorID Vendor Finished Maker? # of POs Finished Goods
    > Parts
    >
    > 1 JBL, Inc. Yes 9
    > 6 3
    > 2 CTJ, Inc. Yes 8
    > 4 4
    > 3 TTY, Inc. Yes 7
    > 3 4
    > 4 JXS Co. No 9
    > 5 4
    >
    > Finished Goods + Parts = # of POs correctly on each row.
    >
    > Here's the current code. Any help would be greatly appreciated! :)
    > Thank You!
    >
    > SELECT tOrders.VendorID, tRefVendors.Vendor,
    > tRefVendors.FinishedMaker AS [Finished Maker?],
    > Count(tRefVendors.Vendor) AS [# of POs], (SELECT Count(tOrders.PONum)
    > AS FinishedGoods
    > FROM tRefVendors INNER JOIN tOrders ON tRefVendors.VendorID =
    > tOrders.VendorID
    > WHERE (((tRefVendors.VendorID)=[tOrders].[VendorID]) AND
    > ((tOrders.PONum) Not Like "*oe*") AND ((tOrders.OrderClosed)=False)
    > AND ((tOrders.OrderClosedDate) Is Null))
    > GROUP BY tOrders.VendorID, tRefVendors.Vendor
    > HAVING (((tOrders.VendorID)=3));) AS FinishedGoods, (SELECT
    > Count(tOrders.PONum) AS Parts
    > FROM tRefVendors INNER JOIN tOrders ON tRefVendors.VendorID =
    > tOrders.VendorID
    > WHERE (((tRefVendors.VendorID)=[tOrders].[VendorID]) AND
    > ((tOrders.PONum) Like "*oe*") AND ((tOrders.OrderClosed)=False) AND
    > ((tOrders.OrderClosedDate) Is Null))
    > GROUP BY tOrders.VendorID, tRefVendors.Vendor
    > HAVING (((tOrders.VendorID)=3));) AS Parts
    > FROM tRefVendors INNER JOIN tOrders ON tRefVendors.VendorID =
    > tOrders.VendorID
    > GROUP BY tOrders.VendorID, tRefVendors.Vendor,
    > tRefVendors.FinishedMaker, tOrders.OrderClosed,
    > tOrders.OrderClosedDate
    > HAVING (((tOrders.OrderClosed)=False) AND ((tOrders.OrderClosedDate)
    > Is Null))
    > ORDER BY Count(tRefVendors.Vendor) DESC;


    I think you want something like this:

    SELECT
    tOrders.VendorID,
    First(tRefVendors.Vendor) AS Vendor,
    First(tRefVendors.FinishedMaker) AS [Finished Maker?],
    Count(*) AS [# of POs],
    Sum(IIf(tOrders.PONum Like "*OE*", 1, 0))
    AS FinishedGoods,
    Sum(IIf(tOrders.PONum Like "*OE*", 0, 1))
    AS Parts,
    FROM
    tRefVendors
    INNER JOIN
    tOrders
    ON
    tRefVendors.VendorID = tOrders.VendorID
    WHERE
    (tOrders.OrderClosed=False) AND
    (tOrders.OrderClosedDate Is Null)
    GROUP BY
    tOrders.VendorID
    ORDER BY
    Count(*) DESC;

    That's just "air SQL", but give it a try and tell us how it comes out.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  4. Anonymous

    Anonymous
    Expand Collapse
    Guest

    THANK YOU! I will give it a try at the client's site tomorrow and report
    back.

    :)


    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:uJvZpP32FHA.3420@TK2MSFTNGP15.phx.gbl...
    > "Anonymous" <someone@somewhere.local> wrote in message
    > news:Ws-dnVPOUvcIB_zeRVn-sA@comcast.com
    >> Hi everyone,
    >>
    >> This newsgroup has been very helpful in the past so I'm hoping one of
    >> the smart folks who hang out here can lend a hand. I have 2 tables:
    >>
    >> tOrders - a table with our order information, PO numbers, etc.
    >> tRefVendors - a reference table with some stats about our vendors,
    >> whether they are a finished maker or not, etc.
    >>
    >> I'm running a query to determine how many POs we have open with each
    >> vendor I'm sorting it in descending order. I also show whether that
    >> vendor is a finished maker by tieing into the tRefVendors table.
    >> Easy enough. Recently, I was asked to show for each vendor total, how
    >> many orders are for parts (determined by an 'OE' in the PO number)
    >> and how many are for finished goods (no OE in PO number). This
    >> forced me to start by making a subquery for Parts and a subquery for
    >> FinishedGoods. I got it working fine this way, but I'd really like
    >> to have these exist as 1 query. I've made it pretty far, but for
    >> some reason I can't seem to get the Parts and FinishedGoods columns
    >> to display the correct count for the associated vendor. The linking
    >> item is VendorID. In the code below, I've hard coded the vendor ID
    >> to 3. That means when I run the query, I see the results for vendor
    >> id 3 all the way down the page on the last 2 columns. I hard coded 3
    >> on purposed for dev testing. Can't seem to get it to dynamically
    >> link the vendor ID for each row to get the correct corresponding
    >> value for Finished Goods and Parts. I need those 2 columns to show
    >> the correct totals for the vendor in each given row.
    >>
    >> Example of what I'm seeing now:
    >>
    >> VendorID Vendor Finished Maker? # of POs Finished Goods
    >> Parts
    >>
    >> 1 JBL, Inc. Yes 9
    >> 3 4
    >> 2 CTJ, Inc. Yes 8
    >> 3 4
    >> 3 TTY, Inc. Yes 7
    >> 3 4
    >> 4 JXS Co. No 9
    >> 3 4
    >>
    >>
    >> What I need to see:
    >>
    >> VendorID Vendor Finished Maker? # of POs Finished Goods
    >> Parts
    >>
    >> 1 JBL, Inc. Yes 9
    >> 6 3
    >> 2 CTJ, Inc. Yes 8
    >> 4 4
    >> 3 TTY, Inc. Yes 7
    >> 3 4
    >> 4 JXS Co. No 9
    >> 5 4
    >>
    >> Finished Goods + Parts = # of POs correctly on each row.
    >>
    >> Here's the current code. Any help would be greatly appreciated! :)
    >> Thank You!
    >>
    >> SELECT tOrders.VendorID, tRefVendors.Vendor,
    >> tRefVendors.FinishedMaker AS [Finished Maker?],
    >> Count(tRefVendors.Vendor) AS [# of POs], (SELECT Count(tOrders.PONum)
    >> AS FinishedGoods
    >> FROM tRefVendors INNER JOIN tOrders ON tRefVendors.VendorID =
    >> tOrders.VendorID
    >> WHERE (((tRefVendors.VendorID)=[tOrders].[VendorID]) AND
    >> ((tOrders.PONum) Not Like "*oe*") AND ((tOrders.OrderClosed)=False)
    >> AND ((tOrders.OrderClosedDate) Is Null))
    >> GROUP BY tOrders.VendorID, tRefVendors.Vendor
    >> HAVING (((tOrders.VendorID)=3));) AS FinishedGoods, (SELECT
    >> Count(tOrders.PONum) AS Parts
    >> FROM tRefVendors INNER JOIN tOrders ON tRefVendors.VendorID =
    >> tOrders.VendorID
    >> WHERE (((tRefVendors.VendorID)=[tOrders].[VendorID]) AND
    >> ((tOrders.PONum) Like "*oe*") AND ((tOrders.OrderClosed)=False) AND
    >> ((tOrders.OrderClosedDate) Is Null))
    >> GROUP BY tOrders.VendorID, tRefVendors.Vendor
    >> HAVING (((tOrders.VendorID)=3));) AS Parts
    >> FROM tRefVendors INNER JOIN tOrders ON tRefVendors.VendorID =
    >> tOrders.VendorID
    >> GROUP BY tOrders.VendorID, tRefVendors.Vendor,
    >> tRefVendors.FinishedMaker, tOrders.OrderClosed,
    >> tOrders.OrderClosedDate
    >> HAVING (((tOrders.OrderClosed)=False) AND ((tOrders.OrderClosedDate)
    >> Is Null))
    >> ORDER BY Count(tRefVendors.Vendor) DESC;

    >
    > I think you want something like this:
    >
    > SELECT
    > tOrders.VendorID,
    > First(tRefVendors.Vendor) AS Vendor,
    > First(tRefVendors.FinishedMaker) AS [Finished Maker?],
    > Count(*) AS [# of POs],
    > Sum(IIf(tOrders.PONum Like "*OE*", 1, 0))
    > AS FinishedGoods,
    > Sum(IIf(tOrders.PONum Like "*OE*", 0, 1))
    > AS Parts,
    > FROM
    > tRefVendors
    > INNER JOIN
    > tOrders
    > ON
    > tRefVendors.VendorID = tOrders.VendorID
    > WHERE
    > (tOrders.OrderClosed=False) AND
    > (tOrders.OrderClosedDate Is Null)
    > GROUP BY
    > tOrders.VendorID
    > ORDER BY
    > Count(*) DESC;
    >
    > That's just "air SQL", but give it a try and tell us how it comes out.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  5. Anonymous

    Anonymous
    Expand Collapse
    Guest

    This worked like a champ! Thanks!

    One weird thing is I'm trying to run this query via an ASP page too. ASP
    does not support the IIF function. There is a workaround, but it doesn't
    like it. Is there an alternative way to write this query without the IIF?
    If not, no problem. You were a HUGE help!


    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:uJvZpP32FHA.3420@TK2MSFTNGP15.phx.gbl...
    > "Anonymous" <someone@somewhere.local> wrote in message
    > news:Ws-dnVPOUvcIB_zeRVn-sA@comcast.com
    >> Hi everyone,
    >>
    >> This newsgroup has been very helpful in the past so I'm hoping one of
    >> the smart folks who hang out here can lend a hand. I have 2 tables:
    >>
    >> tOrders - a table with our order information, PO numbers, etc.
    >> tRefVendors - a reference table with some stats about our vendors,
    >> whether they are a finished maker or not, etc.
    >>
    >> I'm running a query to determine how many POs we have open with each
    >> vendor I'm sorting it in descending order. I also show whether that
    >> vendor is a finished maker by tieing into the tRefVendors table.
    >> Easy enough. Recently, I was asked to show for each vendor total, how
    >> many orders are for parts (determined by an 'OE' in the PO number)
    >> and how many are for finished goods (no OE in PO number). This
    >> forced me to start by making a subquery for Parts and a subquery for
    >> FinishedGoods. I got it working fine this way, but I'd really like
    >> to have these exist as 1 query. I've made it pretty far, but for
    >> some reason I can't seem to get the Parts and FinishedGoods columns
    >> to display the correct count for the associated vendor. The linking
    >> item is VendorID. In the code below, I've hard coded the vendor ID
    >> to 3. That means when I run the query, I see the results for vendor
    >> id 3 all the way down the page on the last 2 columns. I hard coded 3
    >> on purposed for dev testing. Can't seem to get it to dynamically
    >> link the vendor ID for each row to get the correct corresponding
    >> value for Finished Goods and Parts. I need those 2 columns to show
    >> the correct totals for the vendor in each given row.
    >>
    >> Example of what I'm seeing now:
    >>
    >> VendorID Vendor Finished Maker? # of POs Finished Goods
    >> Parts
    >>
    >> 1 JBL, Inc. Yes 9
    >> 3 4
    >> 2 CTJ, Inc. Yes 8
    >> 3 4
    >> 3 TTY, Inc. Yes 7
    >> 3 4
    >> 4 JXS Co. No 9
    >> 3 4
    >>
    >>
    >> What I need to see:
    >>
    >> VendorID Vendor Finished Maker? # of POs Finished Goods
    >> Parts
    >>
    >> 1 JBL, Inc. Yes 9
    >> 6 3
    >> 2 CTJ, Inc. Yes 8
    >> 4 4
    >> 3 TTY, Inc. Yes 7
    >> 3 4
    >> 4 JXS Co. No 9
    >> 5 4
    >>
    >> Finished Goods + Parts = # of POs correctly on each row.
    >>
    >> Here's the current code. Any help would be greatly appreciated! :)
    >> Thank You!
    >>
    >> SELECT tOrders.VendorID, tRefVendors.Vendor,
    >> tRefVendors.FinishedMaker AS [Finished Maker?],
    >> Count(tRefVendors.Vendor) AS [# of POs], (SELECT Count(tOrders.PONum)
    >> AS FinishedGoods
    >> FROM tRefVendors INNER JOIN tOrders ON tRefVendors.VendorID =
    >> tOrders.VendorID
    >> WHERE (((tRefVendors.VendorID)=[tOrders].[VendorID]) AND
    >> ((tOrders.PONum) Not Like "*oe*") AND ((tOrders.OrderClosed)=False)
    >> AND ((tOrders.OrderClosedDate) Is Null))
    >> GROUP BY tOrders.VendorID, tRefVendors.Vendor
    >> HAVING (((tOrders.VendorID)=3));) AS FinishedGoods, (SELECT
    >> Count(tOrders.PONum) AS Parts
    >> FROM tRefVendors INNER JOIN tOrders ON tRefVendors.VendorID =
    >> tOrders.VendorID
    >> WHERE (((tRefVendors.VendorID)=[tOrders].[VendorID]) AND
    >> ((tOrders.PONum) Like "*oe*") AND ((tOrders.OrderClosed)=False) AND
    >> ((tOrders.OrderClosedDate) Is Null))
    >> GROUP BY tOrders.VendorID, tRefVendors.Vendor
    >> HAVING (((tOrders.VendorID)=3));) AS Parts
    >> FROM tRefVendors INNER JOIN tOrders ON tRefVendors.VendorID =
    >> tOrders.VendorID
    >> GROUP BY tOrders.VendorID, tRefVendors.Vendor,
    >> tRefVendors.FinishedMaker, tOrders.OrderClosed,
    >> tOrders.OrderClosedDate
    >> HAVING (((tOrders.OrderClosed)=False) AND ((tOrders.OrderClosedDate)
    >> Is Null))
    >> ORDER BY Count(tRefVendors.Vendor) DESC;

    >
    > I think you want something like this:
    >
    > SELECT
    > tOrders.VendorID,
    > First(tRefVendors.Vendor) AS Vendor,
    > First(tRefVendors.FinishedMaker) AS [Finished Maker?],
    > Count(*) AS [# of POs],
    > Sum(IIf(tOrders.PONum Like "*OE*", 1, 0))
    > AS FinishedGoods,
    > Sum(IIf(tOrders.PONum Like "*OE*", 0, 1))
    > AS Parts,
    > FROM
    > tRefVendors
    > INNER JOIN
    > tOrders
    > ON
    > tRefVendors.VendorID = tOrders.VendorID
    > WHERE
    > (tOrders.OrderClosed=False) AND
    > (tOrders.OrderClosedDate Is Null)
    > GROUP BY
    > tOrders.VendorID
    > ORDER BY
    > Count(*) DESC;
    >
    > That's just "air SQL", but give it a try and tell us how it comes out.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  6. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Anonymous" <someone@somewhere.local> wrote in message
    news:_JGdnX85MN3u_PreRVn-tg@comcast.com
    > This worked like a champ! Thanks!
    >
    > One weird thing is I'm trying to run this query via an ASP page too.
    > ASP does not support the IIF function. There is a workaround, but it
    > doesn't like it. Is there an alternative way to write this query
    > without the IIF? If not, no problem. You were a HUGE help!


    You could try this -- I'm not sure if it will work or not:

    SELECT
    tOrders.VendorID,
    First(tRefVendors.Vendor) AS Vendor,
    First(tRefVendors.FinishedMaker) AS [Finished Maker?],
    Count(*) AS [# of POs],
    Abs(Sum((tOrders.PONum Like '*OE*')))
    AS FinishedGoods,
    Abs(Sum((tOrders.PONum Not Like '*OE*')))
    AS Parts,
    FROM
    tRefVendors
    INNER JOIN
    tOrders
    ON
    tRefVendors.VendorID = tOrders.VendorID
    WHERE
    (tOrders.OrderClosed=False) AND
    (tOrders.OrderClosedDate Is Null)
    GROUP BY
    tOrders.VendorID
    ORDER BY
    Count(*) DESC;

    If that doesn't work, there's a further workaround to try.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     

Share This Page