Rearranging Data in an MS Access 2007 table into a new column

[Moses Wati]

Hi,

I have Access tables with fields that have the following format:

--------------------------------------
|CONSIGNEE_NAMES| CONFIRMATION |CONSIGNOR_NAMES|
--------------------------------------
|WATI SMITH    |              | NAUMI WILLIS  |     
|ALAIN PHELLY  |              | BAHATI BOB    |
|MOSES NJESH   |              | LIL TONY      |
|NAUMI WILLIS  |              | WATI SMITH    |
|Max BROWN     |              | MIKE NJAGA    |
|LIL TONY      |              | WAWERU PETER  |
|VICTOR SEKE   |              | VICTOR SEKE   |   
|UHURU KAZI    |              | JANET WECHE   |  
|GEORGE KARI   |              |               |
|KATE FIONA    |              |               |
|MILLIY WENDY  |              |               |
 

-----------------------------------

I need field CONFIRMATION to pic any name from field CONSIGNEE_NAMES that matches the names in field CONSIGNOR_NAMES as shown below..

----------------------------------------------
|CONSIGNEE_NAMES| CONFIRMATION |CONSIGNOR_NAMES|
-----------------------------------------------
|WATI SMITH    |NAUMI WILLIS  | NAUMI WILLIS  |     
|ALAIN PHELLY  |              | BAHATI BOB    |
|MOSES NJESH   |LIL TONY      | LIL TONY      |
|NAUMI WILLIS  |WATI SMITH    | WATI SMITH    |
|Max BROWN     |              | MIKE NJAGA    |
|LIL TONY      |MOSES NJESH   | MOSES NJESH   |
|VICTOR SEKE   |VICTOR SEKE   | VICTOR SEKE   |   
|UHURU KAZI    |              | JANET WECHE   |
|GEORGE KARI   |              |               |
|KATE FIONA    |              |               |
|MILLIY WENDY  |              |               |

Kindly help..

[twinnyfo]

Moses,

I do not understand what you are trying to do. Based on your example, only some of the names in the field CONSIGNOR_NAMES were added to the field CONFIRMATION. What is the determining criteria for copying the name?

[Moses Wati]

twinnyfo,

well twinnyfo,that is exactly what I need, I need field CONFIRMATION to receive the names that match field CONSIGNOR_NAMES...those names will be pasted in that field (CONFIRMATION)and the names that don't match field CONSIGNOR_NAMES to be deleted.

[Rabbit]

Outer join the table to itself on consignee to consignee and consignor to consignee

[Moses Wati]

Yes that's what I need to do @Rabbit

[NeoPa]

So, in a nutshell, you want the [Confirmation] field to be updated to reflect the [Consignor_Names] field for each record where the [Consignor_Names] value matches matches any one of the values in [Consignee_Names]? Actually, even though you don't say so, it looks like your example data is matching those records (Either single records or pairs.) where Consignor matches Consignee as well as Consignee matching Consignor.

I suspect Rabbit's suggestion was intended to reflect that but appears to have a typo.

Rabbit:
Outer join the table to itself on consignee to consignor and consignor to consignee

I've underlined where I think that was.

I believe, if the requirement is actually as specific as I suspect it is, then you'll need just the link between Consignee and Consignor (OUTER JOIN). You would then filter on (and update only if) the Consignee of the JOINed version of the table matches Consignor of the main one.

The next step is for you to go away and apply that advice in your project and let us know how you get on. No-one here will simply do it for you.