one field with number of features to be linked to feature descript

[Agent_KGB]

I am completely stomped on this one... the only way i know of doing this is
to create 30 ceparate queries and map one feature at a time.... i am sure
there is a better way.

Here is what i have:

Table with "AccountID" and "OldFeatureList" field, features field is a text
field with features as a text string... so it looks like this:
AA1AA2AA3BB1BB2CV1...
where
AA1 - Feature 1
AA2 - Feature 2
AA3 - Feature 3
BB1 - Feature4
etc...

I also have a table with "Old Feature" and "New Feature" fields, where each
feature has it's own record and has AA1 (Old Feature) and "Cook Feature A001"
(New Feature) fields...

what i need to do is to translate old features text string into new features
text string...

i tried building a query that broke down the field into sub fields (using
left and mid function) so no i have a query with "Olf Features" field,
"Feat1" "Feat2"...etc fields where i break feature string into something more
manageble...

what i need to do now is to stranslate each of those sub fields (or whole
text string if i can) into new features...

any thoughts? I am pretty sure i am lost with this one ;0(

 

[KARL DEWEY]

I got a way to do it using Excel.
Fill col A with =Row()
Type AA1 in B1.
Starting with B2 enter =MID(C$1,((ROW()-1)*3)+1,3) and fill col B.
Place your string AA1AA2AA3BB1BB2CV1... in C1.

The results will be ---
1 AA1 AA1AA2AA3BB1BB2CV1
2 AA2
3 AA3
4 BB1
5 BB2
6 CV1

 

[John W. Vinson]

I am completely stomped on this one... the only way i know of doing this is
to create 30 ceparate queries and map one feature at a time.... i am sure
there is a better way.

Here is what i have:

Table with "AccountID" and "OldFeatureList" field, features field is a text
field with features as a text string... so it looks like this:
AA1AA2AA3BB1BB2CV1...
where
AA1 - Feature 1
AA2 - Feature 2
AA3 - Feature 3
BB1 - Feature4
etc...

Ouch!!!

Assuming (and I have no idea if this assumption is correct) that your Features
field consists of a string of an arbitrary but limited number of three-letter
codes, you could use a normalizing UNION query like:

SELECT AccountID, Left([Features], 3) AS Feature FROM yourtable
WHERE Len([Features]) >= 3
UNION ALL
SELECT AccountID, Mid([Features], 4, 3) AS Feature FROM yourtable
WHERE Len([Features]) >= 6
UNION ALL
SELECT AccountID,Mid([Features], 7, 3) AS Feature FROM yourtable
WHERE Len([Features]) >= 9
UNION ALL
....

until you have the largest instance of this misbegotten nonatomic composite
field unravelled.

If the features are of variable or unpredictable lengths... I have no idea how
you'll be able to tease them apart.

 

[Agent_KGB]

I am working with 44K records... was looking for something that would allow
me to do this without much "Manual" work ;0(

 

[Agent_KGB]

Thanks for advice John,

your assumption is correct field does consist of 3letter codes, and from
what i can see the largest of them is a 90 char count string of codes (so 30
features in one field)

I will try to play with your suggestion, not a big expert in sql (in fact i
am not an expert at all)... but i'll try to work it up.

stan by for more questions and hands out for help ;0)

I am completely stomped on this one... the only way i know of doing this is
to create 30 ceparate queries and map one feature at a time.... i am sure
there is a better way.

Here is what i have:

Table with "AccountID" and "OldFeatureList" field, features field is a text
field with features as a text string... so it looks like this:
AA1AA2AA3BB1BB2CV1...
where
AA1 - Feature 1
AA2 - Feature 2
AA3 - Feature 3
BB1 - Feature4
etc...

Ouch!!!

Assuming (and I have no idea if this assumption is correct) that your Features
field consists of a string of an arbitrary but limited number of three-letter
codes, you could use a normalizing UNION query like:

SELECT AccountID, Left([Features], 3) AS Feature FROM yourtable
WHERE Len([Features]) >= 3
UNION ALL
SELECT AccountID, Mid([Features], 4, 3) AS Feature FROM yourtable
WHERE Len([Features]) >= 6
UNION ALL
SELECT AccountID,Mid([Features], 7, 3) AS Feature FROM yourtable
WHERE Len([Features]) >= 9
UNION ALL
....

until you have the largest instance of this misbegotten nonatomic composite
field unravelled.

If the features are of variable or unpredictable lengths... I have no idea how
you'll be able to tease them apart.

 

[Agent_KGB]

am i correct to guess that this code would break up my feature list into
fields?

as in it would take this record ACCOUNT0001 (Field 1) AA1AA2AA3AA4AA5BB3FF9
(Field 2)

and break it into something like this:
record with these fields: ACCOUNT0001 (Field1) AA1 (Field2) AA2 (Field3)...
etc

or would it make a record for each feature for account
as in Record1: ACCOUNT0001 AA1
Record2: ACCOUNT0001 AA2
etc...

?

if it's the first, i already have that broken down, but i can't figure out
how to link each broken field to my FeaturesMap (Can link one of them, but
not all)

i know this is pretty complex... perhaps too compex for a forum
discussion... but i am stuck and i really don't have anywhere else to turn ;0(

I am completely stomped on this one... the only way i know of doing this is
to create 30 ceparate queries and map one feature at a time.... i am sure
there is a better way.

Here is what i have:

Table with "AccountID" and "OldFeatureList" field, features field is a text
field with features as a text string... so it looks like this:
AA1AA2AA3BB1BB2CV1...
where
AA1 - Feature 1
AA2 - Feature 2
AA3 - Feature 3
BB1 - Feature4
etc...

Ouch!!!

Assuming (and I have no idea if this assumption is correct) that your Features
field consists of a string of an arbitrary but limited number of three-letter
codes, you could use a normalizing UNION query like:

SELECT AccountID, Left([Features], 3) AS Feature FROM yourtable
WHERE Len([Features]) >= 3
UNION ALL
SELECT AccountID, Mid([Features], 4, 3) AS Feature FROM yourtable
WHERE Len([Features]) >= 6
UNION ALL
SELECT AccountID,Mid([Features], 7, 3) AS Feature FROM yourtable
WHERE Len([Features]) >= 9
UNION ALL
....

until you have the largest instance of this misbegotten nonatomic composite
field unravelled.

If the features are of variable or unpredictable lengths... I have no idea how
you'll be able to tease them apart.

 

[Agent_KGB]

PS: when i try to make SQL query (in Access) with your code, it gives me
"invalid Function" here is my exact code:

SELECT AccountID, Left([Features], 3) AS Feature FROM [Tbl001Master]
WHERE Len([Features]) >= 3
UNION ALL
SELECT AccountID, Mid([Features], 4, 3) AS Feature FROM [Tbl001Master]
WHERE Len([Features]) >= 6
UNION ALL
SELECT AccountID,Mid([Features], 7, 3) AS Feature FROM [Tbl001Master]
WHERE Len([Features]) >= 9
UNION ALL

am i missing anything in here?

I am completely stomped on this one... the only way i know of doing this is
to create 30 ceparate queries and map one feature at a time.... i am sure
there is a better way.

Here is what i have:

Table with "AccountID" and "OldFeatureList" field, features field is a text
field with features as a text string... so it looks like this:
AA1AA2AA3BB1BB2CV1...
where
AA1 - Feature 1
AA2 - Feature 2
AA3 - Feature 3
BB1 - Feature4
etc...

Ouch!!!

Assuming (and I have no idea if this assumption is correct) that your Features
field consists of a string of an arbitrary but limited number of three-letter
codes, you could use a normalizing UNION query like:

SELECT AccountID, Left([Features], 3) AS Feature FROM yourtable
WHERE Len([Features]) >= 3
UNION ALL
SELECT AccountID, Mid([Features], 4, 3) AS Feature FROM yourtable
WHERE Len([Features]) >= 6
UNION ALL
SELECT AccountID,Mid([Features], 7, 3) AS Feature FROM yourtable
WHERE Len([Features]) >= 9
UNION ALL
....

until you have the largest instance of this misbegotten nonatomic composite
field unravelled.

If the features are of variable or unpredictable lengths... I have no idea how
you'll be able to tease them apart.

 

[KARL DEWEY]

Below is where I took the Excel process and create a query to do the same
thing. First create a table named CountNumber with field CountNUM containing
numbers from 0 (zero) though your maximum.

SELECT Agent_KGB.AccountID, CountNumber.CountNUM AS Feature_Number,
Mid([OldFeatureList],(([CountNUM]-1)*3)+1,3) AS [New Feature]
FROM Agent_KGB, CountNumber
WHERE (((CountNumber.CountNUM) Between 1 And Len([OldFeatureList])/3))
ORDER BY Agent_KGB.AccountID, CountNumber.CountNUM;

 

[Agent_KGB]

you rock KARL!!!!

i'll buy you a beer next time you are in Atlanta!!!

NOT IMPORTANT: so, after running the query, i went ahead ans saved it as a
table as i ended up with over 1M records... next step should be easy (map old
features to new features)... which will take another query to make (to link
my new teature table with new features) and a new table for results (to save
new features with account ID)...

VERY IMPORTANT: so, what do i do next? How do i join all of the new features
into one field? I'll have 30 records (max number of features) for each
account, how do i shrink them back from 1M to 40K?

I just need them all in one text string (with comma to ceparate them... but
i think i can do that part on my own... just need them in a string ;0)

Below is where I took the Excel process and create a query to do the same
thing. First create a table named CountNumber with field CountNUM containing
numbers from 0 (zero) though your maximum.

SELECT Agent_KGB.AccountID, CountNumber.CountNUM AS Feature_Number,
Mid([OldFeatureList],(([CountNUM]-1)*3)+1,3) AS [New Feature]
FROM Agent_KGB, CountNumber
WHERE (((CountNumber.CountNUM) Between 1 And Len([OldFeatureList])/3))
ORDER BY Agent_KGB.AccountID, CountNumber.CountNUM;

--
Build a little, test a little.

I am working with 44K records... was looking for something that would allow
me to do this without much "Manual" work ;0(

 

[KARL DEWEY]

I just need them all in one text string (with comma to ceparate them...

WHY?

--
Build a little, test a little.

you rock KARL!!!!

i'll buy you a beer next time you are in Atlanta!!!

NOT IMPORTANT: so, after running the query, i went ahead ans saved it as a
table as i ended up with over 1M records... next step should be easy (map old
features to new features)... which will take another query to make (to link
my new teature table with new features) and a new table for results (to save
new features with account ID)...

VERY IMPORTANT: so, what do i do next? How do i join all of the new features
into one field? I'll have 30 records (max number of features) for each
account, how do i shrink them back from 1M to 40K?

I just need them all in one text string (with comma to ceparate them... but
i think i can do that part on my own... just need them in a string ;0)

Below is where I took the Excel process and create a query to do the same
thing. First create a table named CountNumber with field CountNUM containing
numbers from 0 (zero) though your maximum.

SELECT Agent_KGB.AccountID, CountNumber.CountNUM AS Feature_Number,
Mid([OldFeatureList],(([CountNUM]-1)*3)+1,3) AS [New Feature]
FROM Agent_KGB, CountNumber
WHERE (((CountNumber.CountNUM) Between 1 And Len([OldFeatureList])/3))
ORDER BY Agent_KGB.AccountID, CountNumber.CountNUM;

--
Build a little, test a little.

I am working with 44K records... was looking for something that would allow
me to do this without much "Manual" work ;0(

:

I got a way to do it using Excel.
Fill col A with =Row()
Type AA1 in B1.
Starting with B2 enter =MID(C$1,((ROW()-1)*3)+1,3) and fill col B.
Place your string AA1AA2AA3BB1BB2CV1... in C1.

The results will be ---
1 AA1 AA1AA2AA3BB1BB2CV1
2 AA2
3 AA3
4 BB1
5 BB2
6 CV1

--
Build a little, test a little.


:

I am completely stomped on this one... the only way i know of doing this is
to create 30 ceparate queries and map one feature at a time.... i am sure
there is a better way.

Here is what i have:

Table with "AccountID" and "OldFeatureList" field, features field is a text
field with features as a text string... so it looks like this:
AA1AA2AA3BB1BB2CV1...
where
AA1 - Feature 1
AA2 - Feature 2
AA3 - Feature 3
BB1 - Feature4
etc...

I also have a table with "Old Feature" and "New Feature" fields, where each
feature has it's own record and has AA1 (Old Feature) and "Cook Feature A001"
(New Feature) fields...

what i need to do is to translate old features text string into new features
text string...

i tried building a query that broke down the field into sub fields (using
left and mid function) so no i have a query with "Olf Features" field,
"Feat1" "Feat2"...etc fields where i break feature string into something more
manageble...

what i need to do now is to stranslate each of those sub fields (or whole
text string if i can) into new features...

any thoughts? I am pretty sure i am lost with this one ;0(

 

[Agent_KGB]

because i have a set format for the output report i am working on... ;0(

WHY?

--
Build a little, test a little.

you rock KARL!!!!

i'll buy you a beer next time you are in Atlanta!!!

NOT IMPORTANT: so, after running the query, i went ahead ans saved it as a
table as i ended up with over 1M records... next step should be easy (map old
features to new features)... which will take another query to make (to link
my new teature table with new features) and a new table for results (to save
new features with account ID)...

VERY IMPORTANT: so, what do i do next? How do i join all of the new features
into one field? I'll have 30 records (max number of features) for each
account, how do i shrink them back from 1M to 40K?

I just need them all in one text string (with comma to ceparate them... but
i think i can do that part on my own... just need them in a string ;0)

Below is where I took the Excel process and create a query to do the same
thing. First create a table named CountNumber with field CountNUM containing
numbers from 0 (zero) though your maximum.

SELECT Agent_KGB.AccountID, CountNumber.CountNUM AS Feature_Number,
Mid([OldFeatureList],(([CountNUM]-1)*3)+1,3) AS [New Feature]
FROM Agent_KGB, CountNumber
WHERE (((CountNumber.CountNUM) Between 1 And Len([OldFeatureList])/3))
ORDER BY Agent_KGB.AccountID, CountNumber.CountNUM;

--
Build a little, test a little.


:

I am working with 44K records... was looking for something that would allow
me to do this without much "Manual" work ;0(

:

I got a way to do it using Excel.
Fill col A with =Row()
Type AA1 in B1.
Starting with B2 enter =MID(C$1,((ROW()-1)*3)+1,3) and fill col B.
Place your string AA1AA2AA3BB1BB2CV1... in C1.

The results will be ---
1 AA1 AA1AA2AA3BB1BB2CV1
2 AA2
3 AA3
4 BB1
5 BB2
6 CV1

--
Build a little, test a little.


:

I am completely stomped on this one... the only way i know of doing this is
to create 30 ceparate queries and map one feature at a time.... i am sure
there is a better way.

Here is what i have:

Table with "AccountID" and "OldFeatureList" field, features field is a text
field with features as a text string... so it looks like this:
AA1AA2AA3BB1BB2CV1...
where
AA1 - Feature 1
AA2 - Feature 2
AA3 - Feature 3
BB1 - Feature4
etc...

I also have a table with "Old Feature" and "New Feature" fields, where each
feature has it's own record and has AA1 (Old Feature) and "Cook Feature A001"
(New Feature) fields...

what i need to do is to translate old features text string into new features
text string...

i tried building a query that broke down the field into sub fields (using
left and mid function) so no i have a query with "Olf Features" field,
"Feat1" "Feat2"...etc fields where i break feature string into something more
manageble...

what i need to do now is to stranslate each of those sub fields (or whole
text string if i can) into new features...

any thoughts? I am pretty sure i am lost with this one ;0(

 

[Agent_KGB]

one other thing... i expected for your code to create 30 records (that's how
many records i have in countnumber table, but going over the data, i see that
some records got as little as 7 features, most did get 30, but i was trying
to figure out what would cause this inconsistency?

WHY?

--
Build a little, test a little.

you rock KARL!!!!

i'll buy you a beer next time you are in Atlanta!!!

NOT IMPORTANT: so, after running the query, i went ahead ans saved it as a
table as i ended up with over 1M records... next step should be easy (map old
features to new features)... which will take another query to make (to link
my new teature table with new features) and a new table for results (to save
new features with account ID)...

VERY IMPORTANT: so, what do i do next? How do i join all of the new features
into one field? I'll have 30 records (max number of features) for each
account, how do i shrink them back from 1M to 40K?

I just need them all in one text string (with comma to ceparate them... but
i think i can do that part on my own... just need them in a string ;0)

Below is where I took the Excel process and create a query to do the same
thing. First create a table named CountNumber with field CountNUM containing
numbers from 0 (zero) though your maximum.

SELECT Agent_KGB.AccountID, CountNumber.CountNUM AS Feature_Number,
Mid([OldFeatureList],(([CountNUM]-1)*3)+1,3) AS [New Feature]
FROM Agent_KGB, CountNumber
WHERE (((CountNumber.CountNUM) Between 1 And Len([OldFeatureList])/3))
ORDER BY Agent_KGB.AccountID, CountNumber.CountNUM;

--
Build a little, test a little.


:

I am working with 44K records... was looking for something that would allow
me to do this without much "Manual" work ;0(

:

I got a way to do it using Excel.
Fill col A with =Row()
Type AA1 in B1.
Starting with B2 enter =MID(C$1,((ROW()-1)*3)+1,3) and fill col B.
Place your string AA1AA2AA3BB1BB2CV1... in C1.

The results will be ---
1 AA1 AA1AA2AA3BB1BB2CV1
2 AA2
3 AA3
4 BB1
5 BB2
6 CV1

--
Build a little, test a little.


:

I am completely stomped on this one... the only way i know of doing this is
to create 30 ceparate queries and map one feature at a time.... i am sure
there is a better way.

Here is what i have:

Table with "AccountID" and "OldFeatureList" field, features field is a text
field with features as a text string... so it looks like this:
AA1AA2AA3BB1BB2CV1...
where
AA1 - Feature 1
AA2 - Feature 2
AA3 - Feature 3
BB1 - Feature4
etc...

I also have a table with "Old Feature" and "New Feature" fields, where each
feature has it's own record and has AA1 (Old Feature) and "Cook Feature A001"
(New Feature) fields...

what i need to do is to translate old features text string into new features
text string...

i tried building a query that broke down the field into sub fields (using
left and mid function) so no i have a query with "Olf Features" field,
"Feat1" "Feat2"...etc fields where i break feature string into something more
manageble...

what i need to do now is to stranslate each of those sub fields (or whole
text string if i can) into new features...

any thoughts? I am pretty sure i am lost with this one ;0(

 

[KARL DEWEY]

i was trying to figure out what would cause this inconsistency?
It should produce as many records as there are sets in the string.

Did you check the results against the strings?

--
Build a little, test a little.

one other thing... i expected for your code to create 30 records (that's how
many records i have in countnumber table, but going over the data, i see that
some records got as little as 7 features, most did get 30, but i was trying
to figure out what would cause this inconsistency?

I just need them all in one text string (with comma to ceparate them...

WHY?

--
Build a little, test a little.

you rock KARL!!!!

i'll buy you a beer next time you are in Atlanta!!!

NOT IMPORTANT: so, after running the query, i went ahead ans saved it as a
table as i ended up with over 1M records... next step should be easy (map old
features to new features)... which will take another query to make (to link
my new teature table with new features) and a new table for results (to save
new features with account ID)...

VERY IMPORTANT: so, what do i do next? How do i join all of the new features
into one field? I'll have 30 records (max number of features) for each
account, how do i shrink them back from 1M to 40K?

I just need them all in one text string (with comma to ceparate them... but
i think i can do that part on my own... just need them in a string ;0)


:

Below is where I took the Excel process and create a query to do the same
thing. First create a table named CountNumber with field CountNUM containing
numbers from 0 (zero) though your maximum.

SELECT Agent_KGB.AccountID, CountNumber.CountNUM AS Feature_Number,
Mid([OldFeatureList],(([CountNUM]-1)*3)+1,3) AS [New Feature]
FROM Agent_KGB, CountNumber
WHERE (((CountNumber.CountNUM) Between 1 And Len([OldFeatureList])/3))
ORDER BY Agent_KGB.AccountID, CountNumber.CountNUM;

--
Build a little, test a little.


:

I am working with 44K records... was looking for something that would allow
me to do this without much "Manual" work ;0(

:

I got a way to do it using Excel.
Fill col A with =Row()
Type AA1 in B1.
Starting with B2 enter =MID(C$1,((ROW()-1)*3)+1,3) and fill col B.
Place your string AA1AA2AA3BB1BB2CV1... in C1.

The results will be ---
1 AA1 AA1AA2AA3BB1BB2CV1
2 AA2
3 AA3
4 BB1
5 BB2
6 CV1

--
Build a little, test a little.


:

I am completely stomped on this one... the only way i know of doing this is
to create 30 ceparate queries and map one feature at a time.... i am sure
there is a better way.

Here is what i have:

Table with "AccountID" and "OldFeatureList" field, features field is a text
field with features as a text string... so it looks like this:
AA1AA2AA3BB1BB2CV1...
where
AA1 - Feature 1
AA2 - Feature 2
AA3 - Feature 3
BB1 - Feature4
etc...

I also have a table with "Old Feature" and "New Feature" fields, where each
feature has it's own record and has AA1 (Old Feature) and "Cook Feature A001"
(New Feature) fields...

what i need to do is to translate old features text string into new features
text string...

i tried building a query that broke down the field into sub fields (using
left and mid function) so no i have a query with "Olf Features" field,
"Feat1" "Feat2"...etc fields where i break feature string into something more
manageble...

what i need to do now is to stranslate each of those sub fields (or whole
text string if i can) into new features...

any thoughts? I am pretty sure i am lost with this one ;0(

 

[Agent_KGB]

just did, and that was the problem... some records were too short for all 30
fields... which is totaly cool!!!

thanks Karl, you rock!

It should produce as many records as there are sets in the string.

Did you check the results against the strings?

--
Build a little, test a little.

one other thing... i expected for your code to create 30 records (that's how
many records i have in countnumber table, but going over the data, i see that
some records got as little as 7 features, most did get 30, but i was trying
to figure out what would cause this inconsistency?

I just need them all in one text string (with comma to ceparate them...

WHY?

--
Build a little, test a little.


:

you rock KARL!!!!

i'll buy you a beer next time you are in Atlanta!!!

NOT IMPORTANT: so, after running the query, i went ahead ans saved it as a
table as i ended up with over 1M records... next step should be easy (map old
features to new features)... which will take another query to make (to link
my new teature table with new features) and a new table for results (to save
new features with account ID)...

VERY IMPORTANT: so, what do i do next? How do i join all of the new features
into one field? I'll have 30 records (max number of features) for each
account, how do i shrink them back from 1M to 40K?

I just need them all in one text string (with comma to ceparate them... but
i think i can do that part on my own... just need them in a string ;0)


:

Below is where I took the Excel process and create a query to do the same
thing. First create a table named CountNumber with field CountNUM containing
numbers from 0 (zero) though your maximum.

SELECT Agent_KGB.AccountID, CountNumber.CountNUM AS Feature_Number,
Mid([OldFeatureList],(([CountNUM]-1)*3)+1,3) AS [New Feature]
FROM Agent_KGB, CountNumber
WHERE (((CountNumber.CountNUM) Between 1 And Len([OldFeatureList])/3))
ORDER BY Agent_KGB.AccountID, CountNumber.CountNUM;

--
Build a little, test a little.


:

I am working with 44K records... was looking for something that would allow
me to do this without much "Manual" work ;0(

:

I got a way to do it using Excel.
Fill col A with =Row()
Type AA1 in B1.
Starting with B2 enter =MID(C$1,((ROW()-1)*3)+1,3) and fill col B.
Place your string AA1AA2AA3BB1BB2CV1... in C1.

The results will be ---
1 AA1 AA1AA2AA3BB1BB2CV1
2 AA2
3 AA3
4 BB1
5 BB2
6 CV1

--
Build a little, test a little.


:

I am completely stomped on this one... the only way i know of doing this is
to create 30 ceparate queries and map one feature at a time.... i am sure
there is a better way.

Here is what i have:

Table with "AccountID" and "OldFeatureList" field, features field is a text
field with features as a text string... so it looks like this:
AA1AA2AA3BB1BB2CV1...
where
AA1 - Feature 1
AA2 - Feature 2
AA3 - Feature 3
BB1 - Feature4
etc...

I also have a table with "Old Feature" and "New Feature" fields, where each
feature has it's own record and has AA1 (Old Feature) and "Cook Feature A001"
(New Feature) fields...

what i need to do is to translate old features text string into new features
text string...

i tried building a query that broke down the field into sub fields (using
left and mid function) so no i have a query with "Olf Features" field,
"Feat1" "Feat2"...etc fields where i break feature string into something more
manageble...

what i need to do now is to stranslate each of those sub fields (or whole
text string if i can) into new features...

any thoughts? I am pretty sure i am lost with this one ;0(