Solved Transferring data from one field to other fileds (1 Viewer)

[Capitala]

Good day!
I have a field "Description" and other four fields "field1", "field2", "field3" and "field4"
The field "Description" has numerous data. Now, I want a code to go to first entry in "Description" and insert it into "field1" then goes to the send entry in "Description" and insert it into "field3" and the same with "filed4" and so forth till the end of Description field.
I've tried crosstab query but it created too many fileds.
Thanks in advance

 

[CJ_London]

Suggest provide some example data and the outcome required as your description is too vague.

also ‘description’ is a reserved word and should not be used as a field name

 

[Gasman]

Use Split() and update query.
However you cannot use Split() directly in a query, so need a wrapper function.

Function fnSplit(pstrText As String, iElement As Integer) As String
    Dim arrFood() As String
    arrFood = Split(pstrText, " ")
    fnSplit = arrFood(iElement)
End Function

SELECT tblFoods.FoodID, fnsplit([FoodName],0) AS Expr1
FROM tblFoods;

 

[arnelgp]

also ‘description’ is a reserved word and should not be used as a field name

i tested and it is ok, maybe what you mean is the word "Desc" since it is used in Sort Order, but also can be used if you enclosed it on square brackets.

 

[Capitala]

Suggest provide some example data and the outcome required as your description is too vague.

also ‘description’ is a reserved word and should not be used as a field name

Here's a sample of desired output with thanks

 

[arnelgp]

you will need a recordset to do that and for what?
something like this:

Private Sub t()

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim arrValue(1 To 4) As String
    
    Dim i As Integer
    Dim sql As String
    
    sql = "Select Top 1 * From [yourTableName] Where (Field1 Is Null);"
    
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("YourTableName", dbOpenSnapshot, dbReadOnly)
    Set rs2 = db.OpenRecordset(sql, dbOpenDynaset)
    With rs1
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            i = i + 1
            If i > 4 Then
                rs2.Edit
                rs2!Field1 = arrValue(1)
                rs2!Field2 = arrValue(2)
                rs2!Field3 = arrValue(3)
                rs2!Field4 = arrValue(4)
                rs2.Update
                rs2.Close
                
                arrValue(1) = ""
                arrValue(2) = ""
                arrValue(3) = ""
                arrValue(4) = ""
                
                Set rs2 = db.OpenRecordset(sql, dbOpenDynaset)
                i = 1
            End If
            arrValue(i) = !Description
            .MoveNext
        Loop
        If Len(arrValue(1)) <> 0 Then
            rs2.Edit
            rs2!Field1 = arrValue(1)
            rs2!Field2 = arrValue(2)
            rs2!Field3 = arrValue(3)
            rs2!Field4 = arrValue(4)
            rs2.Update
        End If
        .Close
        rs2.Close
    End With
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
        
End Sub

 

[gemma-the-husky]

@Capitala

You really don't want to do that you know. Why do you think you need this solution?

 

[Capitala]

you will need a recordset to do that and for what?
something like this:

Private Sub t()

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim arrValue(1 To 4) As String
   
    Dim i As Integer
    Dim sql As String
   
    sql = "Select Top 1 * From [yourTableName] Where (Field1 Is Null);"
   
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("YourTableName", dbOpenSnapshot, dbReadOnly)
    Set rs2 = db.OpenRecordset(sql, dbOpenDynaset)
    With rs1
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            i = i + 1
            If i > 4 Then
                rs2.Edit
                rs2!Field1 = arrValue(1)
                rs2!Field2 = arrValue(2)
                rs2!Field3 = arrValue(3)
                rs2!Field4 = arrValue(4)
                rs2.Update
                rs2.Close
               
                arrValue(1) = ""
                arrValue(2) = ""
                arrValue(3) = ""
                arrValue(4) = ""
               
                Set rs2 = db.OpenRecordset(sql, dbOpenDynaset)
                i = 1
            End If
            arrValue(i) = !Description
            .MoveNext
        Loop
        If Len(arrValue(1)) <> 0 Then
            rs2.Edit
            rs2!Field1 = arrValue(1)
            rs2!Field2 = arrValue(2)
            rs2!Field3 = arrValue(3)
            rs2!Field4 = arrValue(4)
            rs2.Update
        End If
        .Close
        rs2.Close
    End With
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
       
End Sub

Thanks a mile

 

[Gasman]

My sugegstion is not going to work. I thought you wanted to break down a field into it's separate parts.
As CJ_London mentioned, why would you want to do that? Creating a query to find John or any other name is going to be a nightmare?

 

[GPGeorge]

You said you tried a cross-tab, but that it created "too many fields". How many is too many? You do realize that a table can have no more than 255 fields? And if "too many" means more than that, you're out of luck again.

If this is intended to support some sort of interim reporting or analysis, perhaps there is a way to get to that result without going down this particular path. If you intend to replace the current table, though, with a new one containing all of those repeating, "spreadsheet style" fields, it's a regrettable choice.

You have a way to do this in VBA, but it would put some minds at ease if you can help us understand why this is deemed neccesary.

Thank you.

 

[The_Doc_Man]

As noted by others, "pivoting" a table from a vertical list to a horizontal list will run into limits. You are, in essence, denormalizing the table but Access was designed with normalization in mind.

If your cross-tab failed, this effort will fail for the same reasons - a field-count limitation of 255 columns in a table or query or recordset. You cannot exceed those limits regardless of how you got there.

The only mechanical way to deal with this would be to use VBA to open an Excel application and then place the names across the top row. Excel "doesn't care" so much about the number of columns you create up to a very high limit. I believe the limit is now 16K columns by 1 Million rows (numbers rounded). I doubt from your discussion that you would have more than 16K columns but if THAT fails, you are stuck.

There IS another way to look at this. Pivot your expectations. Instead of listing names across the top and (presumably) listing some kind of statistics vertically, leave the names linear and list each name's statistics horizontally.

 

[Capitala]

As noted by others, "pivoting" a table from a vertical list to a horizontal list will run into limits. You are, in essence, denormalizing the table but Access was designed with normalization in mind.

If your cross-tab failed, this effort will fail for the same reasons - a field-count limitation of 255 columns in a table or query or recordset. You cannot exceed those limits regardless of how you got there.

The only mechanical way to deal with this would be to use VBA to open an Excel application and then place the names across the top row. Excel "doesn't care" so much about the number of columns you create up to a very high limit. I believe the limit is now 16K columns by 1 Million rows (numbers rounded). I doubt from your discussion that you would have more than 16K columns but if THAT fails, you are stuck.

There IS another way to look at this. Pivot your expectations. Instead of listing names across the top and (presumably) listing some kind of statistics vertically, leave the names linear and list each name's statistics horizontally.

Thanks alot, arnelgp solution is wonderful

 

[CJ_London]

How many rows are there in your table and do you have duplicates in the data? If less than 255 unique values then you could use a crosstab - it would look something like

TRANSFORM First(Description) AS
SELECT "Row" AS Expr1
FROM tblCurrent
GROUP BY "Row"
PIVOT "Field" & [ID];

gives you this result
Query8 Query8

Expr1	Field1	Field2	Field3	Field4
Row	john	sam	michael	tom

 

[CJ_London]

i tested and it is ok, maybe what you mean is the word "Desc" since it is used in Sort Order, but also can be used if you enclosed it on square brackets.

it is in the list of reserved words

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

learn.microsoft.com

may be OK for a query in this situation, but can cause issues in other areas of Access where 'description' is a property and typically results in misleading error descriptions.

Within VBA it is used in 4 objects ;

Description is also a property of tables, queries, forms, reports and modules

plus table fields

and query objects

So by all means use it (with or without square brackets), but it may well come back to bite you

 

[Capitala]

you will need a recordset to do that and for what?
something like this:

Private Sub t()

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim arrValue(1 To 4) As String
   
    Dim i As Integer
    Dim sql As String
   
    sql = "Select Top 1 * From [yourTableName] Where (Field1 Is Null);"
   
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("YourTableName", dbOpenSnapshot, dbReadOnly)
    Set rs2 = db.OpenRecordset(sql, dbOpenDynaset)
    With rs1
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            i = i + 1
            If i > 4 Then
                rs2.Edit
                rs2!Field1 = arrValue(1)
                rs2!Field2 = arrValue(2)
                rs2!Field3 = arrValue(3)
                rs2!Field4 = arrValue(4)
                rs2.Update
                rs2.Close
               
                arrValue(1) = ""
                arrValue(2) = ""
                arrValue(3) = ""
                arrValue(4) = ""
               
                Set rs2 = db.OpenRecordset(sql, dbOpenDynaset)
                i = 1
            End If
            arrValue(i) = !Description
            .MoveNext
        Loop
        If Len(arrValue(1)) <> 0 Then
            rs2.Edit
            rs2!Field1 = arrValue(1)
            rs2!Field2 = arrValue(2)
            rs2!Field3 = arrValue(3)
            rs2!Field4 = arrValue(4)
            rs2.Update
        End If
        .Close
        rs2.Close
    End With
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
       
