How do I break out data from a field on a form

[puforee]

I have a table where on field can contain more than one item of information.
Example:
Field 3 data
Record 1 00-00
Record 2 05-00
Record 3 51-00, 54-00, 57-00

When I am on my Form I want to see a sub-form that list the falues for each record.

The query should build a temp table to contain the individual values.
So, if my form is looking at the files in Record 3...a sub form would show
51-00
54-00
57-00
Instead of what is shown above. NOTE: there could be as many as 5 items in Field 3 seperated by commas.
Can I do this with a query? I know how to split out the pieces but how do I build the temp table with the split out pieces stored in the same field name in multiple records. Vertically other than horizontally.

 

[kjv1611]

First of all, I think this may be done easier in VBA if you're comfortable messing with array variables and the Split function.

Secondly, to do it in queries, I think you need to use 2 queries, and then union the results with a 3rd query. here's that idea:
1. Query1 = everything where Len(Field 3) < 6 - so it takes out those that have multiple possible values
2. Query2 = everything where Len(Field3) > 5 - so it includes only those with multiple values
3. Query3 = Assuming you split the values in Query2, build a cross-tab query of those values. The hard part here is I don't remember offhand whether you can build a dynamic crosstab query to handle them. Then again, if you just do this step by step, perhaps, then maybe it'll work without being dynamic, b/c it'll just get the furthest out count of columns (so 5 according to your comments)
4. Query4 = Union query to stick together Query1 and Query3

And actually not sure it'll work... I'm talking about turning rows into columns with a cross-tab, not sure you can do that in reverse... but if there is a way to query that way, then you could try that.. I just can't think off hand how to handle that step.

If it were me, I think I'd just do vba. So this is how I'd do it in vba, assuming you're comfortable there:
1. Use a DAO Recordset for the original data (read from), and another for the table you're writing to.
2. Use an array formula to capture the individual pieces of Field 3 by using the Split function
3. Loop through those pieces using LBound and UBound functions to stay within the possible values for the given row/record.
4. As you get to each piece of the array, add that value of the array as a new record in the recordset you're building/adding records to.

Anyway, unless I"m missing something, I'd probably just do the vba method. It might sound more complicated, but best I can tell, it's the simplest solution in actuality.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[dhookom]

You shouldn't really be storing multiple values in a single field. Maybe you don't have control over the system. If this is the case I would use a small user-defined function to parse out the separate values:

Public Function ParseText(pstrText As String, intElement As Integer, _
        pstrDelimiter As String) As Variant
    Dim arText() As String
    On Error GoTo ParseText_Error
    arText() = Split(pstrText, pstrDelimiter)
    ParseText = arText(intElement - 1)
    
ExitParseText:
   On Error GoTo 0
   Exit Function

ParseText_Error:
    Select Case Err
        Case 9 'subscript out of range
            'don't do anything
        Case Else
            MsgBox "Error " & Err.Number & " (" & _
                Err.Description & ") in procedure ParseText of Module basParseText"
    End Select
    Resume ExitParseText
End Function

You can then create a union query that normalizes your data:

SELECT pufID, Field3, 1 as Position, ParseText([Field3],1,", ") AS Element
FROM tblPuforee
UNION ALL
SELECT pufID, Field3, 2, ParseText([Field3],2,", ")
FROM tblPuforee
WHERE ParseText([Field3],2,", ") Is Not Null
UNION ALL
SELECT pufID, Field3, 3, ParseText([Field3],3,", ")
FROM tblPuforee
WHERE ParseText([Field3],3,", ") Is Not Null
UNION ALL
SELECT pufID, Field3, 4, ParseText([Field3],4,", ")
FROM tblPuforee
WHERE ParseText([Field3],4,", ") Is Not Null
UNION ALL
SELECT pufID, Field3, 5, ParseText([Field3],5,", ")
FROM tblPuforee
WHERE ParseText([Field3],5,", ") Is Not Null
ORDER BY 1,3;

Your finished query can be used as the record source of a subform:

Output from query with the field3 data included for reference:
[pre]pufID Field3 Position Element
1 00-00 1 00-00
2 05-00 1 05-00
3 51-00, 54-00, 57-00 1 51-00
3 51-00, 54-00, 57-00 2 54-00
3 51-00, 54-00, 57-00 3 57-00
4 12-34, 56-78, 90-12, 34-56, 78-90 1 12-34
4 12-34, 56-78, 90-12, 34-56, 78-90 2 56-78
4 12-34, 56-78, 90-12, 34-56, 78-90 3 90-12
4 12-34, 56-78, 90-12, 34-56, 78-90 4 34-56
4 12-34, 56-78, 90-12, 34-56, 78-90 5 78-90[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I have a table where on field can contain more than one item of information.

Is that a normal field with values separated by a comma, or is that a multivalue field?

https://support.office.com/en-us/ar...8e4-b6dc-6de9bebbec31?ui=en-US&rs=en-US&ad=US

If it is a multivalue field then you would return a recordset of the values, but then write them to a temp table.
I am assuming this is just a normal field.

 

[puforee]

Thank all of you for your inputs. I have not had a chance to get back to this issue but I will in the next few days....holidays not included. I appreciate all the feedback and I will let you know how it comes out.

Thanks,

 

[MajP]

If you actually use a true multivalue field then you can actually query this automatically. In fact this is what a MV field was designed for.
Here is a real MV field. It looks like there are values seperated by a comma, but that is just a representation. In fact the values are stored in another table in a normalized structure behind the scenes.

So you can actually query it. Notice that
Productselected.value is a child value (it is indented)

And this query produces the following

SELECT 
 selectedProducts.ID, 
 selectedProducts.productsSelected.Value
FROM selectedProducts;

ID	selectedProducts.productsSelected.Value
3	Carnarvon Tigers
3	Chang
3	Boston Crab Meat
3	Chef Anton's Cajun Seasoning
4	Boston Crab Meat
4	Camembert Pierrot
5	Chang
5	Chef Anton's Gumbo Mix
6	Chai
6	Carnarvon Tigers
7	Chang
7	Chai
8	Chartreuse verte
8	Chang
9	Chartreuse verte
9	Chai
9	Chang
9	Carnarvon Tigers
9	Boston Crab Meat
9	Aniseed Syrup
9	Camembert Pierrot
9	Chef Anton's Cajun Seasoning
9	Chef Anton's Gumbo Mix
11	Chang
11	Chai

Which is the normalized view of the data. Super easy because that what MV fields were designed to do.


99.9% of Access users do not understand how MV fields work, and because of this they can get into trouble. I am not suggesting you use a multivalue fields, but they have a place if you understand them.

My preference would be
1. store multiple values in a child table
2. Use a MV field
and extremely distant last
3. concatenate values in a single field