Making use of multiple values in the same field?

[Whizzo]

Hi all;

Here's a record in my table for you:

ID AcctNos               Balance AcctTypes AcctOwners
20 3456,3457,3458,8766   12000   p,p,p,b   John,John,John,Mary

I managed to generate it from four different records in the Query Builder from four separate bank accounts by using SUM() for the Balance.and the handy Concat module for the AcctNos, Account Types (Personal and Bunsiess respectively) and Account Owners.

I've acheived my primary objective, which was to tot up the total balances of all the accounts located under several (duplicated) IDs. Now the data is going out to the world at large I need people to be able to look up the details of the individual accounts. But as you can see, a string of numbers separated by commas isn't exactly the friendliest way of doing this! What I want to do now is generate a separate table that lists all the account numbers associated with this record (3456,3457,3458 ,8766), summarises their individual balances, and lets people fiddle with them. So how do I get this list of values separated by commas into a format Access can use for functions?

Thanks folks!

[ChipR]

I don't see how you can get the individual account balances out of this record. Is there a reason you can't use the data that you orginally concatenated rather than trying to parse this back out?

[DonRayner]

Here is an example on using the split function. Using recordsets, you should be able to expand/adapt this to get what you want.

Dim var As Variant
Dim str As String
str = "3456,3457,3458,8766"
For Each var In Split(str, ",")
    Trim var
    MsgBox var
Next var

[NeoPa]

It's true that you can process this data using Split() Whizzo, but be warned. This is not a very clever way to proceed (see Chip's post #2). It's a bit like taking your dog out for a walk, then chasing after all the neighbourhood cats yourself.

Access is an RDBMS. Not the biggest, nor the best at raw processing of data, but nevertheless a very competent engine for processing large amounts of data. If you try to do the job that Access is designed for you will :

1. Tire yourself out while your dog looks on confused. The more deeply you get involved the more complex your task becomes.
2. Not do anywhere near as good a job as the dog (Access) could have done.

This sort of data should be stored separately, then drawn together, when/where necessary, by Access queries etc.

I hope this makes sense. For further help understanding the issue I recommend checking out Normalisation and Table structures.

[FishVal]

... It's a bit like taking your dog out for a walk, then chasing after all the neighbourhood cats yourself...

I would say it is like taking your dog out to put back into it what it has did on neighbor's lawn.

Sorry for off topic.