?
Solved

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

Posted on 2014-10-01
9
Medium Priority
?
281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
9 Comments
 
LVL 25

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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 46

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 46

Accepted Solution

by:
aikimark earned 2000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…
Suggested Courses
Course of the Month9 days, 3 hours left to enroll

764 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