How to find missing records

[Les Isaacs]

Hi All
I need a query that will return any records from 'qry bulletin recipients' that do NOT have an appropriate record in 'tblEmailLogs': appropriate, for this purpose, means that the field [memBody] in 'tblEmailLogs' contains the text "PayeDoc is on the move" and also that the field [dteSend] in 'tblEmailLogs' is later than 25 March 2012.

There will be many records in 'tblEmailLogs' with related records in 'qry bulletin recipients', but if there's a related record in 'tblEmailLogs' that satisfies the above 2 criteria I don't want the query to return the related record from 'qry bulletin recipients'.

My attempt was:
SELECT tblEmailLogs.memBody, tblEmailLogs.dteSend, [qry bulletin recipients].email, [qry bulletin recipients].[prac name]
FROM tblEmailLogs RIGHT JOIN [qry bulletin recipients] ON tblEmailLogs.txtEmailTo = [qry bulletin recipients].email
WHERE (((tblEmailLogs.memBody) Like "*" & "PayeDoc is on the move" & "*") AND ((tblEmailLogs.dteSend)>#3/25/2012#));

... but this does not return the required records.
Hope someone can help.
Many thanks
Les

 

[jdraw]

What is the sql for
[qry bulletin recipients]?

 

[MSAccessRookie]

Hi All
I need a query that will return any records from 'qry bulletin recipients' that do NOT have an appropriate record in 'tblEmailLogs': appropriate, for this purpose, means that the field [memBody] in 'tblEmailLogs' contains the text "PayeDoc is on the move" and also that the field [dteSend] in 'tblEmailLogs' is later than 25 March 2012.

There will be many records in 'tblEmailLogs' with related records in 'qry bulletin recipients', but if there's a related record in 'tblEmailLogs' that satisfies the above 2 criteria I don't want the query to return the related record from 'qry bulletin recipients'.

My attempt was:
SELECT tblEmailLogs.memBody, tblEmailLogs.dteSend, [qry bulletin recipients].email, [qry bulletin recipients].[prac name]
FROM tblEmailLogs RIGHT JOIN [qry bulletin recipients] ON tblEmailLogs.txtEmailTo = [qry bulletin recipients].email
WHERE (((tblEmailLogs.memBody) Like "*" & "PayeDoc is on the move" & "*") AND ((tblEmailLogs.dteSend)>#3/25/2012#));

... but this does not return the required records.
Hope someone can help.
Many thanks
Les

Since you want "any records from 'qry bulletin recipients' that do NOT have an appropriate record in 'tblEmailLogs'", it sounds more like a LEFT JOIN to me (Take ALL records from 'qry bulletin recipients' and only the matching records from 'tblEmailLogs'. Records that do not have a match will have NULL values for Fields taken from 'tblEmailLogs'.

NOTE: My comments are based on the assumption that 'qry bulletin recipients' returns the required dataset. If there is any concern that it might not, then jdraw's question regarding the SQL should be viewed as highly important for use in diagnosing the issue. If there is not, then it does not have to be, since the results of any Query can be treated as if they were a dynamic Table.

 

[Les Isaacs]

Hi

Jdraw: the sql for [qry bulletin recipients] is:

SELECT practices.[prac name], practices.email, practices.live, practices.potential
FROM practices
WHERE (((practices.email) Is Not Null) AND ((practices.live)=True) AND ((practices.potential)=False));

MSAccessRookie: I can confirm that 'qry bulletin recipients' returns the required dataset - as well as a lot of other unrequired records, but rather than "Take ALL records from 'qry bulletin recipients' and only the matching records from 'tblEmailLogs'" I need "Take ALL records from 'qry bulletin recipients' EXCEPT THOSE WITH matching records (that satisfy the two criteria, on memBody and dteSend) from 'tblEmailLogs'".

So, to put it another way (I think!): 'qry bulletin recipients' contains all the possibly required records, and 'tblEmailLogs' contains lots of related records, but if there's a related record in 'tblEmailLogs' that satisfies the two criteria I don't want to see that record - I only want records where there is NO releted record in 'tblEmailLogs' that satisfies the criteria.

Hope that explains it adequately - and very many thanks again for the help.
Les

 

[MSAccessRookie]

So in effect you need all records from 'qry bulletin recipients' that also have a record in 'tblEmailLogs'.

That can be done using an INNER JOIN.

You also want to exclude any record in 'tblEmailLogs' that contains BOTH of your Criteria.

That can be done using a WHERE statement.

If I am correct, then the code would look something like the folowing (You may need to play with the WHERE Statement).

SELECT tblEmailLogs.memBody, tblEmailLogs.dteSend, [qry bulletin recipients].email, [qry bulletin recipients].[prac name]
FROM tblEmailLogs INNER JOIN [qry bulletin recipients] ON tblEmailLogs.txtEmailTo = [qry bulletin recipients].email
WHERE (((tblEmailLogs.memBody) [B]Not Like[/B] "*" & "PayeDoc is on the move" & "*") AND ((tblEmailLogs.dteSend)[B]<=[/B]#3/25/2012#));

 

[Les Isaacs]

Hi MSAccessRookie
I'm not in the office now so cannot test your suggested code, but I think the logic may still be wrong:
Where you say "So in effect you need all records from 'qry bulletin recipients' that also have a record in 'tblEmailLogs'. ", it isn't necessarily true that for the required records there will be any record in 'tblEmailLogs'. All that is definitely true is that the required records do NOT have a record in 'tblEmailLogs' with memBody Like "*" & "PayeDoc is on the move" & "*" and dteSend>#3/25/2012#. The required records MAY have records in 'tblEmailLogs', but none of those 'tblEmailLogs' records will meet both these criteria - if they do, they must be excluded from the query results.
Takes a bit of getting your head round!
Thanks again - hope you don't give up on this because I'm stumped!
Les

 

[MSAccessRookie]

Does this work any better for you? It will also select entries that do not have any match in tblEmailLogs.

SELECT tblEmailLogs.memBody, tblEmailLogs.dteSend, [qry bulletin recipients].email, [qry bulletin recipients].[prac name]
[COLOR=black][FONT=Verdana]FROM [qry bulletin recipients] LEFT JOIN tblEmailLogs ON  = tblEmailLogs.txtEmailTo[/FONT][/COLOR]
WHERE [COLOR=black][FONT=Verdana]((tblEmailLogs.memBody) Is Null) OR (((tblEmailLogs.memBody) [B]Not Like[/B] "*" & "PayeDoc is on the move" & "*") AND ((tblEmailLogs.dteSend) [B]<= [/B]#3/25/2012#))[/FONT][/COLOR];

[/QUOTE]

 

[Les Isaacs]

Hi MSAccessRookie

I really appreciate your help with this: but we're still not there!

I tried your suggested code, and I amended what I think was a typo - after the ON bit. When I ran that query it returned thousands of records, whereas there should be a maximum on 94 on the current data because there are only 94 records in [qry bulletin recipients] (but thousands in [tblEmailLogs]). I therefore made it a GROUP BY query, using WHERE for the criteria. The resulting sql was is below (I removed the date criteria altogether to simplify things a bit - I'll add that back later), but this returns all 94 records from [qry bulletin recipients] - including the ones that do have a record in tblEmailLogs with "PayeDoc is on the move" in the memBody field.

If there is a related record in tblEmailLogs with "PayeDoc is on the move" in the memBody field, the query must EXCLUDE the related record from [qry bulletin recipients].

An example (always a good idea!):
[qry bulletin recipients] has a record where 'email' = leslie@abc.net
[tblEmailLogs] has 200 records where 'txtEmailTo' = leslie@abc.net, and one of these 200 has "PayeDoc is on the move" in 'memBody'.
So the query should NOT return the record from [qry bulletin recipients] where 'email' = leslie@abc.net.
If none of the 200 records in [tblEmailLogs] (where 'txtEmailTo' = leslie@abc.net) had "PayeDoc is on the move" in 'memBody', then the query SHOULD return the record from [qry bulletin recipients] where 'email' = leslie@abc.net.

Writing it out like this has helps see what I'm trying to do - but I'm no nearer getting the query right!!

Really hope you can help.
Thanks once again
Les
My latest attempt as refered to above:

SELECT [qry bulletin recipients].email, [qry bulletin recipients].[prac name]
FROM [qry bulletin recipients] LEFT JOIN tblEmailLogs ON [qry bulletin recipients].email = tblEmailLogs.txtEmailTo
WHERE (((tblEmailLogs.memBody) Not Like "*" & "PayeDoc is on the move" & "*")) OR (((tblEmailLogs.memBody) Is Null))
GROUP BY [qry bulletin recipients].email, [qry bulletin recipients].[prac name];

 

[Beetle]

How about a sub-query with a Not In clause. Would look like;


SELECT tblEMailLogs.txtEmailTo, tblEMailLogs.memBody, [qry bulletin reipients].email
FROM [qry bulletin reipients] LEFT JOIN tblEMailLogs ON [qry bulletin reipients].email = tblEMailLogs.txtEmailTo
WHERE [qry bulletin reipients].email Not In (SELECT [qry bulletin reipients].email
FROM [qry bulletin reipients] INNER JOIN tblEMailLogs ON [qry bulletin reipients].email = tblEMailLogs.txtEmailTo
WHERE tblEMailLogs.memBody Like "*" & "payedoc is on the move" & "*" AND tblEMailLogs.dteSend>#3/25/2012#);

 

[Les Isaacs]

Hi Beetle

Many thanks for your input. I haven't seen a query with a sub-query like this before - but it 'feels right' (to the extent that I have any kind of 'feel' for these things)! However, when I tried to run your suggested code it took ages to run - in fact I gave up after a minute or so! Not sure why - [qry bulletin reipients] runs instantly. I will persevere to at least check that the query returns the correct results, then see if I can do something to speed it up. Would you have any ideas on that?

Thanks again
Les

 

[Beetle]

Take the sub-query SELECT statement and run it on it's own as a separate query. It should return all the records that you want to exclude form the other query. See if it hangs, or runs very slowly, as a stand alone query.

 

[Les Isaacs]

Hi Beetle

I did as you suggested and the 'sub query' runs fine, almost instantly. But when it is used as part of the main query, the main query hangs. If I remove the 'sub query' from the main query, the main query run fine (but returns the wrong results, of course). Does this help?

I've already got something from this, even before 'we' solve this problem - because I can now know about using sub queries within queries and this is going to help me with a couple of other issues (assuming the technique doesn't always cause the main query to hang )

Thanks as ever
Les

 

[Beetle]

Are the email fields (email and txtEmailTo) Indexed in the underlying tables?

 

[Les Isaacs]

Hi Beetle
txtEmailTo is indexed, email is not - and I won't be able to do that until I'm back in the office in the morning (UK time!), but will reports back then.
Thanks again
Les