Queries not returing all results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

timmycav - 18 Apr 2008 03:13 GMT

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.

Reply to this Message

Ken Snell (MVP) - 18 Apr 2008 03:23 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> Hi everybody.
>
[quoted text clipped - 15 lines]
>
> Tim.

Reply to this Message

timmycav - 18 Apr 2008 05:19 GMT

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));

> 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
[quoted text clipped - 19 lines]
> >
> > Tim.

Reply to this Message

Dale Fye - 18 Apr 2008 13:04 GMT

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

Signature

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

email address is invalid
Please reply to newsgroup only.

> Hi Ken
>
[quoted text clipped - 40 lines]
> > >
> > > Tim.

Reply to this Message

timmycav - 22 Apr 2008 03:55 GMT

Thanks Dale. Worked like a charm.

> Tim,
>
[quoted text clipped - 63 lines]
> > > >
> > > > Tim.

Reply to this Message