End Sub

Dear arnelgp,
I appreciate your excellent endeavors,
Pls, Pls, Pls,
What if I need to transpose the records of two fields on 8 fields as in the attached image
Thanks for your excellent cooperation.

 

[GPGeorge]

Dear arnelgp,
I appreciate your excellent endeavors,
Pls, Pls, Pls,
What if I need to transpose the records of two fields on 8 fields as in the attached image
Thanks for your excellent cooperation.
View attachment 113899

Sometimes people think that by aliasing their data and obscuring the real process involved, they are "simplifying" the scenario for others. In fact, that generally results in less communication, not more. Here, for example, using random words totally conceals what is really going on so no one knows if a given approach makes sense or not. There's no logical connection between the terms grouped under the different fields, so it's impossible to know what the grouping principle would be or how "vegetables" and "internet" go together. It's more confusing to use these aliased terms for your context, not less.

Tell us, please, in plain language, why you want to do this. What is the data in reality and what is the end goal here? Help us help you.

Also, when you ignore questions and avoid responding to good advice, it gets more difficult to encourage further good advice. Why bother when it's going to be ignored anyway?

Finally. There is a technical aspect and a practical aspect to most situations. While the technical answer may be correct in itself, it may be utterly impractical as a solution. Knowing how to implement an inappropriate solution leads only to an inappropriate solution.

Please, help us help you. Explain the context, the end goal and the real data required. Thank you.

 

[Capitala]

Sometimes people think that by aliasing their data and obscuring the real process involved, they are "simplifying" the scenario for others. In fact, that generally results in less communication, not more. Here, for example, using random words totally conceals what is really going on so no one knows if a given approach makes sense or not. There's no logical connection between the terms grouped under the different fields, so it's impossible to know what the grouping principle would be or how "vegetables" and "internet" go together. It's more confusing to use these aliased terms for your context, not less.

Tell us, please, in plain language, why you want to do this. What is the data in reality and what is the end goal here? Help us help you.

Also, when you ignore questions and avoid responding to good advice, it gets more difficult to encourage further good advice. Why bother when it's going to be ignored anyway?

Finally. There is a technical aspect and a practical aspect to most situations. While the technical answer may be correct in itself, it may be utterly impractical as a solution. Knowing how to implement an inappropriate solution leads only to an inappropriate solution.

Please, help us help you. Explain the context, the end goal and the real data required. Thank you.

I appreciate your advises and your time you have spared. Indeed, the information provided is indicative only. I have many users filling in two fields only. That filed became too lengthy. That's why I need to transpose them into other multiple fields. Thanks again

 

[Gasman]

Your dB is not constructed correctly. If it was, you would not have these issues. You are trying to put a plaster on a hemorrhage.

Construct your db correctly, then this would not be required.
What happens when you have a third field and more?

 

[June7]

"That filed became too lengthy." - exactly how is this field "too lengthy"? Each data element is in its own record. This is how it should be.

What other fields are in source table - ID, Date, etc?

How many possible Desc elements are there? Could there be repetitions?

WHY would you want to save data is this bizarre arrangement where elements have no organized associations? It could be done with VBA but it really doesn't make sense to me.

Perhaps you should just build a multi-column report using the source table as is.

 

[GPGeorge]

I appreciate your advises and your time you have spared. Indeed, the information provided is indicative only. I have many users filling in two fields only. That filed became too lengthy. That's why I need to transpose them into other multiple fields. Thanks again

Your "indicative" names ,unfortunately, hide more than they reveal about what's going on. The problem is that the longer this discussion goes, and the more details we are able to coax out of you, the more clear it becomes this is not an appropriate relational database design.

You have the start of a VBA method that might lead to the desired table structure, but the problems will only be shifted into new aspects of that design. So I suspect you'll continue down that path.

Good luck, though.