"Not In" Query "Not Working" (1 Viewer)

[NauticalGent]

Hello again Access Commandos,
I am having difficulty writing a query which I thought would be simple, but like every other time I have come to this forum, I thought wrong.
I have two tables: tblSchedule and tblStatus. The PK in tblSchedule is an Autonumber called SchedID that the records in tblStatus are linked to.
There is a field in tblStatus, called AuditStatus, that is a combo box with about 25 options for status input. One of the options is “Invoice Received”.
What I am trying to do is create a query that shows all the records in tblSchedule.SchedID that do not have an entry in tblStatus.AuditStatus that does equals “Invoice Received”.
I hope I have stated this clear enough and thank anyone and everyone in advance for any help…

 

[pbaldy]

Try a 2 query solution. First create a query of all the IDs that do have that status. Then use the unmatched query wizard to compare that to the first table.

 

[CJ_London]

should be simple enough - are you using lookups in your table design by any chance?

 

[NauticalGent]

I am. Here is an example of the sql I tried. The syntax is incorrect, and I ctn figure out what is wrong with it...and...even if I could, I have no idea if it would work since I cant get it to run. I cant beleive something as simple as this is confounding me...

SELECT tblSchedule.SchedID, tblSchedule.ShipID
FROM tblSchedule LEFT JOIN tblCORAudit ON tblSchedule.[SchedID] = tblCORAudit.[SchedID]
WHERE (((tblSchedule.[Start Date])>=#10/1/2015#) AND ((Count(*) tblCORAudit.AuditStatus)="Invoice Received"=0));

 

[The_Doc_Man]

This is possibly a case for an outer join. Sounds intimidating? Not if you use the query design grid. I'm going to be pedantic but because I'm going to suggest something a bit unusual, stick with me.

From the Create tab, do a query design for a new SELECT query. It will open up a blank area and a tabular structure. You can add tables to the blank area, so bring in the two tables. If there is already a formal relation between the two tables on the schedule ID field, there will be a line between those fields in the table area to reflect the "global" relationship. Click on that to check its properties - and delete it (which in the design view of a query is a local override only) if the properties aren't what you want.

What you want is a relationship between the ScheduleID fields that selects all the records in tblSchedule and any records from tblStatus that match the selected ID. If there was no relationship, or if the existing relationship wasn't of this type, then make it so. Making a relationship in the query design table area is again a "Spot override" as opposed to a general relationship declaration. (This is the part that often isn't well understood.)

Once the tables are present, their fields are available for selection. In the field grid, select whatever fields you want from the schedule table as-is. For the items that might or might not come from your status table (might not, if no entries have been made yet), for EACH such field make a construct that looks like "Sxxx: NZ([xxx],0)" - which means that in the query, field xxx from the status table would be named Sxxx. This is the same as doing a "SELECT ..., xxx AS Sxxx, ..." in a SELECT query. Where the value could be a string, you can use NZ([xxx],"") if you wish. When you have a schedule item that wasn't matched by a status item, of course you will get 0 or "" as appropriate, but it won't be null.

Now you just add a criterion under the SAuditStatus (aliased) field that says "<> Invoice Received" (or whatever code you are using for that status). If you are using status codes for this, it still works OK as long as the code for "Invoice Received" isn't 0.

This will cause the query to select ALL entries in the schedule, with OR WITHOUT a corresponding status entry - EXCEPT for those schedule items that were (a) matched by a status record that (b) did say "Invoice Received." If the schedule item had no status (yet), it would be included. If the schedule item had a status, you would still see it unless the status was "Invoice Received."

This is the SIMPLE way to do an outer join. Now, if you want to see how that works in SQL, you can change from query design view to SQL view and see the syntax for that particular case.

 

[NauticalGent]

"I'd piss on a spark plug if I thought it would do any good!"

Going to give this a try, MTF.

 

[CJ_London]

modify your query to this

 SELECT tblSchedule.SchedID, tblSchedule.ShipID
FROM tblSchedule TS 
     LEFT JOIN 
         (SELECT SchedID, COUNT(*) FROM tblCORAudit  
          WHERE AuditStatus="Invoice Received" 
          GROUP BY SchedID) TC 
     ON TS.[SchedID] = TC.[SchedID]
WHERE [Start Date])>=#10/1/2015#) AND TC.SchedID is Null

 

