Test Boxes to Fields

C

Cheryl Mckerney

How would I separate a text field in an Access 97 database, entered like
this:

A695 Item1/Item2/Item3/Item4 (say up to Item 10)
A696 Item1/Item2

to separate fields in a new table for each item? So that it would break
down like this:

A695 Item1
A695 Item2
A695 Item3
A695 Item4
A696 Item1
A696 Item2

Thanks! Any ideas would be appreciated.
 
D

Duane Hookom

Is the A695 in the same field with the items or in a different field? Can
you provide a table and field names? Are all items delimited by "/"?
Your solution is possible with a cartesian query.
 
C

Cheryl

They are in different fields, named "OrderNmbr" and "Item." And yes,
they are all delimited by the "/". I've never heard of a cartesian query!

Cheryl
 
D

Duane Hookom

Assuming tblOrderItems with fields OrderNmbr and Item as you stated. You
need a table of all different Item values: tblItems with a field Item. This
allows you to create a query with the following SQL:

SELECT tblOrderItems.OrderNmbr, tblItems.Item, tblOrderItems.Item
FROM tblItems, tblOrderItems
WHERE (((tblOrderItems.Item) Like "*" & [tblItems].[Item] & "*"))
ORDER BY tblOrderItems.OrderNmbr, tblItems.Item;

Notice there is no join between the two tables which creates a Cartesian
query. If you potentially have items like Item1 and Item10 where all
characters of "Item1" are included in "Item10" then you need to modify the
query:
SELECT tblOrderItems.OrderNmbr, tblItems.Item,
"/" & [tblOrderItems].[Item] & "/" AS Expr1
FROM tblItems, tblOrderItems
WHERE ((("/" & [tblOrderItems].[Item] & "/") Like "*/" & [tblItems].[Item] &
"/*"))
ORDER BY tblOrderItems.OrderNmbr, tblItems.Item;
 
C

Cheryl

I think I didn't explain very well. What I want to do is append the data
to a new table, but with each item as a separate record:

From TblItems
ITEMNMBR ITEM
A695 Item1/Item2/Item3/Item4
A696 Item1/Item2

To TblItemsSeparate
ITEMNMBR ITEM
A695 Item1
A695 Item2
A695 Item3
A695 Item4
A696 Item1
A697 Item2

Is this possible? Sorry for the confusion.

Cheryl
 
D

Duane Hookom

How far did you get with my suggestion? Do you have a table with a field
containing each unique Item?
I tested my sql and it creates a resultset exactly like you asked. It could
easily be converted to a make table or append query.

--
Duane Hookom
MS Access MVP


Cheryl said:
I think I didn't explain very well. What I want to do is append the data
to a new table, but with each item as a separate record:

From TblItems
ITEMNMBR ITEM
A695 Item1/Item2/Item3/Item4
A696 Item1/Item2

To TblItemsSeparate
ITEMNMBR ITEM
A695 Item1
A695 Item2
A695 Item3
A695 Item4
A696 Item1
A697 Item2

Is this possible? Sorry for the confusion.

Cheryl




Duane said:
Assuming tblOrderItems with fields OrderNmbr and Item as you stated. You
need a table of all different Item values: tblItems with a field Item. This
allows you to create a query with the following SQL:

SELECT tblOrderItems.OrderNmbr, tblItems.Item, tblOrderItems.Item
FROM tblItems, tblOrderItems
WHERE (((tblOrderItems.Item) Like "*" & [tblItems].[Item] & "*"))
ORDER BY tblOrderItems.OrderNmbr, tblItems.Item;

Notice there is no join between the two tables which creates a Cartesian
query. If you potentially have items like Item1 and Item10 where all
characters of "Item1" are included in "Item10" then you need to modify the
query:
SELECT tblOrderItems.OrderNmbr, tblItems.Item,
"/" & [tblOrderItems].[Item] & "/" AS Expr1
FROM tblItems, tblOrderItems
WHERE ((("/" & [tblOrderItems].[Item] & "/") Like "*/" & [tblItems].[Item] &
"/*"))
ORDER BY tblOrderItems.OrderNmbr, tblItems.Item;
Click to expand...
Click to expand...
 
C

Cheryl

I get nothing except blank fields. I'll keep working with it tomorrow
and get back to you.

Cheryl
 
D

Duane Hookom

Your tblItems is not my sample tblItems. If you would have provided the
table names earlier, it might have avoided some confusion. I used
"tblOrderItems" as the original table name.
 
C

Cheryl

I renamed my table to match yours and used the exact code, but it did
not work for me (no records). I revised the first query to append to
TblItems, and then got results from the second query that are a little
hopeful. One record sort of worked, from Item1/Item2/Item3/Item4 to
Item1/Item2. But I'm out of ideas where to go from there.

I don't understand what you mean by "if you potentially have items like
Item1 and Item10 and Item10 where all characters of "Item1" are included
in "Item10" then you have to modify the query. That text field could
have ten items, each separated by the slash, or maybe only one. It
varies with every order number.

Cheryl
 
C

Cheryl

I found this on The Access Web, and I think it's what I'm trying to do.
Is there a way to modify this to parse the items separated by "/" into
separate fields?

