Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>At a guess, I'd say that your query, at the "NOT IN" point (i.e. its subquery), will effectively <strong>not</strong> show any partnumber if it has been removed from <strong>any</strong> package. This is because that subquery is searching across all packages. You will need to modify it (somehow) so that it only considers rows that pertain to the package of interest.</p> <p>Look closely at that subquery. It places conditions on part numbers, change levels, and event types. But what constraints are there for packages? How is it supposed to determine what rows go with Package 1, and what rows go with Package 2?</p> <p>My guess is that rows from Package 1 and Package 2 are commingled together. Since it finds a row where "EvtLog.EventTypeSelected = 'pn REMOVED From Wrapper'" is <strong>True</strong> for at least one of these packages, the "NOT IN" makes it <strong>False</strong> no matter the package you really wanted to display in your event viewer.</p> <hr> <p>What Brandon S said above (about IN versus EXISTS), plus: You may need to use an alias; for example (making alias <strong>EvtLog</strong> for the sub-query):</p> <pre><code>"SELECT DISTINCT tblRevRelLog_Detail.PartNumber, tblRevRelLog_Detail.ChangeLevel FROM tblRevRelLog_Detail LEFT JOIN tblEventLog ON (tblEventLog.PartNumber = tblRevRelLog_Detail.PartNumber) AND (tblEventLog.PartNumberChgLvl = tblRevRelLog_Detail.ChangeLevel) WHERE NOT EXISTS (SELECT EvtLog.PartNumber, EvtLog.PartNumberChgLvl FROM tblEventLog EvtLog WHERE tblEventLog.PartNumber=EvtLog.PartNumber AND tblEventLog.PartNumberChgLvl=EvtLog.PartNumberChgLvl AND EvtLog.EventTypeSelected = 'pn REMOVED From Wrapper' AND EvtLog.PartNumber = tblRevRelLog_Detail.PartNumber AND EvtLog.PartNumberChgLvl = tblRevRelLog_Detail.ChangeLevel) AND tblEventLog.TrackingNumber = """ &amp; tempTrackingNumber &amp; """ AND tblEventLog.PartNumber = tblRevRelLog_Detail.PartNumber AND tblEventLog.PartNumberChgLvl = tblRevRelLog_Detail.ChangeLevel" </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POMS ACCESS query
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USrskar
    1. USrskar
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThanks for responding rskar, but still it is pulling the same records :(
      singulars
      1. PO
      1. USuser397316
    2. COI am now perplexed on what the question was. Your use of IN ( (value1, value2) NOT IN (subquery) ) isn't valid syntax in the SQL of Access 2003. You've shown your query as a Visual Basic expression - are you sure you're accessing an Access DB (and not some other DB system)? Edit your question to indicate what sort of records you want pulled.
      singulars
      1. PO
      1. USrskar
    3. CO@rskar..yes I am accessing an ACCESS DB- 2003.. and I am editing the question to show the example output records
      singulars
      1. PO
      1. USuser397316
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload