Joining tables with duplicate entries

[Alex Martinez]

Hello,

I need some help. I have two tables that are joined together.

Table 1 field consist of:
PolicyNumber - text field
AssignedName - text field
CompeletedDate - date field


Table 2 field consist of:
Policy_Number - text field
User_id - text field
EntryDate - date field



Table 1 PolicyNumber can have duplicates it is joined with Policy_Number
from table 2.
What I want to do is if the CompletedDate from table 1 is null then I want
that particular Policy_Number to join with PolicyNumber and have the
EntryDate replacing the CompletedDate, but only if the CompletedDate is
null. Any duplicate PolicyNumber will already be populated with a
CompletedDate.and I still what the duplicate to join too. How can I do
this? Any tips will be appreciated. Thank you in advance.

 

[Cinzia]

Hello,

I need some help. I have two tables that are joined together.

Table 1 field consist of:
PolicyNumber - text field
AssignedName - text field
CompeletedDate - date field


Table 2 field consist of:
Policy_Number - text field
User_id - text field
EntryDate - date field



Table 1 PolicyNumber can have duplicates it is joined with Policy_Number
from table 2.
What I want to do is if the CompletedDate from table 1 is null then I want
that particular Policy_Number to join with PolicyNumber and have the
EntryDate replacing the CompletedDate, but only if the CompletedDate is
null. Any duplicate PolicyNumber will already be populated with a
CompletedDate.and I still what the duplicate to join too. How can I do
this? Any tips will be appreciated. Thank you in advance.

Hi Alex,
if I understand your question, try this

SELECT Table1.PolicyNumber, Table1.AssignedName, nz(table1.CompletedDate,
Table2.EntryDate) as Newdate FROM Table1
LEFT JOIN Table2 ON
Table1.Policy_Number= Table2.PolicyNumber

Bye

 

[Alex Martinez]

Thanks Cinzia,

I will give it a try.

Hi Alex,
if I understand your question, try this

SELECT Table1.PolicyNumber, Table1.AssignedName, nz(table1.CompletedDate,
Table2.EntryDate) as Newdate FROM Table1
LEFT JOIN Table2 ON
Table1.Policy_Number= Table2.PolicyNumber

Bye