Clarification on my Join question

[EveatEden]

Basically Table A contains a list of insurance policies while Table B contains a list of accidents.  Some 10 or so fields are the same in Table A as in Table B.  Those fields are used to find matching records and then those records are joined and placed in Table C.  The problem is that Table B may have multiple accidents listed for the same policy number.  I need Table C to have only one record per policy number.  I was just talking to someone else who said if I used distinct in front of the field for the policy number instead of for the Select statement itself that is should work.  Does that sound right to you guys?  If not do you have another solution?

 

[MikeLacey]

Don't *think* so.<br><br>My understanding is that you have to say<br><br>Select Distinct &lt;field_list<br> <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[calahans]

Myabe you need to question why you are doing this. <br><br>Why are you creating a table from data in two other tables? If you require information you should just join the existing two, not create a third. This can lead to data redundancy and integrity issues.<br><br>Also, why are you trying to combine a one to many relationship into a one to one relationship?<br><br>If you have valid reason for doing this, then I'm sure it could be done. <br><br>I'm&nbsp;&nbsp;not critising your design, I'm only saying that maybe you should review it.<br><br> <p>Cal<br><a href=mailto: > </a><br><a href= > </a><br>

 

[bigscouse]

If you select distinct then you do no not control which records you select. <br>You may for instance only require the latest accident in which case you need to create a where clause to select on the latest date only. <p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>