Help with Basic SQL Code for a Query!

[Antonio]

OK, here is the full dataset. The fields and table names are slightly different, so I have modified your current unmatched records SQL statement accordingly, to this:



SELECT Solicit.ContactID, Solicit.SolicitCode
FROM Solicit LEFT JOIN Attribute ON (Solicit.SolicitCode = Attribute.AttributeCode) AND (Solicit.ContactID = Attribute.ContactID)
WHERE (((Attribute.AttributeCode) Is Null));

Here is the full dataset:

In fact its not the full dataset, because it was too large to attach. But its got a good chunk of real data.

The kind of criteria I need to add follows this kind of prniciple:

Where Solicit.SolicitCode = 'Postal opt out' and the Attribute.AttributeCode = 'Postal opt in', do NOT select the record
Where Solicit.SolicitCode = 'Postal opt in' and the Attribute.AttributeCode = 'Postal opt out', do NOT select the record
Where Solicit.SolicitCode = 'Email opt out' and the Attribute.AttributeCode = 'Email opt in', do NOT select the record
Where Solicit.SolicitCode = 'Email opt in' and the Attribute.AttributeCode = 'Email opt out', do NOT select the record

[isladogs]

OK - at last there's enough data to see the point of doing this ....
Just had a short break from DIY and have I believe got a definitive result for you

I've left in my previous queries but not used them here

Various queries added whilst I was working out the best strategy.

The important ones are:
- qrySolicitCodesToIgnore - this finds the opposing opt in / opt out codes you want excluded - only 12 of these it seems
- qrySolicitCodesToKeep - this uses the above query and the Solicit table to find remaining codes you do want - 9987 out of 9999 in the Solicit table

Hopefully I've got the correct idea!
If I have, you should be able to 'upscale' to apply to the entire dataset

OK back to the DIY now ...

[Antonio]

ridders52,


You did have the right idea, although you slightly misunderstood my initial question, and actually gave me a more concise (better!) answer in the end, but actually what I was looking to do was write all possible text strings out - because I am a newbie to Access, it helps me to keep track on whats going on! In other words I wrote your SolicitCodesToIgnore as:

SELECT DISTINCT Solicit.ContactID, Solicit.SolicitCode, Attribute.AttributeCode
FROM Attribute INNER JOIN Solicit ON Attribute.ContactID = Solicit.ContactID
WHERE ((Solicit.SolicitCode="Email opt in" AND Attribute.AttributeCode="Email opt out") OR
(Solicit.SolicitCode="Email opt out" AND Attribute.AttributeCode="Email opt in") OR
(Solicit.SolicitCode="Postal opt in" AND Attribute.AttributeCode="Postal opt out") OR
(Solicit.SolicitCode="Postal opt out" AND Attribute.AttributeCode="Postal opt in") OR
(Solicit.SolicitCode="Telephone opt in" AND Attribute.AttributeCode="Telephone opt out") OR
(Solicit.SolicitCode="Telephone opt out" AND Attribute.AttributeCode="Telephone opt in") OR
(Solicit.SolicitCode="SMS opt in" AND Attribute.AttributeCode="SMS opt out") OR
(Solicit.SolicitCode="SMS opt out" AND Attribute.AttributeCode="SMS opt in"));

But basically I understood all your queries (well, actually the SolcitCodesToKeep only partly, but the important thing is it returns the correct data!), and have adapted to my use.

And the point is that you have solved the problem!

You have no idea how much I have appreciated your help. I would have not been able to deliver what I am doing at the moment for a client.

So an immense thank you to you!!!

If there is something I can do for you in return, just let me know!

[isladogs]

ridders52,
You did have the right idea, although you slightly misunderstood my initial question, and actually gave me a more concise (better!) answer in the end, but actually what I was looking to do was write all possible text strings out - because I am a newbie to Access, it helps me to keep track on whats going on! In other words I wrote your SolicitCodesToIgnore as:

