Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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

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
P-Daddy
Asked:
P-Daddy
  • 4
  • 2
1 Solution
 
chaauCommented:
Will you always have three values in the ChoiseStr?
0
 
P-DaddyAuthor Commented:
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
 
P-DaddyAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
aikimarkCommented:
@P-Daddy

Does your table have an autonumber field?
0
 
P-DaddyAuthor Commented:
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
 
aikimarkCommented:
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
 
P-DaddyAuthor Commented:
Aikimark - Thank you for the solution. Good job.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now