[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
*************************************************************

Other related posts: