Help with Basic SQL Code for a Query!

[Antonio]

Hi there,


An SQL statement I have written in Access Query does not do what I would like it to do, and I wonder if you can help.

I have tables that can be linked via an identifier, with each table containing potentially multiple records per each ID number.

Here is some sample data from the 2 tables:

SolicitCode Table:

ContactID	SolicitCode
1261	Postal opt out
1261	Email opt in
7592	Postal opt out
9000	Email opt out
9000	EverydayHero opt In
9000	Postal opt out
9000	Telephone opt out
9500	Email Opt In

Attribute Table:

ContactID	AttributeCode
1261	Email opt in
1261	Postal opt out
7592	Email opt in
7592	Email opt in
7592	Postal opt in
7592	Postal opt in
7592	Postal opt in
7592	Postal opt in
7592	Telephone opt in
7592	Telephone opt in
9000	Email opt out
9000	Postal opt out
9000	Telephone opt out

What I am intending to do is to create a dataset that selects the ContactID and SolicitCode from the SolicitCode table where the SolicitCode of the contact does NOT match the AttributeCode.

In other words, I want it to create this dataset:

ContactID	Solicit Code
7592	Postal opt out
9000	EverydayHero opt In
9500	Email Opt In

My current code is as follows:

SELECT SolicitCode.*
FROM SolicitCode LEFT JOIN Attribute ON SolicitCode.ContactID = Attribute.ContactID
WHERE SolicitCode.SolicitCode <> Attribute.AttributeCode;



However, this returns the following dataset! :

ContactID	SolicitCode
1261	Postal opt out
1261	Email opt in
7592	Postal opt out
7592	Postal opt out
7592	Postal opt out
7592	Postal opt out
7592	Postal opt out
7592	Postal opt out
7592	Postal opt out
9000	Email opt out
9000	Email opt out
9000	EverydayHero opt In
9000	EverydayHero opt In
9000	EverydayHero opt In
9000	Postal opt out
9000	Postal opt out
9000	Telephone opt out
9000	Telephone opt out

I think its because as it runs the query, for each contact, it checks whether the statement is true or not for EACH LINE per ID match, not for the ENTIRE contact record.

I'm sure its something very simple, any help would be massively appreciated!!!


Antonio

[June7]

Try:

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

[Antonio]

Hi June,

Thanks very much for your input. However your code returns this set of data:

9000 EverydayHero opt In
9500 Email Opt In

So its still missing:

7592 Postal opt out

Any other ideas??

Antonio

[isladogs]

Before I saw June's answer I copied your data to a test db and created my own unmatched records query .
I got the 3 records you expected
I then saw June's query was identical to my own so I didn't bother to post.

The test db is attached.
If you get different results, its likely that you either have slightly different data or copied June's query incorrectly

[Antonio]

Hi ridders52 / June,


Yes I had indeed made a mistake in my data - one text field was incorrect! However I did need to replace the 'Attribute.Attribute' with 'Attribute.AttributeCode'; after doing this it does get the same results.

I have been trying to understand the logic behind this statement, and to me it seems it shouldnt pull out any records at all.

Because the way I see it, giving some examples:

- when the system goes through the 1st record in SolicitCode, '1261 Postal opt out', it doesn't link with the 1st record in Attribute because it matches by ContactID but not by SolicitCode=AttributeCode, so it does not meet the criteria before it even reaches the WHERE part of the statement, thus not selecting the record (CORRECT answer)
- when the system goes through the 1st record in SolicitCode, '1261 Postal opt out', it does link with the 2nd record in Attribute because it matches by ContactID and by SolicitCode=AttributeCode, so it does meet the criteria, but when it reaches the WHERE part of the statement, because the AttributeCode is NOT Null, it doesn't select the record (again, CORRECT answer)
- when the system goes through the 3rd record in SolicitCode, '7592 Postal opt out', and attempts to link with any of the records in Attribute where there is a match by ContactID, there are no matches when linking via SolicitCode=AttributeCode, so it does not meet the criteria before it even reaches the WHERE part of the statement, thus not selecting the record (INCORRECT answer)

Is my logic completely nuts (or am I just completely nuts!)???


One final question I wanted to put to you guys:

I want to create another dataset with the same results as the original query, with the further criteria added:

Only select the record from the SolicitCode table where the SolicitCode does not conflict with the AttributeCode; more specifically with examples:

Where SolicitCode.SolicitCode = Postal opt out, do not select the record where Attribute.AttributeCode = Postal opt in
Where SolicitCode.SolicitCode = Postal opt in, do not select the record where Attribute.AttributeCode = Postal opt out
Where SolicitCode.SolicitCode = Email opt in, do not select the record where Attribute.AttributeCode = Email opt out
Where SolicitCode.SolicitCode = Email opt out, do not select the record where Attribute.AttributeCode = Email opt in

