Query not returning all records

[wv_guy_26301]

Query not returning all records **Issuse Solved **

This is the Access 2007 database i'm working on and I'm hitting a wall.
When you run "Member Query" it does not return all the records in the database

The field's in the query are

Member:Last name
Member:First name
Member:chapter
Options: ID

There is a relationship between Member:ID and Options:sponsor number

What I'm trying to do is list member names and just the ID numbers of thier guest and spouse. I want all members listed even if they do not have a guest or spouse.

how I have it set up, if a member has a spouse then that spouse puts the members ID number in their sponsor number box. when the query is run, the spouse's ID is listed with the members name. but if a member is alone and the member's ID is not listed in anyones sponsor box their name IS NOT returned in the query.


Below is the Database i'm talking about, Thanks for any help!

 

[RuralGuy]

"There is a relationship between Member:ID and Options:sponsor number"
Your relationship menu does not show this relationship and neither table has a PrimaryKey field (AutoNumber would be best).

 

[wv_guy_26301]

"There is a relationship between Member:ID and Options:sponsor number"
Your relationship menu does not show this relationship and neither table has a PrimaryKey field (AutoNumber would be best).

I am seeing the relationship on mine, Remember i'm using 2007.

I didn't select autoNumber on purpose. I needed to be able to delete records and reuse the ID number.

I didn't notice the Primary key, Thank You.

Any ideas on the other issuse?

 

[RuralGuy]

You have defined a 1:1 relationship in the Relationship menu. You are using a different relationship in your query that is not in the Relationship menu. I was using ac2010 to look at your db. Since you do not have Referential Integrity (RI) enabled it is not surprising that there are records where the two tables do not match and so the query will not return them which is the Join you are using. Why on earth would you want to reuse a number?

 

[wv_guy_26301]

You have defined a 1:1 relationship in the Relationship menu. You are using a different relationship in your query that is not in the Relationship menu. I was using ac2010 to look at your db. Since you do not have Referential Integrity (RI) enabled it is not surprising that there are records where the two tables do not match and so the query will not return them which is the Join you are using. Why on earth would you want to reuse a number?

I Turned on the Referential Integrity and selcted Cascade update related fields. and its still not returning all the records.

As for the numbers its a long story LOL

What do I need to do to fix it so I can get all the records from the query?

 

[RuralGuy]

Go into your query in design mode and change the Join to an Outside or Left Join.

 

[wv_guy_26301]

Go into your query in design mode and change the Join to an Outside or Left Join.

That did it.. Thank you for all your help, If you have any other suggestions that would help please feel free speak up. Again thank you

 

[RuralGuy]

AutoNumber PrimaryKey fields in both tables. You can always put a "no duplicates" index on any field if you want Access to warn you about duplicates. Keep in mind that in the real world there are very few 1:1 relationships. Do you just have too many fields for one table?