Solved

MS Access - looping thru a recordset to form a single record

Posted on 2014-10-01
9
273 Views
Last Modified: 2014-10-16
I'm a novice MS Access user, perhaps you can help me figure this out. I have a dataset where there are multiple records for the same sku like this...

SKU                       ChoiceStr                              Option
YTHBLKLRG      Youth, Black, LRG                       Age
YTHBLKLRG      Youth, Black, LRG                       Color
YTHBLKLRG      Youth, Black, LRG                       Size

... and I want to loop through and convert to a single record like this...

SKU                  Option1   Choice1   Option2   Choice2   Option3   Choice3
YTHBLKLRG     Age           Youth       Color        Black        Size          LRG

Can you please help?
0
Comment
Question by:P-Daddy
  • 4
  • 2
9 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40356053
Will you always have three values in the ChoiseStr?
0
 

Author Comment

by:P-Daddy
ID: 40357081
Hi chaau.

No, unfortunately ChoiceStr might have 1,2,3 or 4 comma separated values at random. (Not more than 4) My challenge is to pair the 1st  value "Youth" with the second Option value "Age", then  "Black" with  "Color", then "LRG" with "Size"

thanks!
0
 

Author Comment

by:P-Daddy
ID: 40357089
Hi aikimark

Actually, this request is a bit different from http:Q_28399173.html which is why I asked for it to be deleted and replaced with this one. The difference is that in this question, I'm trying to pair the comma-separated value in one field (ChoiceStr) with a single value in the second field (Option) before writing out to a single record.

If we could leave this as an open question, I'd sure appreciate it. Thank you!
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 45

Expert Comment

by:aikimark
ID: 40357119
@P-Daddy

Does your table have an autonumber field?
0
 

Author Comment

by:P-Daddy
ID: 40357221
Hi aikimark

Not at this time, the example data is just in a query . But if it simplifies things, I CAN write to a table with an autonumber field.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40357321
Let me reiterate what I posted in the prior question...
use the DConcat() function in this article:
http:A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

Secondly, you will need a parsing function to pick out the delimited words in your string.
Public Function GetWord(ByVal parmString As String, ByVal parmPosition As Long, Optional parmDelim As String = ",") As String
    Static vParsed As Variant
    vParsed = Split(parmString, parmDelim)
    Select Case parmPosition
        Case 1 To UBound(vParsed) + 1
            GetWord = Trim(vParsed(parmPosition - 1))
        Case Else
            GetWord = vbNullString
    End Select
End Function

Open in new window


You could use both of these functions and either a query or another routine to merge the multi-row data with the delimited text data.

You haven't stated how you are going to use this and in what form the output needs to be.
0
 

Author Closing Comment

by:P-Daddy
ID: 40385124
Aikimark - Thank you for the solution. Good job.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now