Query troubles...

[melinda.pluma]

My query is not giving me the right information. What I'm trying to
get is a list of Club Members that have Remote Access. I thought it
was accurate, but several records are missing. I'm sorry this is
probably something super easy...



SELECT [Remote Access Users].[First Name], [Remote Access Users].[Last
Name], [Club Members 2007].[FC #], [Club Members 2007].[Club Level],
[Remote Access Users].Active, [Remote Access Users].Branch
FROM [Club Members 2007] LEFT JOIN [Remote Access Users] ON [Club
Members 2007].[FC #] = [Remote Access Users].[FC #]
WHERE ((([Remote Access Users].Active)="Yes"));

 

[John W. Vinson]

My query is not giving me the right information. What I'm trying to
get is a list of Club Members that have Remote Access. I thought it
was accurate, but several records are missing. I'm sorry this is
probably something super easy...



SELECT [Remote Access Users].[First Name], [Remote Access Users].[Last
Name], [Club Members 2007].[FC #], [Club Members 2007].[Club Level],
[Remote Access Users].Active, [Remote Access Users].Branch
FROM [Club Members 2007] LEFT JOIN [Remote Access Users] ON [Club
Members 2007].[FC #] = [Remote Access Users].[FC #]
WHERE ((([Remote Access Users].Active)="Yes"));

The LEFT JOIN will give all entries in [CLUB MEMBERS 2007], and return NULL
values for all fields in [Remote Access Users] - but your criterion returning
Active = "Yes" will exclude the nulls. The default INNER join would give
exactly the same results!

Doublecheck that the "missing" records are in fact missing. Are there entries
in both tables for the missing records' [FC #]? Is the Active field in fact
equal to Yes?

John W. Vinson [MVP]