Help - Trying to Combine 3 Queries into 1

A

Anonymous

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;
 
D

Dirk Goldgar

Anonymous said:
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;
Click to expand...

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.
 
A

Anonymous

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

:)


Dirk Goldgar said:
Anonymous said:
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;
Click to expand...

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)
Click to expand...
 
A

Anonymous

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 said:
Anonymous said:
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;
Click to expand...

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)
Click to expand...
 
D

Dirk Goldgar

Anonymous said:
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!
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

FWT Newsletter - Weekly - March 8, 2004 4

Top