Count records / rows not working?? Help!

[Guest]

Can anyone tell me why this SQL is not working for me? I need to COUNT the
number of records/rows there are for each PART, but all I get in the
CountOfPart column is all "1"s. I've tried many different things but can't
seem to get it to work.

There are two linked Tables from an outside ODBC data source.
V_INVENTORY_MSTR as a left join (1 to M) on the V_JOB_HEADER table. PART is
unique and JOB is unique but there can be multiple JOB records for each PART.

I need to count the numbers of records/rows for each part based on the
number of JOB records which PART is equal two like:

PART JOB CountOfPart
A 123 1
B 456 2
B 789 2

Here is the SQL I have:
SELECT V_INVENTORY_MSTR.PRODUCT_LINE AS [PR LINE], V_INVENTORY_MSTR.PART,
[QTY_ONHAND]-[QTY_REQUIRED] AS [NET AVAIL], V_JOB_HEADER.JOB,
Count(V_INVENTORY_MSTR.PART) AS CountOfPART
FROM V_INVENTORY_MSTR LEFT JOIN V_JOB_HEADER ON V_INVENTORY_MSTR.PART =
V_JOB_HEADER.PART
GROUP BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART,
[QTY_ONHAND]-[QTY_REQUIRED], V_JOB_HEADER.JOB
HAVING (((V_INVENTORY_MSTR.PRODUCT_LINE)="20") AND
(([QTY_ONHAND]-[QTY_REQUIRED])<0))
ORDER BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART;

Any help is greatly appreciated. Thanks!

 

[Guest]

Chances are the Group By is so specific that you are only going to return one
record for each. I'd especially think that the [QTY_ONHAND]-[QTY_REQUIRED]
would really limit things. Try removing that in all three places and see if
you get results more to what you expect. You may need a subquery to pull in
the [QTY_ONHAND]-[QTY_REQUIRED] data.

 

[Guest]

Thank you Jerry.

I tried pulling in other subqueries and failed before, but I started from
scratch again and just queried PART and CountOfJob and it's working now.
Thanks for your time and input.

Chances are the Group By is so specific that you are only going to return one
record for each. I'd especially think that the [QTY_ONHAND]-[QTY_REQUIRED]
would really limit things. Try removing that in all three places and see if
you get results more to what you expect. You may need a subquery to pull in
the [QTY_ONHAND]-[QTY_REQUIRED] data.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Can anyone tell me why this SQL is not working for me? I need to COUNT the
number of records/rows there are for each PART, but all I get in the
CountOfPart column is all "1"s. I've tried many different things but can't
seem to get it to work.

There are two linked Tables from an outside ODBC data source.
V_INVENTORY_MSTR as a left join (1 to M) on the V_JOB_HEADER table. PART is
unique and JOB is unique but there can be multiple JOB records for each PART.

I need to count the numbers of records/rows for each part based on the
number of JOB records which PART is equal two like:

PART JOB CountOfPart
A 123 1
B 456 2
B 789 2

Here is the SQL I have:
SELECT V_INVENTORY_MSTR.PRODUCT_LINE AS [PR LINE], V_INVENTORY_MSTR.PART,
[QTY_ONHAND]-[QTY_REQUIRED] AS [NET AVAIL], V_JOB_HEADER.JOB,
Count(V_INVENTORY_MSTR.PART) AS CountOfPART
FROM V_INVENTORY_MSTR LEFT JOIN V_JOB_HEADER ON V_INVENTORY_MSTR.PART =
V_JOB_HEADER.PART
GROUP BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART,
[QTY_ONHAND]-[QTY_REQUIRED], V_JOB_HEADER.JOB
HAVING (((V_INVENTORY_MSTR.PRODUCT_LINE)="20") AND
(([QTY_ONHAND]-[QTY_REQUIRED])<0))
ORDER BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART;

Any help is greatly appreciated. Thanks!