getting all entries from main table and matching from another

[EdLeeYoung]

I need to modify my query below that uses 2 tables (06_statments and Directory)

We get the statement table from the university, they identify users by
something other than name.
I want to get all entries for each "Service Number" (telephone number) from
the 06_Statements table.....if the number is in our Directory I also want to
get the name and group for that entry othewise the name and group field is
left blank. What I've have here only gives me records found in both files.


help


SELECT [06_Statements].[Billing Date], [06_Statements].[Service Number],
[06_Statements].[Subscriber User Name], [06_Statements].Description,
[06_Statements].[To Number], [06_Statements].[To City], [06_Statements].[To
State], [06_Statements].Duration, [06_Statements].[Connect Date],
[06_Statements].[Total Charge], Directory.[Service Number], Directory.[Full
Name], Directory.Group
FROM 06_Statements, Directory
WHERE ((([06_Statements].[Billing Date]) Like "*DEC*") AND
(([06_Statements].[Service Number]) Like [Directory].[Service Number]));

 

[John Spencer]

Try doing a LEFT JOIN on the tables based on the Service Number.

SELECT [06_Statements].[Billing Date], [06_Statements].[Service Number],
[06_Statements].[Subscriber User Name], [06_Statements].Description,
[06_Statements].[To Number], [06_Statements].[To City],
[06_Statements].[To State], [06_Statements].Duration,
[06_Statements].[Connect Date],
[06_Statements].[Total Charge],
Directory.[Service Number], Directory.[Full Name], Directory.Group
FROM 06_Statements LEFT JOIN Directory
ON [06_Statements].[Service Number] = [Directory].[Service Number]
WHERE [06_Statements].[Billing Date] Like "*DEC*"