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!
4 1446 ChipR 1,287
Recognized Expert Top Contributor
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?
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 32,556
Recognized Expert Moderator MVP
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 : - Tire yourself out while your dog looks on confused. The more deeply you get involved the more complex your task becomes.
- 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 2,653
Recognized Expert Specialist @NeoPa
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: jason |
last post by:
How does one loop through the contents of a form complicated by dynamic
construction of checkboxes which are assigned a 'model' and 'listingID' to
the NAME field on the fly in this syntax:...
|
by: shank |
last post by:
I have a recordset that contains multiple records of product a user is
purchasing. For clarity, I converted the recordset fields to variables. I
need to take that entire recordset and insert it...
|
by: Epetruk |
last post by:
Hi,
I have a mySql question here on updates to multiple tables.
Here's a simple schema to clarify things:
Structure
Table A
|
by: Sami |
last post by:
Please bear with me, and if you answer this question, please do it
step by step. I am new at Access, not at all sophisticated. I am
using Office XP. This will need to be read in Access for...
|
by: Usenet |
last post by:
Hi there,
I am quite a novice to MS Access, and I would deeply appreciate help on the
following:
I have created a list box in a form. I wish for that list box to enter
multiple values from table...
|
by: aleksandar.ristovski |
last post by:
Hello all,
I have been thinking about a possible extension to C/C++ syntax.
The current syntax allows declaring a function that returns a value:
int foo();
however, if I were to return...
|
by: MP |
last post by:
Hi
trying to begin to learn database using vb6, ado/adox, mdb format, sql
(not using access...just mdb format via ado)
i need to group the values of multiple fields
- get their possible...
|
by: mctime |
last post by:
Hello,
I am attempting to split a raw data table into a new table that has
split out a specific field in the raw data and created a new record
for each split but I have come to an impasse due to...
|
by: chumlyumly |
last post by:
Hello scripters -
OS: Mac OSX
Language: PHP w/ MySQL database
I've created an insert page where a user inputs his info, which then goes to four different tables in a MySQL database. The...
|
by: klbachrodt |
last post by:
Hi all - I've been browsing this forum for a while now, looking for help, and finally decided to join and ask my question, since I'm not terribly good at writing SQL queries in Access. Here is my...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |