Lost on update query

[Guest]

Here is where I am...
Current
Tbl. PO_ITEM_NBR Result PO_ITEM PO_NBR
123344 001 123344 001
123344 002 123344 002
123455 001 123455 001
etc.

This can be a permament change because the data will not be manuplated. I am
not sure where the best place to do this. The data will be displayed in a
form and used in a report.

So I am going to use a Update Query (correct?) to do this. Where do I add
the new columns - tbl or do I need to create a new table with all of the
fields I will be needing to keep?

In the update query:
FIELD: - Should this be my new field names, PO_NBR and PO_ITEM_NBR?
TABLE: - Should this be tbl?
UPDATE TO: I thought this would be my update criteria such as
Field: PO_Item: LEFT(Po_Item_Nbr, Instr(1, Po_Item_Nbr & " "," ")-1)
Field: Po_Nbr: Mid(Po_Item_Nbr, Instr(1, Po_Item_Nbr & " "," ")+1)????
CRITERIA: I want all rows, so do I need to add anything?

All help is appricated.

Thanks,

 

[Guest]

Here is where I am...
Current
Tbl. PO_ITEM_NBR Result PO_ITEM PO_NBR
123344 001 123344 001
123344 002 123344 002
123455 001 123455 001
etc.

This can be a permament change because the data will not be manuplated. I am
not sure where the best place to do this. The data will be displayed in a
form and used in a report.

So I am going to use a Update Query (correct?) to do this. Where do I add
the new columns - tbl or do I need to create a new table with all of the
fields I will be needing to keep?

In the update query:
FIELD: - Should this be my new field names, PO_NBR and PO_ITEM_NBR?
TABLE: - Should this be tbl?
UPDATE TO: I thought this would be my update criteria such as
Field: PO_Item: LEFT(Po_Item_Nbr, Instr(1, Po_Item_Nbr & " "," ")-1)
Field: Po_Nbr: Mid(Po_Item_Nbr, Instr(1, Po_Item_Nbr & " "," ")+1)????
CRITERIA: I want all rows, so do I need to add anything?

Need to update:
I see there is a SQL update which is used. Where in the system can you use
straight sql. Then I would not have a issue.

 

[Carl Rapson]

Need to update:
I see there is a SQL update which is used. Where in the system can you
use
straight sql. Then I would not have a issue.

If you create a new query in Design view and select no tables, then switch
to SQL view, you'll see a blank screen you can use to type in your own SQL
statement. This SQL code should do the trick:

UPDATE [Tbl]
SET [PO_ITEM]=Left(PO_ITEM_NBR,InStr(PO_ITEM_NBR," ")-1),
[PO_NBR]=Mid(PO_ITEM_NBR,InStr(PO_ITEM_NBR," ")+1)

Carl Rapson