Get relative value from associated matching fields

[dazcox]

Hi Guys,

I have a list of company IDs and Unique reference Nos (that show which system they are) and a similar list which has old Company IDs and Unique Ref Nos from systems.

What I need to do is match the NewSystemRefs with the OldSystemRefs and put the relevant CompanyID against it - hope I'm making sense:

NewID NewSystemRef MATCHES OldID OldSytemRef
1 ARL01 999 853 CPR39
2 CCR76 999 ARL01
3 CPR39 853 685 DID76


Basically, the MATCHES column shows the OldCompanyID against the NewCompanyID where the System refs match.

I can get a ref to show where there is a match but cannot get the relevant OldID to show. Please help.

I am using CR9 and the data is in Excel.

 

[lbass]

Not sure I understand why the records would not align with their old system refs, but... Try adding the table again so that you have Table and Table_1. Create a left join from {Table.NewSystemRef} to {Table_1.OldSystemRef}. This assumes that you want New System Refs that have null matches to appear in the report.

-LB