[NauticalGent]

Doc, it worked pretty well. I added a DISTINCT parameter to pare down the RS and I get what I am looking for.

CJ, your example is what I was trying to do. I cut and pasted your code but when I try to run it (I had to remove the ")" after [Start Date] and 2015), it asks me for a Parameter Value for tblSchedule.SchedID and tblSchedule.ShipID. Which is strange becuase that is the correct spelling for my table and field. Any ideas?

 

[CJ_London]

I was using aliases and forgot to change that bit - change to

SELECT TS.SchedID, TS.ShipID
...
...

 

[NauticalGent]

CJ, that worked well too. You and The Doc have once again saved my bacon, there is a place in heaven reserved for the both of you...

Thanks again!

 

[Misiek]

I have the same problem, can you be so kind and change my query as I'm lost :/

@The_Doc_Man
I tried do it your way, but I'm lost at the point where I need to add my fields, as they are expressions.

@CJ_London
I tried your way, but when I ran the Query it asks me to enter data for Q1.uUserPK and Q2.dUserFK

SELECT Q1.uUserName, Sum(IIf([Q2].[dCatFK]=1,1,0)) AS AM, Sum(IIf([Q2].[dCatFK]=2,1,0)) AS PM
FROM T_Users AS Q1 LEFT JOIN (SELECT dCatFK FROM Q_Defects
WHERE dDeptFK=Forms!F_MainMenu.cboSelectDept
) AS Q2 ON Q1.uUserPK = Q2.dUserFK
WHERE (((DatePart("m",[dDateClosed]))=Forms!F_MainMenu!txtMonth)) Or (((Forms!F_MainMenu!txtMonth) Is Null))
GROUP BY Q1.uUserName;

Any help will be much appreciated.

 

[Grumm]

This will not work.
You need to add the dUserFK in the SELECT of your left join...
It is not because you select 1 field that SQL knows what all the other fields of that table are.

So try this :

SELECT Q1.uUserName, Sum(IIf([Q2].[dCatFK]=1,1,0)) AS AM, Sum(IIf([Q2].[dCatFK]=2,1,0)) AS PM
FROM T_Users AS Q1 LEFT JOIN (SELECT dUserFK,dCatFK FROM Q_Defects 
WHERE dDeptFK=Forms!F_MainMenu.cboSelectDept
) AS Q2 ON Q1.uUserPK = Q2.dUserFK
WHERE (((DatePart("m",[dDateClosed]))=Forms!F_MainMenu!txtMonth)) Or (((Forms!F_MainMenu!txtMonth) Is Null))
GROUP BY Q1.uUserName;

 

[Misiek]

Oh I see, thanks.
I tried that but query asks for input for dDateClosed, I tried changing for Q2.dDateClosed but no luck.
Query also asks for input for Q1.uUserPK....
Then it runs and displays all names correctly but no calculation is performed for AM or PM, just displays 0.

 

[Grumm]

Did you try both select's without left join first ?

Like what results do you get if you just do

SELECT dUserFK,dCatFK FROM Q_Defects 
WHERE dDeptFK=Forms!F_MainMenu.cboSelectDept

Then ask yourself, is this the data i need ? If yes, then check that the PK matches with the FK.
Also if you use aliases, make sure all the fields use one of the alias. ([dDateClosed] should be either Q1.dDateClosed or Q2.dDateClosed)

Then is it best practice to test if a form field is Null in a query ? I lack experience to tell you that you beter don't do it. Maybe someone else here can tell me if that is correct or not.

 

[Misiek]

Stupid me!
mistype field name, everything works now.
Thanks for your help Grumm.