How to extract the data

[Pro]

Hi,

I have a problem on extracting data from the access db.

The data is entered in the table as

Item size/qty
A X, 20; XS, 100; M, 1000;
B 40, 10; 42, 120; 46; 500; 48;100

How can I extract from the query like the following

Item Size Qty
A X 20
A XS 100
A M 1000
B 40 10
B 42 120
B 46 500
B 48 100

Thank you.

 

[Pieter Wijnen]

I hope this is external data to normalize.

I would use the split function (Acc2K & higher) to put each line into an
array & then append the extracted data into a normalized table

hth

Pieter

 

[Pro]

I lookup for the help from access and cannot find document for split
function.
How to use split function?

 

[Brendan Reynolds]

It's a VBA function, so to find it in help, start your search from the VBA
editor rather than from Access. Just open the MDB in Access, and press
Ctrl+G to open the VBA editor and go to the Immediate Window. You can search
for 'split function' using the menu or the task pane, or just type the word
'split' in the Immediate Window and press F1.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.