relationships

[ad602000]

I'm a newbie please be gentle,
I have 2 tables with a common field I want to join. On the primary table I have indexed the field and set no duplicates. On the secondary table I did likewise but after designing and joining the tables, I went back and allowed duplicates on the field. This allows a one to one relationship on the field,rather than a one to many.

My questions are,
what effect will this have on query results when I have more than one entry on the secondary table?
Will I get all entries returned?
What will occur if there are no corresponding entries on the secondary table?
Have I created a conflict in SQL?
TIA
Pete

 

[gusbrunston]

Hi, Pete:

So many questions!
MY first question is, why are you avoiding the one-to-many relationship? Sounds it's like what you want anyway.

Where did those questions come from? Are you getting errors? What are they? Are you not selecting all the records you want? What suggests a conflict in SQL?

Glad to help if I can. Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[ad602000]

Hi Gus,
Thanks for your reply, I started out with a one to many, but I was not getting a return for families without children. So i was trying this as an alternative. I have come to the realisation that it was not the actual relationship that was the problem,
I was using an inner join. I should have been using a left join. The thing that fooled me is that when I had set up the relationship properties to "choice 2' instead of the default 'choice 1'in the window, it didnt make a difference.
So I started reading about joins, what I found was that although I had changed the join properties I had not changed the joins. So when I went into SQL I changed all the 'inners' to 'lefts' and it works.
What fooled me was that the book I was reading inferred that this happened automatically when you changed join properties.
It was a big learning experience for me.

Thanks for your help
Pete

 

[gusbrunston]

Don't you just get sick of all those "learning experiences", though? Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com