Query Records Showing Null AND Count = 1

[laknight]

I'm hoping someone can help me with this. I'm trying to NOT QUERY records
which have a null value if there is more than one record. I have posted the
SQL below, but the main fields are [PART],[DATE_CLOSED] and [JOB_NO].

Through the query I have requested the [JOB_NO] to display as Null if the
[DATE_CLOSED] is not #1/1/1900#. How can I take it a step further by saying
DO NOT display records for [PART] which have a [DATE_CLOSED] not #1/1/1900#
AND [JOB_NO] HAVING Count >1. I hope I said that right.

This is what I have:
PART DATE_CLOSE JOB_NO
A 10/24/2006 (Null)
B 10/25/2006 (Null)
B 1/1/1900 1234
B 1/1/1900 1235
B 1/1/1900 1236

This is what I NEED:
PART DATE_CLOSE JOB_NO
A 10/24/2006 (Null)
B 1/1/1900 1234
B 1/1/1900 1235
B 1/1/1900 1236

Here's the SQL I have currently (it is in Design View in Access)

SELECT V_INVENTORY_MSTR.PRODUCT_LINE AS [PR LINE], V_INVENTORY_MSTR.PART,
[QTY_ONHAND]-[QTY_REQUIRED] AS [NET AVAIL], V_JOB_HEADER.DATE_CLOSED,
IIf([DATE_CLOSED]=#1/1/1900#,[V_JOB_HEADER].[JOB],Null) AS JOB_NO
FROM (V_INVENTORY_MSTR LEFT JOIN V_ORDER_LINES ON V_INVENTORY_MSTR.PART =
V_ORDER_LINES.PART) 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.DATE_CLOSED,
IIf([DATE_CLOSED]=#1/1/1900#,[V_JOB_HEADER].[JOB],Null)
HAVING (((V_INVENTORY_MSTR.PRODUCT_LINE)="17" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="19" Or (V_INVENTORY_MSTR.PRODUCT_LINE)="20"
Or (V_INVENTORY_MSTR.PRODUCT_LINE)="50" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="52") AND (([QTY_ONHAND]-[QTY_REQUIRED])<0))
ORDER BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART;

Thank you!

 

[NetworkTrade]

create a calculated count column by Part

then return records for when

PartCount =1 AND Job_No = Null
OR
Job_No = NotNull

 

[laknight]

NTC: Thanks for your response.

I have lots of trouble with the Count function. Everytime I try to use it, I
get unexpected results. (Totally from my lack of knowledge of course!)

I added a column like this:
Field PartCount: PART
Table V_INVENTORY_MSTR
Total Count
Sort (Blank)
Show (Checked On)
Criteria (Blank)

and all I get are "1" in each field for each record. Am I not doing it right?

create a calculated count column by Part

then return records for when

PartCount =1 AND Job_No = Null
OR
Job_No = NotNull

--
NTC

I'm hoping someone can help me with this. I'm trying to NOT QUERY records
which have a null value if there is more than one record. I have posted the
SQL below, but the main fields are [PART],[DATE_CLOSED] and [JOB_NO].

Through the query I have requested the [JOB_NO] to display as Null if the
[DATE_CLOSED] is not #1/1/1900#. How can I take it a step further by saying
DO NOT display records for [PART] which have a [DATE_CLOSED] not #1/1/1900#
AND [JOB_NO] HAVING Count >1. I hope I said that right.

This is what I have:
PART DATE_CLOSE JOB_NO
A 10/24/2006 (Null)
B 10/25/2006 (Null)
B 1/1/1900 1234
B 1/1/1900 1235
B 1/1/1900 1236

This is what I NEED:
PART DATE_CLOSE JOB_NO
A 10/24/2006 (Null)
B 1/1/1900 1234
B 1/1/1900 1235
B 1/1/1900 1236

Here's the SQL I have currently (it is in Design View in Access)

SELECT V_INVENTORY_MSTR.PRODUCT_LINE AS [PR LINE], V_INVENTORY_MSTR.PART,
[QTY_ONHAND]-[QTY_REQUIRED] AS [NET AVAIL], V_JOB_HEADER.DATE_CLOSED,
IIf([DATE_CLOSED]=#1/1/1900#,[V_JOB_HEADER].[JOB],Null) AS JOB_NO
FROM (V_INVENTORY_MSTR LEFT JOIN V_ORDER_LINES ON V_INVENTORY_MSTR.PART =
V_ORDER_LINES.PART) 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.DATE_CLOSED,
IIf([DATE_CLOSED]=#1/1/1900#,[V_JOB_HEADER].[JOB],Null)
HAVING (((V_INVENTORY_MSTR.PRODUCT_LINE)="17" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="19" Or (V_INVENTORY_MSTR.PRODUCT_LINE)="20"
Or (V_INVENTORY_MSTR.PRODUCT_LINE)="50" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="52") AND (([QTY_ONHAND]-[QTY_REQUIRED])<0))
ORDER BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART;

Thank you!