[mso] Re: Access 2002 Doesn't Link Null Data

I think the problem with your select is that you are not using parentheses 
around the OR clause, so you're getting test 1 or (test 2 and test 3) by 
default, rather than (test 1 or test 2) and test 3.

> FROM [Active Members] INNER JOIN PEOPLE ON ([Active Members].First_Name =
> PEOPLE.First_Name or [Active Members].Title is null and PEOPLE.Title is 
> null)
> AND ([Active Members].Last_Name = PEOPLE.Last_Name)

should be

> FROM [Active Members] INNER JOIN PEOPLE ON (([Active Members].First_Name =
> PEOPLE.First_Name or [Active Members].Title is null and PEOPLE.Title is 
> null))
> AND ([Active Members].Last_Name = PEOPLE.Last_Name)

The warning message re design view is what I was meaning by my comment re 
design mode.  Sorry, I was totally unclear.


Regards, Dave S

----- Original Message ----- 
From: "Ray Shapp" <ras45@xxxxxxxxxxxxx>
To: <mso@xxxxxxxxxxxxx>
Sent: Saturday, November 17, 2007 6:04 AM
Subject: [mso] Re: Access 2002 Doesn't Link Null Data


> Hi Dave and Cathy,
>
> Many thanks for your replies.
>
> This is my first encounter with SQL, and as in most areas, even the
> terminology is strange.
>
> I did as Dave suggested. See the original query and the suggested 
> modification
> below for selecting all active members who are interested in 
> astrophotography.
>
> It is correct to assume that the null fields will be a match in the Active
> Members query and the PEOPLE master table.
>
> This modified query now incorrectly selects some records. For example, a
> father and daughter who share last name but have differing first names are
> both selected even though the father is marked "No" in the Astrophoto 
> field
> and the daughter is marked "Yes".
>
> As a side issue, Access 2002 generates a warning message that says it 
> cannot
> represent the Join expression in Design view. I'm assuming I can ignore 
> that
> message.
>
> Another side issue: can you suggest a basic SQL online tutorial? I 
> followed
> the Help link to the downloadable MS SQL Server book, but that's far more 
> info
> than I want and I'm not sure the SQL Server is relevant to my current 
> need.
>
> Thanks again.
>
> Ray Shapp
> ***************************
>
> Original query:
> SELECT [Active Members].Title, [Active Members].Last_Name, [Active
> Members].First_Name, PEOPLE.PHONE, PEOPLE.[E-MAIL], [Active
> Members].TYPE_OF_Membership
> FROM [Active Members] INNER JOIN PEOPLE ON ([Active Members].First_Name =
> PEOPLE.First_Name) AND ([Active Members].Last_Name = PEOPLE.Last_Name)
> WHERE (((PEOPLE.Astrophoto)=Yes))
> ORDER BY [Active Members].Last_Name;
>
> Modified SQL query
> SELECT [Active Members].Title, [Active Members].Last_Name, [Active
> Members].First_Name, PEOPLE.PHONE, PEOPLE.[E-MAIL], [Active
> Members].TYPE_OF_Membership
> FROM [Active Members] INNER JOIN PEOPLE ON ([Active Members].First_Name =
> PEOPLE.First_Name or [Active Members].Title is null and PEOPLE.Title is 
> null)
> AND ([Active Members].Last_Name = PEOPLE.Last_Name)
> WHERE (((PEOPLE.Astrophoto)=Yes))
> ORDER BY [Active Members].Last_Name;
>
>
> ----- Original Message ----- 
> From: "David Smart" <smartware@xxxxxxxxxxxxxxx>
> To: <mso@xxxxxxxxxxxxx>
> Sent: Friday, November 16, 2007 4:37 AM
> Subject: [mso] Re: Access 2002 Doesn't Link Null Data
>
>
>> Can I assume that the null fields will be a match in the two tables? 
>> I.e.
>> if the first name is omitted in one, it is also omitted in the other?
>>
>> If so, then Access will handle an inner join with "OR" in the selection 
>> and
>> you can also check for "Is Null" without trouble.
>>
>> However, you need to do it in SQL mode - it can't be done in design mode.
>>
>> For instance:
>>
>> SELECT master.title, master.first, master.last, slave.title, slave.first,
>> slave.last
>> FROM master INNER JOIN slave ON ((master.first = slave.first) or
>> (master.first is null and slave.first is null)) AND (master.last =
>> slave.last) AND (master.title = slave.title);
>>
>> This one is coping with missing first names in both records.  You could
>> easily do the same with title too.
>>
>> Regards, Dave S
>>
>> PS  Access 2003 here, but it should work with 2002 as well.
>
>
> *************************************************************
> You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>
> To Unsubscribe from this group, visit the group's homepage and use the 
> dropdown menu at the top.  This will allow you to unsubscribe your email 
> address or change your email settings to digest or vacation (no mail).
> http://www.freelists.org/webpage/mso
>
> To be able to share files with the group, you must join our Yahoo sister 
> group.  This group will not allow for posting of emails, but will allow 
> you to join and share problem files, templates, etc.: 
> http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for 
> FILE SHARING ONLY.
>
> If you are using Outlook and you see a lot of unnecessary code in your 
> email messages, read these instructions that explain why and how to fix 
> it:
> http://personal-computer-tutor.com/abc3/v28/greg28.htm
> *************************************************************
> 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
 This group will not allow for posting of emails, but will allow you to join 
and share problem files, templates, etc.:  
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

Other related posts: