Moving data.

[jjohnson88]

Hey guys. How can I select certain data from one column and then move just that selected data to a new column? Thanks!!

 

[KernelK]

An update query will do the trick

 

[Premy]

So u mean u have in ExistingColumn Item1, Item2 ... Item200, but u want to select just the interval Item31-Item40 and place that in NewColumn? Is NewColumn in the same table? If it is, on which line should the copied sequence start? on line 1 or line 31, and what happens to the remaining 170 blank entries in NewColumn; do they stay that way?

Now if NewColumn is in another table, created just to accomodate the copied sequence, an append query might do the trick easily (or maybe even better: a make table query).

HTH

 

[jjohnson88]

Ok, so here's the deal. I'm new to Access, so I don't know how to do an update query and such. But here's what I want to do. I have a table, with a bunch of columns. One column is a description of a part...a really long description. I want to essentially break up this description by searching for commonalities and then putting those commonalities in a new column within the same table, but in the same row they came from so as to have more things to search for. Does that make sense?

 

[Premy]

OK, now you're talking. The best would be to create a form with a textbox (txtSearch) and button (btnCreate). Add the following code behind the button:

Private Sub btnCreate_Click()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sSQL As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblParts", dbOpenDynaset)
sSQL = "fldPartName Like'*" & Me.txtSearch & "*'"
rs.FindFirst sSQL
Do Until rs.NoMatch
rs.Edit
rs!fldNew = Me.txtSearch
rs.Update
rs.FindNext sSQL
Loop
rs.Close
Set db = Nothing
Set rs = Nothing
End Sub

The names "tblParts", "fldPartname" and "fldNew" have to be replaced by the actual names, of course.

Now run the form and enter your "commonality" in the text box and hit the button. This will cause the code to look up every record for the search string. when it finds it, it'll copy it over to the new column on exactly the same row. It will go on doing this until there are no more records left.

HTH

 

[jjohnson88]

I'm sorry but I'm really new at this. I got the form created but then it gave me some sort of error. I don't even know if I had the names right. What is tblParts representing? The column I'm looking in?

Also, there's multiple commonalities within this column. Like there's 'raw materials' and 'unclassified' and stuff like that. Is it possible to make it where it searches this column for let's say 'unclassified', and takes all the records with that string in the specified column, and moves them to a new table called 'Unclassified'. And have it do that for each different string in that column. Thanks a bunch for your help.

 

[Premy]

I'm sorry but I'm really new at this. I got the form created but then it gave me some sort of error. I don't even know if I had the names right. What is tblParts representing? The column I'm looking in?

Well, no, "tblParts" is the table the data is in: just replace it with the actual name of your table. Likewise "fldPartName" is the name of the Column you want to get the data out; here too rename it with the actual Column name. "fldPartNew" is up to u, since it's a new field you're creating.

Also, there's multiple commonalities within this column. Like there's 'raw materials' and 'unclassified' and stuff like that. Is it possible to make it where it searches this column for let's say 'unclassified', and takes all the records with that string in the specified column, and moves them to a new table called 'Unclassified'. And have it do that for each different string in that column. Thanks a bunch for your help.

Ok, now you're talking about moving the data to a new table, but I think u mean new field(column), right? If the latter is the case then u just make sure that "fldPartNew" changes to the new name, for ex. "Unclassified", since u want the extracted data to go to this field/column. Enter "unclassified" in the textbox and hit the button. This will move all occurences of "Unclassified" to the Unclassified Column, on the same row. Now change the fieldname in the code to "RawMaterials" (of course u'll have to create a field/column in your table first with exactly that name), search for "Raw Materials" and hit the button... and so on.

HTH