I have a db with several tables.  Two of the tables Owners and FeesPaid 

have a One-To-Many relationship.

The many side, FeesPaid, has a field OwnersID which is the primary key of 

the Owners table.



The Indexed property of the OwnerID field in the Owners table is set to 

Yes (No Duplicates).  The Indexed property of the OwnersID field in the 

FeesPaid table is set to Yes (Duplicates OK).



Both tables are populated with about 40 records.  I ran a select query 

using fields from both tables and nothing shows up.



I checked in the query window and right click on the join line between 

both tables, option 1 (only include rows where the join fields from both 

fields are equal) is the selected item.



If I switch to option 2 (ALL records from Owners table.....) I get all 

records from the Owners table and nothing from the FeesPaid table



Selecting the third option (All records from FeesPaid Table....) I get all 

records from this table and nothing from the Owners table.



I know I should be getting records from both tables when option 1 is 

selected but it is not working.



Can someone assist me with resolving this.



Thanks a lot

Have you looked at both tables individually to see if the values in the

OwnersID fields are the same?  It sounds like they are not.  How is the

value from the OwnersID field in the Owners table being placed into the

FeesPaid field?



John Ruff - The Eternal Optimist :-)







-----Original Message-----

From: Howard Stone [mailto:ququmber@h...] 

Sent: Thursday, December 20, 2001 12:23 AM

To: Access

Subject: [access] one to many relationship





I have a db with several tables.  Two of the tables Owners and FeesPaid 

have a One-To-Many relationship.

The many side, FeesPaid, has a field OwnersID which is the primary key

of 

the Owners table.



The Indexed property of the OwnerID field in the Owners table is set to 

Yes (No Duplicates).  The Indexed property of the OwnersID field in the 

FeesPaid table is set to Yes (Duplicates OK).



Both tables are populated with about 40 records.  I ran a select query 

using fields from both tables and nothing shows up.



I checked in the query window and right click on the join line between 

both tables, option 1 (only include rows where the join fields from both



fields are equal) is the selected item.



If I switch to option 2 (ALL records from Owners table.....) I get all 

records from the Owners table and nothing from the FeesPaid table



Selecting the third option (All records from FeesPaid Table....) I get

all 

records from this table and nothing from the Owners table.



I know I should be getting records from both tables when option 1 is 

selected but it is not working.



Can someone assist me with resolving this.



Thanks a lot

Thanks John.  After reading your response I realised what I was doing 

wrong.  I input the OwnersID field in the FeesPaid form from a combobox 

derived from a query and bound the column to the OwnerName instsed of the 

OwnerID.  When I changed it and bound to the owner ID it worked.



There is one follw up. There are 40 owners and not all have paid their 

fees.  If I change the join property to option 2 (Include ALL records from 

Owners Table and only those record from the FeesPaid where the joined 

fields are equal) I should get all 40 owners and a blank field in the 

FeesDue table when the fees have not been paid.  I am not geting that.  

All three options return 34 records and does not give me the name for 

those who have not paid.



How can I find out the names of those who have not paid.  This is the most 

important information I need to get



Thanks

Create another query and link both tables as you did in your sample.

Make sure a field from the FeesPaid is in the query grid (for this

example, we will call the field AmountPaid).  The criteria for

AmountPaid from the FeesPaid table will be Is Null. 



You are telling the query to view all the Owners in the Owners Table and

only those records from the FeesPaid table where AmountPaid is null.



I hope this is making sense.



John Ruff - The Eternal Optimist :-)







-----Original Message-----

From: Howard Stone [mailto:ququmber@h...] 

Sent: Thursday, December 20, 2001 1:54 PM

To: Access

Subject: [access] Re: one to many relationship





Thanks John.  After reading your response I realised what I was doing 

wrong.  I input the OwnersID field in the FeesPaid form from a combobox 

derived from a query and bound the column to the OwnerName instsed of

the 

OwnerID.  When I changed it and bound to the owner ID it worked.



There is one follw up. There are 40 owners and not all have paid their 

fees.  If I change the join property to option 2 (Include ALL records

from 

Owners Table and only those record from the FeesPaid where the joined 

fields are equal) I should get all 40 owners and a blank field in the 

FeesDue table when the fees have not been paid.  I am not geting that.  

All three options return 34 records and does not give me the name for 

those who have not paid.



How can I find out the names of those who have not paid.  This is the

most 

important information I need to get



Thanks

Thanks John; The database keeps record of FeesPaid on a monthly basis 

starting September 2001. Each Owner should have 4 entires in the FeesPaid 

table one for Sept, Oct, Nov and Dec.



One Owner has not paid any fees and therefore has no entry in the FeesPaid 

Table. Three other owners have paid for Sept, Oct, and Nov but have not  

for December



When I do as you say only the person who have made no payments shows up.

Those who have paid for Sept, Oct, Nov but not for Dec did not show up.  

When I filter for the month of December to see who have not made the 

December payment nothing shows.



What I would like to do is to be able to run a query and for any month be 

able to see all those who have not paid any fees.



I hope I am clear and you are able to help me.  This is the problem that 

is preventing me from completing this project.



Thanks again for your help.



Howard

Howard,



Can you send me your db so I can see what you've got?  I have a sense

that it may need some modifications but it's hard to tell without

looking at it.



John Ruff - The Eternal Optimist :-)







-----Original Message-----

From: Howard Stone [mailto:ququmber@h...] 

Sent: Friday, December 21, 2001 2:38 AM

To: Access

Subject: [access] Re: one to many relationship





Thanks John; The database keeps record of FeesPaid on a monthly basis 

starting September 2001. Each Owner should have 4 entires in the

FeesPaid 

table one for Sept, Oct, Nov and Dec.



One Owner has not paid any fees and therefore has no entry in the

FeesPaid 

Table. Three other owners have paid for Sept, Oct, and Nov but have not



for December



When I do as you say only the person who have made no payments shows up.

Those who have paid for Sept, Oct, Nov but not for Dec did not show up.



When I filter for the month of December to see who have not made the 

December payment nothing shows.



What I would like to do is to be able to run a query and for any month

be 

able to see all those who have not paid any fees.



I hope I am clear and you are able to help me.  This is the problem that



is preventing me from completing this project.



Thanks again for your help.



Howard

I will send it but your email address is truncated.



If you send me your full email address I will send it 



Thanks





Howard

My address is papparuff@c...



John Ruff - The Eternal Optimist :-)







-----Original Message-----

From: Howard Stone [mailto:ququmber@h...] 

Sent: Friday, December 21, 2001 2:44 PM

To: Access

Subject: [access] Re: one to many relationship





I will send it but your email address is truncated.



If you send me your full email address I will send it 



Thanks





Howard