Creating multiple records with a comma separated field

[pfuesting]

I have a query in Access that is producing this:
V1 V2 Count
Group 1 PT1, PT2, PT3 1
Group 1 PT4, PT5 1
Group 1 PT5,PT6 1
Group 2 PT7 2
Group 3 PT8, PT9 1

But I need it to create a new record for the comma separated values. The data should look like this:
V1 V2 Count
Group 1 PT1 1
Group 1 PT2 1
Group 1 PT3 1
Group 1 PT4 1
Group 1 PT5 2
Group 1 PT6 1
Group 2 PT7 2
Group 3 PT9 1
Group 3 PT8 1

Can anyone provide the code that would make this happen?

 

[Andrzejek]

This is what your query gives you.

How about showing us what your data looks like in your table(s)

Have fun.

---- Andy

 

[pfuesting]

Sure, here is the data:
v1 v2 case number
Group 1 PT1, PT2, PT3 123
Group 1 PT4, PT5 125
Group 1 PT5,PT6 124
Group 2 PT7 126
Group 3 PT8 127

It doesn't really look that different. I am justing counting the number of case numbers for each v1 and v2. The only problem is that the v2 column may have multiple values that comma separated.

 

[SkipVought]

hi,

Is this a one-time thing?

It pretty simple to do in Excel using the process faq68-5287

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pfuesting]

This is not a one time thing and would need to be repeated. Therefore it has to be automated in Access for the users. This information will be used to display on a report and has to be parsed out.

 

[Golom]

There is faq701-6293 that does that although it is a bit complex.

 

[pfuesting]

Thanks for the response, but I am hoping to find VBA code that would be able to do this without all the complex queries.

 

[PHV]

I am hoping to find VBA code that would be able to do this
What have you tried so far and where in your code are you stuck ?
Tip: use a Recordset and the Split function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pfuesting]

I have no code to do this.

 

[PHV]

I have no code to do this
So, you expect someone here writes the code for you ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886