SELECT DISTINCT Solicit.ContactID, Solicit.SolicitCode, Attribute.AttributeCode
FROM Attribute INNER JOIN Solicit ON Attribute.ContactID = Solicit.ContactID
WHERE ((Solicit.SolicitCode="Email opt in" AND Attribute.AttributeCode="Email opt out") OR
(Solicit.SolicitCode="Email opt out" AND Attribute.AttributeCode="Email opt in") OR
(Solicit.SolicitCode="Postal opt in" AND Attribute.AttributeCode="Postal opt out") OR
(Solicit.SolicitCode="Postal opt out" AND Attribute.AttributeCode="Postal opt in") OR
(Solicit.SolicitCode="Telephone opt in" AND Attribute.AttributeCode="Telephone opt out") OR
(Solicit.SolicitCode="Telephone opt out" AND Attribute.AttributeCode="Telephone opt in") OR
(Solicit.SolicitCode="SMS opt in" AND Attribute.AttributeCode="SMS opt out") OR
(Solicit.SolicitCode="SMS opt out" AND Attribute.AttributeCode="SMS opt in"));

But basically I understood all your queries (well, actually the SolcitCodesToKeep only partly, but the important thing is it returns the correct data!), and have adapted to my use.

And the point is that you have solved the problem!

You have no idea how much I have appreciated your help. I would have not been able to deliver what I am doing at the moment for a client.

So an immense thank you to you!!!

If there is something I can do for you in return, just let me know!

You're welcome

Whilst I can see why you want to 'spell out' exactly what your 'ignore' query does, for the benefit of anyone else reading this, compare your query (above) with my more concise version:

SELECT DISTINCT Solicit.ContactID, Solicit.SolicitCode, Attribute.AttributeCode, Left([SolicitCode],InStr([SolicitCode]," ")) AS IgnoreCodeType
FROM Attribute INNER JOIN Solicit ON Attribute.ContactID = Solicit.ContactID
WHERE (((Attribute.AttributeCode)<>[SolicitCode]) AND ((Left([SolicitCode],InStr([SolicitCode]," ")))=Left([AttributeCode],InStr([AttributeCode]," "))));

Both give the same results.

ContactID	SolicitCode	AttributeCode	IgnoreCodeType
4756	Email opt in	Email opt out	Email
6044	Email opt in	Email opt out	Email
6044	Email opt out	Email opt in	Email
6044	Postal opt out	Postal opt in	Postal
6297	Postal opt out	Postal opt in	Postal
6297	Telephone opt out	Telephone opt in	Telephone
7592	Postal opt out	Postal opt in	Postal
8054	Email opt out	Email opt in	Email
9262	Email opt out	Email opt in	Email
9912	Email opt in	Email opt out	Email
12041	Postal opt out	Postal opt in	Postal
20725	Email opt out	Email opt in	Email

However your 'explicit' version only works if you KNOW that none of the other types will be listed in the 'opposite incarnation'
For example Everyday hero Opt in / out or others such as DARQ / DARQ updates only (whatever that means)
The real advantage of the 'concise' version is not just its relative brevity but also that it covers all possible eventualities


The other query of codes to keep is:

SELECT Solicit.ContactID, Solicit.SolicitCodeFROM Solicit 
LEFT JOIN qrySolicitCodesToIgnore ON (Solicit.SolicitCode = qrySolicitCodesToIgnore.SolicitCode) AND (Solicit.ContactID = qrySolicitCodesToIgnore.ContactID)
WHERE (((qrySolicitCodesToIgnore.ContactID) Is Null) AND ((qrySolicitCodesToIgnore.SolicitCode) Is Null));

All that does is get all the codes from your solicit table which aren't in the list of 12 codes listed in the ignore query


Many thanks for what I thought was an extremely positive reputation comment
The forum software somehow thinks it was 'somewhat positive'

If you want to show appreciation for my responses or those of other forum users, that is the very best way to do so.