If No PO Then Show Part Number

N

Nanette

I need to create a query that pulls up a Part Number from the Part table only
if there are no PRs in the PR table.

tblParts.PartNo
tblLinkPartAndPRs
tblPRs.PRNo

I keep getting only the PartNos and PRNos where there are PRs.

How can if fix this?
 
N

Nanette

Hi David,

I used the Unmatched Query Wizard on another table that has a direct
connection between the tables and it worked great.

Unfortunately there is a linked table between the Parts table and the PR
table. I tried using the initial formula and added code for the linking, but
now I get zero records returned.

Heres my query:

SELECT tblParts.PartNo, tblParts.PartDesc, tblPRs.PRNo
FROM tblPRs INNER JOIN (tblParts INNER JOIN tblLinkPoNAndPartsAndPrN ON
tblParts.PartNo=tblLinkPoNAndPartsAndPrN.PartNo) ON
tblPRs.PRNo=tblLinkPoNAndPartsAndPrN.PRNo
WHERE (((tblPRs.PRNo) Is Null));

Do you know how to fix this?
 
D

David F Cox

Apologies, I opened the reply window, then the phone rang.You got the quick
and cheap option.

The clue you needed was that there are some options in the window, like
Tables, Queries and Both.
One way to tackle the problem is to create a query linking the Parts and
Link table and run the Unmatched query against that query and the PR table.
 
K

Ken Snell \(MVP\)

Assuming that there will be no record in tblLinkPartAndPRs for a specific
PartNo if it hasn't been used on a PR:

SELECT tblParts.PartNo
FROM tblParts LEFT JOIN tblLinkPartAndPRs
ON tblParts.PartNo = tblLinkPartAndPRs.PartNo
WHERE tblLinkPartAndPRs.PartNo Is Null;
 
N

Nanette

E haaaa! I got it!

Thanks David

David F Cox said:
Apologies, I opened the reply window, then the phone rang.You got the quick
and cheap option.

The clue you needed was that there are some options in the window, like
Tables, Queries and Both.
One way to tackle the problem is to create a query linking the Parts and
Link table and run the Unmatched query against that query and the PR table.
Click to expand...
 
Top