[mso] Re: Access 2002 Doesn't Link Null Data
- From: Ray Shapp <ras45@xxxxxxxxxxxxx>
- To: mso@xxxxxxxxxxxxx
- Date: Fri, 16 Nov 2007 14:04:49 -0500
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).
//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:
