Queries not returing all results

  • Thread starter Thread starter timmycav
  • Start date Start date
T

timmycav

Hi everybody.

I have been trying to run a query to show the members who have used a
voucher. The field with this information is a "Yes/No" tick (check) box. When
I have run the query some of the members don't show up. Whenever I go into
their records to double check that the box is correctly marked, it is.

I have also run a query to show all members. My database has 3683 members,
but when I run a query which should return all of them, it only returns 2370.

I've tried everything I know, but can't get everything to show.

Any and all help would be great.

Thanks in advance.

Tim.
 
Ummmm.... can you show us the SQL statement of the query that "isn't
returning all the records"? Kinda hard to troubleshoot your query when we
have no idea about its structure.
 
Hi Ken

Here's the SQL Statement:

SELECT [01 Member Table].[Customer ID], [003 Loyalty Program Level].[Current
Level], [01 Member Table].Name, [01 Member Table].[Name 2], [01 Member
Table].[Name 3], [01 Member Table].[Name 4], [02 Club].Title, [02
Club].Firstname, [02 Club].Lastname, [01 Member Table].[Building Name], [01
Member Table].[Unit Floor 1], [01 Member Table].[Unit Floor 2], [01 Member
Table].[Street Address], [01 Member Table].[Add Address], [01 Member
Table].City, [01 Member Table].[Post Code], [01 Member Table].State, [01
Member Table].Telephone, [01 Member Table].Fax, [01 Member Table].Email, [01
Member Table].[Email bounced Y/N], [01 Member Table].[Travel Voucher], [01
Member Table].[Travel Voucher Y/N], [01 Member Table].[Return to sender Y/N]
FROM [02 Club] INNER JOIN (([01 Member Table] INNER JOIN [003 Loyalty
Program Level] ON [01 Member Table].[Customer ID] = [003 Loyalty Program
Level].[Customer ID]) INNER JOIN [99 CLUB Primary Keys] ON [01 Member
Table].[Customer ID] = [99 CLUB Primary Keys].[Customer ID]) ON [02
Club].[ITI_Stargert ID] = [99 CLUB Primary Keys].[Club ID]
WHERE ((([01 Member Table].[Travel Voucher Y/N])=Yes));
 
Tim,

Your problem is that all of your 3683 members are not in all three tables,
and you have used inner joins to join the tables. This type of join only
returns those records that exist in all of the tables being joined.

1. Go back to your query design view.

2. Identify which of the tables has all of the records (this will be
identified in step 3 as XXX). My guess is that this is [01 Member Table].

3. Right click on each of the lines joining the various tables, and select
Join Properties. This will popup the Join Properties dialog. Select the
option (1, 2, 3) that reads "Select all records from XXX and only those
records from 'YYY' where the fields are equal.

Now, when you run the query (without the where clause) you should get all of
the records, but will have some NULL values in cells where members are
missing from one of the other two tables.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



timmycav said:
Hi Ken

Here's the SQL Statement:

SELECT [01 Member Table].[Customer ID], [003 Loyalty Program Level].[Current
Level], [01 Member Table].Name, [01 Member Table].[Name 2], [01 Member
Table].[Name 3], [01 Member Table].[Name 4], [02 Club].Title, [02
Club].Firstname, [02 Club].Lastname, [01 Member Table].[Building Name], [01
Member Table].[Unit Floor 1], [01 Member Table].[Unit Floor 2], [01 Member
Table].[Street Address], [01 Member Table].[Add Address], [01 Member
Table].City, [01 Member Table].[Post Code], [01 Member Table].State, [01
Member Table].Telephone, [01 Member Table].Fax, [01 Member Table].Email, [01
Member Table].[Email bounced Y/N], [01 Member Table].[Travel Voucher], [01
Member Table].[Travel Voucher Y/N], [01 Member Table].[Return to sender Y/N]
FROM [02 Club] INNER JOIN (([01 Member Table] INNER JOIN [003 Loyalty
Program Level] ON [01 Member Table].[Customer ID] = [003 Loyalty Program
Level].[Customer ID]) INNER JOIN [99 CLUB Primary Keys] ON [01 Member
Table].[Customer ID] = [99 CLUB Primary Keys].[Customer ID]) ON [02
Club].[ITI_Stargert ID] = [99 CLUB Primary Keys].[Club ID]
WHERE ((([01 Member Table].[Travel Voucher Y/N])=Yes));


Ken Snell (MVP) said:
Ummmm.... can you show us the SQL statement of the query that "isn't
returning all the records"? Kinda hard to troubleshoot your query when we
have no idea about its structure.
Click to expand...
Click to expand...
 
Thanks Dale. Worked like a charm.

Dale Fye said:
Tim,

Your problem is that all of your 3683 members are not in all three tables,
and you have used inner joins to join the tables. This type of join only
returns those records that exist in all of the tables being joined.

1. Go back to your query design view.

2. Identify which of the tables has all of the records (this will be
identified in step 3 as XXX). My guess is that this is [01 Member Table].

3. Right click on each of the lines joining the various tables, and select
Join Properties. This will popup the Join Properties dialog. Select the
option (1, 2, 3) that reads "Select all records from XXX and only those
records from 'YYY' where the fields are equal.

Now, when you run the query (without the where clause) you should get all of
the records, but will have some NULL values in cells where members are
missing from one of the other two tables.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



timmycav said:
Hi Ken

Here's the SQL Statement:

SELECT [01 Member Table].[Customer ID], [003 Loyalty Program Level].[Current
Level], [01 Member Table].Name, [01 Member Table].[Name 2], [01 Member
Table].[Name 3], [01 Member Table].[Name 4], [02 Club].Title, [02
Club].Firstname, [02 Club].Lastname, [01 Member Table].[Building Name], [01
Member Table].[Unit Floor 1], [01 Member Table].[Unit Floor 2], [01 Member
Table].[Street Address], [01 Member Table].[Add Address], [01 Member
Table].City, [01 Member Table].[Post Code], [01 Member Table].State, [01
Member Table].Telephone, [01 Member Table].Fax, [01 Member Table].Email, [01
Member Table].[Email bounced Y/N], [01 Member Table].[Travel Voucher], [01
Member Table].[Travel Voucher Y/N], [01 Member Table].[Return to sender Y/N]
FROM [02 Club] INNER JOIN (([01 Member Table] INNER JOIN [003 Loyalty
Program Level] ON [01 Member Table].[Customer ID] = [003 Loyalty Program
Level].[Customer ID]) INNER JOIN [99 CLUB Primary Keys] ON [01 Member
Table].[Customer ID] = [99 CLUB Primary Keys].[Customer ID]) ON [02
Club].[ITI_Stargert ID] = [99 CLUB Primary Keys].[Club ID]
WHERE ((([01 Member Table].[Travel Voucher Y/N])=Yes));


Ken Snell (MVP) said:
Ummmm.... can you show us the SQL statement of the query that "isn't
returning all the records"? Kinda hard to troubleshoot your query when we
have no idea about its structure.

--

Ken Snell
<MS ACCESS MVP>


Hi everybody.

I have been trying to run a query to show the members who have used a
voucher. The field with this information is a "Yes/No" tick (check) box.
When
I have run the query some of the members don't show up. Whenever I go into
their records to double check that the box is correctly marked, it is.

I have also run a query to show all members. My database has 3683 members,
but when I run a query which should return all of them, it only returns
2370.

I've tried everything I know, but can't get everything to show.

Any and all help would be great.

Thanks in advance.

Tim.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top