FirstName: Right$([Name],Len([Name])- InStr(1,[Name],",")-1)

LastName: Left$([Name],InStr(1,[Name],",")-1)

(this string had to do with a name format like "Doe, John" in one field.

Cheryl
 
D

Duane Hookom

What are your current tables with a few sample records. I would expect that
you have a table with each unique item.

An example:
Open the Northwind.mdb sample database and create a table with two fields
and these records:

tblOrderProducts
OrderNumber ProductIDs
A 1/2
B 5/6/7
C 1/6
D 9

Then create a query with the following sql:
SELECT tblOrderProducts.OrderNumber,
Products.ProductID
INTO tblOrderProductsNormalized
FROM Products, tblOrderProducts
WHERE ((("/" & [ProductIDs] & "/") Like "*/" & [ProductID] & "/*"))
ORDER BY tblOrderProducts.OrderNumber, Products.ProductID;

You will get a resulting table:
tblOrderProductsNormalized
OrderNumber ProductID
A 1
A 2
B 5
B 6
B 7
C 1
C 6
D 9

Duane Hookom
MS Access MVP
--
 
C

Cheryl

Woohoo! It worked in Northwind, and then I modified my table/field names
to test it out with my own data. That's what I needed to get started;
I'll tweak and modify to make it work the rest of the way. What a relief!

Thank you for your patience and persistance. It's got to be frustrating
to decipher what people are trying to accomplish when you can't work
with their data.

Cheryl

Duane said:
What are your current tables with a few sample records. I would expect that
you have a table with each unique item.

An example:
Open the Northwind.mdb sample database and create a table with two fields
and these records:

tblOrderProducts
OrderNumber ProductIDs
A 1/2
B 5/6/7
C 1/6
D 9

Then create a query with the following sql:
SELECT tblOrderProducts.OrderNumber,
Products.ProductID
INTO tblOrderProductsNormalized
FROM Products, tblOrderProducts
WHERE ((("/" & [ProductIDs] & "/") Like "*/" & [ProductID] & "/*"))
ORDER BY tblOrderProducts.OrderNumber, Products.ProductID;

You will get a resulting table:
tblOrderProductsNormalized
OrderNumber ProductID
A 1
A 2
B 5
B 6
B 7
C 1
C 6
D 9

Duane Hookom
MS Access MVP
--

I renamed my table to match yours and used the exact code, but it did not
work for me (no records). I revised the first query to append to TblItems,
and then got results from the second query that are a little hopeful. One
record sort of worked, from Item1/Item2/Item3/Item4 to Item1/Item2. But I'm
out of ideas where to go from there.

I don't understand what you mean by "if you potentially have items like
Item1 and Item10 and Item10 where all characters of "Item1" are included
in "Item10" then you have to modify the query. That text field could have
ten items, each separated by the slash, or maybe only one. It varies with
every order number.

Cheryl
Click to expand...
Click to expand...
 
D

Duane Hookom

Glad to hear you experienced success with Northwind. I trust you understand
the reason it works and can apply this to your situation.

--
Duane Hookom
MS Access MVP


Cheryl said:
Woohoo! It worked in Northwind, and then I modified my table/field names
to test it out with my own data. That's what I needed to get started;
I'll tweak and modify to make it work the rest of the way. What a relief!

Thank you for your patience and persistance. It's got to be frustrating
to decipher what people are trying to accomplish when you can't work
with their data.

Cheryl

Duane said:
What are your current tables with a few sample records. I would expect that
you have a table with each unique item.

An example:
Open the Northwind.mdb sample database and create a table with two fields
and these records:

tblOrderProducts
OrderNumber ProductIDs
A 1/2
B 5/6/7
C 1/6
D 9

Then create a query with the following sql:
SELECT tblOrderProducts.OrderNumber,
Products.ProductID
INTO tblOrderProductsNormalized
FROM Products, tblOrderProducts
WHERE ((("/" & [ProductIDs] & "/") Like "*/" & [ProductID] & "/*"))
ORDER BY tblOrderProducts.OrderNumber, Products.ProductID;

You will get a resulting table:
tblOrderProductsNormalized
OrderNumber ProductID
A 1
A 2
B 5
B 6
B 7
C 1
C 6
D 9

Duane Hookom
MS Access MVP
--

I renamed my table to match yours and used the exact code, but it did not
work for me (no records). I revised the first query to append to TblItems,
and then got results from the second query that are a little hopeful. One
record sort of worked, from Item1/Item2/Item3/Item4 to Item1/Item2. But I'm
out of ideas where to go from there.

I don't understand what you mean by "if you potentially have items like
Item1 and Item10 and Item10 where all characters of "Item1" are included
in "Item10" then you have to modify the query. That text field could have
ten items, each separated by the slash, or maybe only one. It varies with
every order number.

Cheryl

Duane Hookom wrote:

Your tblItems is not my sample tblItems. If you would have provided the
table names earlier, it might have avoided some confusion. I used
"tblOrderItems" as the original table name.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query that sum two tables 5
Need Help to Create Query 2
Need Help 1
Problem converting tet to table 5
arrange the display format 5
Trying to SUM variable number of VLOOKUPs 7
help needed with possible query issue 1
Query 1

Top