Solved

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

Posted on 2014-10-01
9
274 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

785 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