sql query help

[Guest]

hello,

using access 2003 and sql syntax, how would I accomplish the following:

I have 4 tables of which are not related, but i need to get data from all 4.
table 1 (contains all employees):
- userid
- fullname

table 2 (contains all employees):
- fullname
- dept#
- deptname

table 3 (contains only contractors):
- userid
- fullname

table 4:
- domainname
- username

What I need to do is produce the following results:

table4.domainuserid, fullname (the domainuserid from either table 1 or 3),
table2.dept# (if domainuserid is in table3 list "ctr", but if in table1 list
table1.dept#, otherwise nothing), table2.deptname (if domainuserid is in
table3 list "contractor", but if in table1 list table1.deptname, otherwise
nothing) We need all records in table4 (left join) but need to pull together
the other data from the other tables.

i have written the following sql statement but it doesn't returns all values
because the fullname in tables 2 and 3 are not the same. The last name and
the first name are correct, but the middle initial may not be or they had
other characters jr, sr, etc. But the fullname should always be at least
<lastname>,<firstname>.

SELECT [table4].Username AS [Dom UserID], [table1].UserID AS [Emp UserID],
IIf([table3].UserID,[table3].Name,[table1].Name) AS Name,
IIf([table3].Name,"CTR",[table2].[Dept#]) AS [Dept #],
IIf([table3].Name,"Contractors",[table2].[Dept name]) AS [Dept Name]
FROM ([table4] LEFT JOIN ([table1] LEFT JOIN [table2] ON
[table1].Name=[table2].Name) ON [table4].Username=[table1].UserID) LEFT JOIN
[table3] ON [table4].Username=[table3].UserID
ORDER BY IIf([table3].Name,"CTR",[table2].[Dept#]), [table4].Username;


There could be records in table4 that cannot be matched to table 1 or table
3, but I still need them listed. I need some way to join tables 4, 1, and 2
and 4, 1, 3. The only way to link the 4 tables is like this:

table4.username to table1.userid OR table3.userid
table1.fullname to table2.fullname <<<this is the problem since the
fullname is inconsistent. The only consistency is lastname,firstname
(Whitefield,Bill)>>>

How would I write the sql statement to get values I need?

Thank you in advance.

 

[Peter Yang [MSFT]]

Hello,

I think it might not be so easy to do this in one query.

1. Get the result of userid in either table1 or table3 into a temp table
temp1

2. left out join temp1 with table4 to get all the records.

In SQL, you could do this in a stored procedure. In Access, you could run a
query on a subquery to achieve the goal.

To handle the fullname, you may need to use a custom function to get the
firstname, lastname sepeartely, and determine fullname is equal if both
firstname/lastname are the same.

If you have any further questions or concerns on the issue, please feel
free to let's know.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.