I have actually attempted to do this myself, a complete stab in the dark, but of course it doesn;t work!:

SELECT SolicitCode.ContactID, SolicitCode.SolicitCode
FROM SolicitCode LEFT JOIN Attribute ON (SolicitCode.SolicitCode = Attribute.AttributeCode) AND (SolicitCode.ContactID = Attribute.ContactID)
WHERE Attribute.AttributeCode Is Null
AND NOT EXISTS ((SolicitCode.SolicitCode = [Postal opt out]) AND (Attribute.AttributeCode = [Postal opt in]))
AND NOT EXISTS ((SolicitCode.SolicitCode = [Postal opt in]) AND (Attribute.AttributeCode = [Postal opt out]))
AND NOT EXISTS ((SolicitCode.SolicitCode = [Email opt in]) AND (Attribute.AttributeCode = [Email opt out]))
AND NOT EXISTS ((SolicitCode.SolicitCode = [Email opt out]) AND (Attribute.AttributeCode = [Email opt in]));

I have attempted a modification, but again it doesn;t work:

SELECT SolicitCode.ContactID, SolicitCode.SolicitCode
FROM SolicitCode LEFT JOIN Attribute ON (SolicitCode.SolicitCode = Attribute.AttributeCode) AND (SolicitCode.ContactID = Attribute.ContactID)
WHERE Attribute.AttributeCode Is Null
AND (((SolicitCode.SolicitCode = [Postal opt out]) <> (Attribute.AttributeCode = [Postal opt in]))
OR ((SolicitCode.SolicitCode = [Postal opt in]) <> (Attribute.AttributeCode = [Postal opt out]))
OR ((SolicitCode.SolicitCode = [Email opt in]) <> (Attribute.AttributeCode = [Email opt out]))
OR ((SolicitCode.SolicitCode = [Email opt out]) <> (Attribute.AttributeCode = [Email opt in])));


Antonio

[isladogs]

I'm not going to try and go through your logic, nuts or otherwise.
You wanted to have all ContactID and SolicitCode values where there was no matching AttributeCode for the same ContactID.
So you needed a left join to get all records from the first table where the corresponding record in the other table was null.
It's called an unmatched query and you can use the query Wizard to create these.

As for your new question, I have no idea what you want so can't suggest a solution.

Suggest you provide the required results based on the data from post #1.
Then it should be possible to provide a simple query to do what you want

It would also help if you explained the point of this exercise.
From the outside I don't understand why this isn't in one table with fields:
ContactID, Code, CodeType where the last field value is Solicit or Attribute

[Antonio]

Point taken, I will follow the logic you listed, thanks for that.

As for the second question, I'm in this situation because data protection data is currently stored in 2 tables in this particular database, and I need to merge the results into one table. Where the data matches between the 2 tables, the data in the Attribute table is more reliable than the SolicitCode table, so thats the data to keep. Thats why I am just taking the data from the solicit code table where it doesnt exist in the other one, then later add this data to the attribute table. The final table must not contain 'conflicts' either between the 2 tables, e.g. an 'Opt In' from one table and an 'Opt Out' from the other where the channel is the same (e.g. must not have 'Email Opt In' AND 'Email Opt Out' (or vice-versa), or a 'Postal Opt In' AND 'Postal Opt out' (or vice-versa)); again because the data from the attributes table is more reliable, I do not want to return data from the SolicitCode table where there is a 'conflict', thus only keeping the record in the Attribute table. I now realise it was probably easier if I matched the 2 tables together and just kept the records in Attributes rather than SolicitCode where there are matches (thus avoiding the step of adding data to the other table when doing it the other way round), but I'm committed in this direction now so lets finish in this direction!

Basically, I want to produce a resulting dataset as follows:

ContactID	SolicitCode
9000	EverydayHero opt In
9500	Email opt in

The difference from the original dataset results is that because record with ContactID=7592 contains a SolicitCode of 'Postal opt out', this clashes with record ContactID=7592 from the Attribute table with AttributeCode of 'Postal opt in', so this record is not selected.

Does this now make sense?


Antonio

[isladogs]

Yes it sort of makes sense when I read it but may be quite tricky to do in practice.
I think it would be necessary to add 2 new fields as a temporary measure
- SolicitType - text (Telephone / Email / Postal / EverydayHero (what on earth is that?)
- OptType - yes/no - True for In & False for Out
Happy to have a go but I assume this isn't the full set of data (or even a selection of actual data)
So I don't want to waste time if that's not going to help

If it is the full dataset, easier to just create the table from scratch

I now realise it was probably easier if I matched the 2 tables together and just kept the records in Attributes rather than SolicitCode where there are matches (thus avoiding the step of adding data to the other table when doing it the other way round), but I'm committed in this direction now so lets finish in this direction!

Definitely easier to just go with the matching codes using this query
Here's all codes that currently match

SELECT Attribute.ContactID, Attribute.AttributeCodeFROM Attribute INNER JOIN SolicitCode ON (Attribute.AttributeCode = SolicitCode.SolicitCode) AND (Attribute.ContactID = SolicitCode.ContactID);

Result:

ContactID	AttributeCode
1261	Postal opt out
1261	Email opt in
9000	Email opt out
9000	Postal opt out
9000	Telephone opt out

Does that help?
Anyway, suggest abandoning your latest idea & going with matching codes even if a bit of adapting is needed!

I'm about to sign off so I won't do anything else tonight but someone else may wish to take over

[Antonio]

ridders52, thanks very much for all the input you have given me, its much appreciated.

My explanation was covering all bases, but for the sake of simplicity, ignore my full explanation.

Instead, for anyone new reading this thread now, I have now created 2 new example datasets to demonstrate the new criteria I need to add (as well as making sure the original criteria still works). They are:

SolicitCode Table:

ContactID	SolicitCode
1261	Postal opt out
1261	Email opt in
7592	Postal opt out
9000	Email opt out
9000	EverydayHero opt In
9000	Postal opt out
9000	Telephone opt out
9500	Email Opt In
9700	Postal opt out
9700	Email opt in
9700	Telephone opt in
9800	Email opt out

Attribute Table:

ContactID	AttributeCode
1261	Email opt in
1261	Postal opt out
7592	Email opt in
7592	Email opt in
7592	Postal opt in
7592	Postal opt in
7592	Postal opt in
7592	Postal opt in
7592	Telephone opt in
7592	Telephone opt in
9000	Email opt out
9000	Postal opt out
9000	Telephone opt out
9700	EverydayHero opt in
9700	Postal opt in
9700	Telephone opt in
9700	Telephone opt in
9800	Email opt in

As before, I want to create a dataset that selects the ContactID and SolicitCode from the SolicitCode table where the SolicitCode of the contact does NOT match the AttributeCode, and this SQL statement as suggested by June7 does the trick:

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

And creates this dataset:

ContactID	Solicit Code
7592	Postal opt out
9000	EverydayHero opt In
9500	Email Opt In
9700	Postal opt out
9700	Email opt in
9800	Email opt out

However, I now want to add the following criteria:

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

Which should now create this dataset:

ContactID	SolicitCode
9000	EverydayHero opt In
9500	Email opt in
9700	Email opt in

Any ideas how I can incorporate this into my SQL statement??

Any help much appreciated!!


Antonio

[isladogs]

Antonio
Are those the same as the original datasets?
If not, please could you modify the tables in the database I uploaded in post #4 and include when you reply

[Antonio]

No, they are not - I had to create 2 new datasets in order to be able to see the new criteria working.

But don't worry, I am modifying those now and will upload into my next post

[Antonio]

OK, here is your database file with the 2 example datasets modified to include my addtional data (and to now match the 2 datasets I have created in Post#9:

[isladogs]

Thanks. I'll look at it later but it won't be for several hours

EDIT - Scrub that comment - its easy

SELECT SolicitCode.ContactID, SolicitCode.SolicitCodeFROM SolicitCode LEFT JOIN Attribute ON (SolicitCode.ContactID = Attribute.ContactID) AND (SolicitCode.SolicitCode = Attribute.AttributeCode)
WHERE (((SolicitCode.SolicitCode) Not Like "*Out") AND ((Attribute.AttributeCode) Is Null));

Result

ContactID	SolicitCode
9000	EverydayHero opt In
9500	Email Opt In
9700	Email opt in

[Antonio]

Thanks a lot again for your reply, however, even though yes it does work for that dataset, in the full dataset there could (and will) be SolicitCodes of 'opt out' which need to be returned, as well as 'opt in'. I have further modified your data tables to reflect an example of when an 'opt out' needs to also be returned, and attached the updated dbase here:



This needs to return the following dataset:

ContactID	SolicitCode
9000	EverydayHero opt In
9500	Email opt In
9700	Email opt in
9900	Email opt out

And just to give more examples of the further criteria I will need to add, to cover all possibilities (although don't waste your time incorporating these into your code, I will do that myself once I apply your principle! Whatever solution you adopt, if you could create something that I can modify to incorporate these kind of rules, that would be perfect):

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

And no problem if you can only reply later, the help you and June7 have given me so far is priceless, I need to complete this data manipulation exercise for a client of mine and normally use Excel so am out of my comfort zone, so I am extremely grateful!

Antonio

[isladogs]

It would make much more sense to upload the complete and final dataset so it can be done in one go.
This time I really can't look at it for several hours so no rush for you to do so.