John, Thanks. Even better than what I was thinking last night where it was, of course, more convoluted.
OR (OpSeqBOM IS NULL and OpSeqWO IS NOT NULL) OR (OpSeqBOM IS NOT NULL AND OpSeqWO IS NULL)
I appreciate you and Duane and this list. Thanks.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, July 11, 2017 11:40 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] RE: unmatched query question
Liz-
Simply add:
OR OpSeqBOM IS NULL OR OpSeqWO IS NULL
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On Jul 12, 2017, at 01:04, Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Duane, however, what if one of the fields is null? Will the mismatch show on that or do I need to tweak the query somehow and if so, how would I do that?
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, July 11, 2017 8:06 AM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: [MS_AccessPros] RE: unmatched query question
Yes. Thank you Duane!
Yet again, I plead guilty to overcomplicating something.
I have 2 other queries and will do accordingly with this type of logic.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, July 10, 2017 8:09 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: unmatched query question
Liz,
Can't you use:
SELECT tblOutputDataFS.Username, tblOutputDataFS.OpSeqBOM, tblOutputDataFS.OpSeqWO, tblOutputDataFS.PartNumber, tblOutputDataFS.Description, tblOutputDataFS.StockType, tblOutputDataFS.GLClass, tblOutputDataFS.BOMRev, tblOutputDataFS.PLRev, tblOutputDataFS.BomQty, tblOutputDataFS.PLQty, tblOutputDataFS.Delta, tblOutputDataFS.RevMismatch, tblOutputDataFS.QtyMismatch, tblOutputDataFS.SalesOrder, tblOutputDataFS.ITM, tblOutputDataFS.WORKORDER, tblOutputDataFS.OpMismatch
FROM tblOutputDataFS
WHERE OpSeqBOM <> OpSeqWO
Regards,
Duane Hookom
Back In Minnesota
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, July 10, 2017 4:11 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] unmatched query question
Pros,
I have to find a mismatch on a table where the BOM Op Sequence doesn't equal the WO Op Sequence. I am using a left join to itself with the is null thing I thought was logical. Please help.
Here is the SQL:
SELECT tblOutputDataFS.Username, tblOutputDataFS.OpSeqBOM, tblOutputDataFS.OpSeqWO, tblOutputDataFS.PartNumber, tblOutputDataFS.Description, tblOutputDataFS.StockType, tblOutputDataFS.GLClass, tblOutputDataFS.BOMRev, tblOutputDataFS.PLRev, tblOutputDataFS.BomQty, tblOutputDataFS.PLQty, tblOutputDataFS.Delta, tblOutputDataFS.RevMismatch, tblOutputDataFS.QtyMismatch, tblOutputDataFS.SalesOrder, tblOutputDataFS.ITM, tblOutputDataFS.WORKORDER, tblOutputDataFS.OpMismatch
FROM tblOutputDataFS LEFT JOIN tblOutputDataFS AS tblOutputDataFS_1 ON (tblOutputDataFS.OpSeqWO = tblOutputDataFS_1.OpSeqWO) AND (tblOutputDataFS.OpSeqBOM = tblOutputDataFS_1.OpSeqBOM) AND (tblOutputDataFS.PartNumber = tblOutputDataFS_1.PartNumber) AND (tblOutputDataFS.WORKORDER = tblOutputDataFS_1.WORKORDER)
WHERE (((tblOutputDataFS_1.OpSeqBOM) Is Null)) OR (((tblOutputDataFS_1.OpSeqWO) Is Null));
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
Tidak ada komentar:
Posting Komentar