Split an array field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Phillips - 05 Dec 2005 20:06 GMT

I have a linked table to Access that consolidates sales history into 12
entries within a single field. I would like to query this table based on a
date range - say July 2003 through September 2004. In order to do this I need
to sum the proper elements of the field. Is there a fairly simple way to
achieve this without creating new tables where the data is broken out into
individual columns? I am using Access 2K3.

Reply to this Message

Klatuu - 05 Dec 2005 20:19 GMT

See what problems bad design can create? Twelve values stuffed into one
field. Whomever designed that should be stuffed in a bag and thrown in a
field.
Enough soap box, let's solve the problem:

So how are the 12 elements delimited, in other words, how do you pull out
one value and know what it represents?
All you need to do is create a query and break the individual fields out
into the query. For each of the columns, instead of putting a field name in
the query builder, use an expression that will give you the value you want.

> I have a linked table to Access that consolidates sales history into 12
> entries within a single field. I would like to query this table based on a
> date range - say July 2003 through September 2004. In order to do this I need
> to sum the proper elements of the field. Is there a fairly simple way to
> achieve this without creating new tables where the data is broken out into
> individual columns? I am using Access 2K3.

Reply to this Message

Bill Phillips - 05 Dec 2005 20:28 GMT

The elements are comma delimited. My filed could have the following data:
1,2,3,4,5,6,7,8,9,10,11,12

Element 1 represents the qty sold of an item in January, Element 2 qty sold
in Feb etc. The year is specified in it own field. So for the last 5 years of
sales I would have 5 records, each record having 12 qty elements. I believe
the table was created this way to save space and increase response time by
limiting the number of records needed to query. The table is linked to my ERP
program which means no changes to the table design.

My question is How to write the query? I'm not sure on how to specify the
elements I need.

> See what problems bad design can create? Twelve values stuffed into one
> field. Whomever designed that should be stuffed in a bag and thrown in a
[quoted text clipped - 13 lines]
> > achieve this without creating new tables where the data is broken out into
> > individual columns? I am using Access 2K3.

Reply to this Message

David C. Holley - 05 Dec 2005 20:54 GMT

That at least makes life easier. The Split() takes a delimited value and
loads up an array with eacy individual value. Roughly, and this *is* of
the top of my head...

myArray() = Split([string], ",")

should do it.

> The elements are comma delimited. My filed could have the following data:
> 1,2,3,4,5,6,7,8,9,10,11,12
[quoted text clipped - 26 lines]
>>>achieve this without creating new tables where the data is broken out into
>>>individual columns? I am using Access 2K3.

Reply to this Message

Klatuu - 05 Dec 2005 21:24 GMT

Here is a function you can call from your query to split the data into months:

Function SplitIt(ByVal lngNdx As Long, ByVal strSplit As String) As Long
Dim varaSplit As Variant

varaSplit = Split(strSplit, ",")
SplitIt = varaSplit(lngNdx)
End Function

You would call it in your query by using expressions instead of fields in
the query builder. For example purposes, we will call the field with the
values in it HIST_VALS

So, Jan: SplitIt(0, HIST_VALS) | Feb: SplitIt(1, HIST_VALS)
and so on for all the months.

This will split the data out into a query for your. That is the easy part.
The fun part will be excluding the months you don't want. That you will
have to do in the criteria row of the query, but you run into the problem as
in your example, you can't exclude January because you have January in one of
the years. I will leave that to you.

> The elements are comma delimited. My filed could have the following data:
> 1,2,3,4,5,6,7,8,9,10,11,12
[quoted text clipped - 26 lines]
> > > achieve this without creating new tables where the data is broken out into
> > > individual columns? I am using Access 2K3.

Reply to this Message

Bill Phillips - 05 Dec 2005 21:51 GMT

Klatuu,

Sorry, I'm having some conceptual problems still. I'm fairly new to the
programming aspect so bear with me. I placed the SplitIT function in a module
(named SplitIt) and declared it as a public function. However when I try to
use it in my qyery I get the following message: Undefined Function SplitIt in
Expression

Am I putting the code in the wrong place or am I missing some sort of
declaration to make the code run.

Thanks,

> Here is a function you can call from your query to split the data into months:
>
[quoted text clipped - 48 lines]
> > > > achieve this without creating new tables where the data is broken out into
> > > > individual columns? I am using Access 2K3.

Reply to this Message

David C. Holley - 05 Dec 2005 22:17 GMT

Try opening up the VBA editor and in the Immediate Window type
?SplitIt(3, "2,6,7,10,11")

You should get 7 as the return value. (Unless Split() is zero-based in
which case you'll get 10, can't remember.)

The statement above ?SplitIt([]) doesn't work in the Immediate Window,
the problem is with the Sub, if it works the problem will be with how
you're using it in the query WBTW would be SplitIt([position of value],
[list of values])

> Klatuu,
>
[quoted text clipped - 61 lines]
>>>>>achieve this without creating new tables where the data is broken out into
>>>>>individual columns? I am using Access 2K3.

Reply to this Message

Klatuu - 05 Dec 2005 22:59 GMT

Description

Returns a zero-based, one-dimensional array containing a specified number of
substrings.

See Chapter 14 of my road trip novel.

> Try opening up the VBA editor and in the Immediate Window type
> ?SplitIt(3, "2,6,7,10,11")
[quoted text clipped - 72 lines]
> >>>>>achieve this without creating new tables where the data is broken out into
> >>>>>individual columns? I am using Access 2K3.

Reply to this Message

Douglas J. Steele - 05 Dec 2005 22:45 GMT

While I see you've solved your problem, I thought I should point out that
you cannot name the module the same as routines that are within it. In other
words, since the function name is SplitIt, the module cannot be named
SplitIt.

Many of us use a naming convention, making all modules start with bas or
mdl, to avoid this problem.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Klatuu,
>
[quoted text clipped - 86 lines]
>> > > > out into
>> > > > individual columns? I am using Access 2K3.

Reply to this Message

John Spencer - 05 Dec 2005 23:16 GMT

Change the name of the module. The name space for module names and functions
and subs is shared. I would just add mod to the front of the module name and
make it "modSplitIt"

> Klatuu,
>
[quoted text clipped - 61 lines]
> > > > > achieve this without creating new tables where the data is broken out into
> > > > > individual columns? I am using Access 2K3.

Reply to this Message

Bill Phillips - 05 Dec 2005 22:05 GMT

Never mind the last post, I got it to work. I guess I didn't put into the
module exactly like Access wanted me too. When I went back & did an insert
Function it worked correctly.

Reply to this Message

Douglas J Steele - 05 Dec 2005 20:21 GMT

No, there really isn't a good way to do what you want, but even if there
was, you're FAR better off fixing the data model so that it doesn't store
multiple values in a single field.

Not only are you storing multiple values in a one field, but you're hiding
data (the date to which the particular value is related). You could use the
Split function to break your twelve entries into an array, but how do you
know that the first element of the array corresponds to July, 2003 and the
12th element to September 2004 (especially since July 2003 to September 2004
represents 15 months!)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message