• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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