one row of codes to multiple rows

[Guest]

since I have been amazed with the amount of knowledge and help on here I'll
throw out another problem, which as of now we are solving manually.

Old Member table has a field "Skills" with codes CO, BE, AR etc. and the
column can hold mulitple codes separated by a comma. New format has a child
Skills table, and the skill column has a lookup to Skill Type table; so 1 =
BE, 2 = AR, etc. (However, the codes are now expanded to words, the above is
just to show the reln from number to code).

Is there a query to read the skills row, parse it, and insert a child row
for the corresponding skill type? The format of the old skill row is two
letters "," two letters, etc. eg BE,AR,PS etc.

tia

 

[Duane Hookom]

Assuming a table of members, tblMembers, with fields MemberID and Skills.
Also a skill type table, tblSkillTypes, with fields SkillID and SkillTitle.
You can append records (MemberID and SkillID) to your new table based on a
query like:

SELECT tblMembers.MemberID, tblSkillTypes.SkillId
FROM tblMembers, tblSkillTypes
WHERE (((InStr([Skills],[SkillTitle]))>0));

 

[Guest]

it's not that simple. Old table: Member, has column Skills. Skills were
entered like this: BE;CA;AR etc New Model has a Skill table as a child of
Member, and the skill member has Skill Type which is a lookup to Skill_Type
table.

I need to take the skills eg BE;CA;AR and turn that into three rows in the
new Skill table, along with translating the BE to it's code (from Skill
Type), CA, to its code, etc. The other thing is, each old Skill row has N
skills typed in, some one (BE), some 5 (BE:CA:RE:TG:FD), etc. There are 25
possible skills.

Is that clearer?

Thanks for your help.

HB

Assuming a table of members, tblMembers, with fields MemberID and Skills.
Also a skill type table, tblSkillTypes, with fields SkillID and SkillTitle.
You can append records (MemberID and SkillID) to your new table based on a
query like:

SELECT tblMembers.MemberID, tblSkillTypes.SkillId
FROM tblMembers, tblSkillTypes
WHERE (((InStr([Skills],[SkillTitle]))>0));


--
Duane Hookom
MS Access MVP

since I have been amazed with the amount of knowledge and help on here
I'll
throw out another problem, which as of now we are solving manually.

Old Member table has a field "Skills" with codes CO, BE, AR etc. and the
column can hold mulitple codes separated by a comma. New format has a
child
Skills table, and the skill column has a lookup to Skill Type table; so 1
=
BE, 2 = AR, etc. (However, the codes are now expanded to words, the above
is
just to show the reln from number to code).

Is there a query to read the skills row, parse it, and insert a child row
for the corresponding skill type? The format of the old skill row is two
letters "," two letters, etc. eg BE,AR,PS etc.

tia

 

[Duane Hookom]

You are ruffling my feathers (if I actually had feathers .

I took the time to create two tables in a sample database (Northwinds) with
the exact structure as you describe and I mentioned in my posting. The table
and field names were probably not the same since you didn't take the time to
provide them to us.

I then typed several records into each table. The values in the Skills field
in my members table contained multiple skills like "BE, CA, AR,..". My skill
type table contained one row for each skill type just as you described.

I then created a query with the SQL I provided in my posting and tested it
to see if a record from the members table with a Skill field like "BE, CA,
AR" created a recordset with three records containing the member ID and the
SkillID.

Apparently you got lost along the way or didn't try my suggestion. If my
solution didn't work, at least do me the courtesy of describing the results
of my query and why it wasn't appropriate. I believe the query is that
simple.

--
Duane Hookom
MS Access MVP

it's not that simple. Old table: Member, has column Skills. Skills were
entered like this: BE;CA;AR etc New Model has a Skill table as a child
of
Member, and the skill member has Skill Type which is a lookup to
Skill_Type
table.

I need to take the skills eg BE;CA;AR and turn that into three rows in the
new Skill table, along with translating the BE to it's code (from Skill
Type), CA, to its code, etc. The other thing is, each old Skill row has N
skills typed in, some one (BE), some 5 (BE:CA:RE:TG:FD), etc. There are 25
possible skills.

Is that clearer?

Thanks for your help.

HB

Assuming a table of members, tblMembers, with fields MemberID and Skills.
Also a skill type table, tblSkillTypes, with fields SkillID and
SkillTitle.
You can append records (MemberID and SkillID) to your new table based on
a
query like:

SELECT tblMembers.MemberID, tblSkillTypes.SkillId
FROM tblMembers, tblSkillTypes
WHERE (((InStr([Skills],[SkillTitle]))>0));


--
Duane Hookom
MS Access MVP

since I have been amazed with the amount of knowledge and help on here
I'll
throw out another problem, which as of now we are solving manually.

Old Member table has a field "Skills" with codes CO, BE, AR etc. and
the
column can hold mulitple codes separated by a comma. New format has a
child
Skills table, and the skill column has a lookup to Skill Type table; so
1
=
BE, 2 = AR, etc. (However, the codes are now expanded to words, the
above
is
just to show the reln from number to code).

Is there a query to read the skills row, parse it, and insert a child
row
for the corresponding skill type? The format of the old skill row is
two
letters "," two letters, etc. eg BE,AR,PS etc.

tia

 

[Guest]

Sorry Duane. I did read your reply, but i wasn't sure you had exactly the
same setup (though I guess you do), and I couldn't belive the query was that
simple.

However, there is one part missing. Your query may get the codes into the
new skills table, but I also need them converted to numbers (like a switch()).

But I'll certainly try the query in my test database.

And don't think your help is overlooked or under appreciated. It is
sincerely appreicated.

Thanks again.

HB

You are ruffling my feathers (if I actually had feathers .

I took the time to create two tables in a sample database (Northwinds) with
the exact structure as you describe and I mentioned in my posting. The table
and field names were probably not the same since you didn't take the time to
provide them to us.

I then typed several records into each table. The values in the Skills field
in my members table contained multiple skills like "BE, CA, AR,..". My skill
type table contained one row for each skill type just as you described.

I then created a query with the SQL I provided in my posting and tested it
to see if a record from the members table with a Skill field like "BE, CA,
AR" created a recordset with three records containing the member ID and the
SkillID.

Apparently you got lost along the way or didn't try my suggestion. If my
solution didn't work, at least do me the courtesy of describing the results
of my query and why it wasn't appropriate. I believe the query is that
simple.

--
Duane Hookom
MS Access MVP

it's not that simple. Old table: Member, has column Skills. Skills were
entered like this: BE;CA;AR etc New Model has a Skill table as a child
of
Member, and the skill member has Skill Type which is a lookup to
Skill_Type
table.

I need to take the skills eg BE;CA;AR and turn that into three rows in the
new Skill table, along with translating the BE to it's code (from Skill
Type), CA, to its code, etc. The other thing is, each old Skill row has N
skills typed in, some one (BE), some 5 (BE:CA:RE:TG:FD), etc. There are 25
possible skills.

Is that clearer?

Thanks for your help.

HB

Assuming a table of members, tblMembers, with fields MemberID and Skills.
Also a skill type table, tblSkillTypes, with fields SkillID and
SkillTitle.
You can append records (MemberID and SkillID) to your new table based on
a
query like:

SELECT tblMembers.MemberID, tblSkillTypes.SkillId
FROM tblMembers, tblSkillTypes
WHERE (((InStr([Skills],[SkillTitle]))>0));


--
Duane Hookom
MS Access MVP


since I have been amazed with the amount of knowledge and help on here
I'll
throw out another problem, which as of now we are solving manually.

Old Member table has a field "Skills" with codes CO, BE, AR etc. and
the
column can hold mulitple codes separated by a comma. New format has a
child
Skills table, and the skill column has a lookup to Skill Type table; so
1
=
BE, 2 = AR, etc. (However, the codes are now expanded to words, the
above
is
just to show the reln from number to code).

Is there a query to read the skills row, parse it, and insert a child
row
for the corresponding skill type? The format of the old skill row is
two
letters "," two letters, etc. eg BE,AR,PS etc.

tia

 

[Guest]

Here;s my SQL (based on yours) and I get prompted for a "Old skill One"

INSERT INTO MEMBER_SKILL ( MemberID, MemberSkill1 )
SELECT Member.MemberID AS Expr1, InStr([OldSkill1],[MemberSkill1])
FROM Member, Member_Skill
WHERE (((InStr([OldSkill1],[MemberSkill1]))>0));

You are ruffling my feathers (if I actually had feathers .

I took the time to create two tables in a sample database (Northwinds) with
the exact structure as you describe and I mentioned in my posting. The table
and field names were probably not the same since you didn't take the time to
provide them to us.

I then typed several records into each table. The values in the Skills field
in my members table contained multiple skills like "BE, CA, AR,..". My skill
type table contained one row for each skill type just as you described.

I then created a query with the SQL I provided in my posting and tested it
to see if a record from the members table with a Skill field like "BE, CA,
AR" created a recordset with three records containing the member ID and the
SkillID.

Apparently you got lost along the way or didn't try my suggestion. If my
solution didn't work, at least do me the courtesy of describing the results
of my query and why it wasn't appropriate. I believe the query is that
simple.

--
Duane Hookom
MS Access MVP

it's not that simple. Old table: Member, has column Skills. Skills were
entered like this: BE;CA;AR etc New Model has a Skill table as a child
of
Member, and the skill member has Skill Type which is a lookup to
Skill_Type
table.

I need to take the skills eg BE;CA;AR and turn that into three rows in the
new Skill table, along with translating the BE to it's code (from Skill
Type), CA, to its code, etc. The other thing is, each old Skill row has N
skills typed in, some one (BE), some 5 (BE:CA:RE:TG:FD), etc. There are 25
possible skills.

Is that clearer?

Thanks for your help.

HB

Assuming a table of members, tblMembers, with fields MemberID and Skills.
Also a skill type table, tblSkillTypes, with fields SkillID and
SkillTitle.
You can append records (MemberID and SkillID) to your new table based on
a
query like:

SELECT tblMembers.MemberID, tblSkillTypes.SkillId
FROM tblMembers, tblSkillTypes
WHERE (((InStr([Skills],[SkillTitle]))>0));


--
Duane Hookom
MS Access MVP


since I have been amazed with the amount of knowledge and help on here
I'll
throw out another problem, which as of now we are solving manually.

Old Member table has a field "Skills" with codes CO, BE, AR etc. and
the
column can hold mulitple codes separated by a comma. New format has a
child
Skills table, and the skill column has a lookup to Skill Type table; so
1
=
BE, 2 = AR, etc. (However, the codes are now expanded to words, the
above
is
just to show the reln from number to code).

Is there a query to read the skills row, parse it, and insert a child
row
for the corresponding skill type? The format of the old skill row is
two
letters "," two letters, etc. eg BE,AR,PS etc.

tia

 

[Guest]

I think this is the correct corresponding sql from what you gave me but I
still get a prompt
SELECT Member.MemberID, Member_Skill_Type.Member_Skill_Type_Id
FROM Member, Member_Skill_Type
WHERE (((InStr([OldSkill1],[Member_Skill_Type_Id]))>0));

You are ruffling my feathers (if I actually had feathers .

I took the time to create two tables in a sample database (Northwinds) with
the exact structure as you describe and I mentioned in my posting. The table
and field names were probably not the same since you didn't take the time to
provide them to us.

I then typed several records into each table. The values in the Skills field
in my members table contained multiple skills like "BE, CA, AR,..". My skill
type table contained one row for each skill type just as you described.

I then created a query with the SQL I provided in my posting and tested it
to see if a record from the members table with a Skill field like "BE, CA,
AR" created a recordset with three records containing the member ID and the
SkillID.

Apparently you got lost along the way or didn't try my suggestion. If my
solution didn't work, at least do me the courtesy of describing the results
of my query and why it wasn't appropriate. I believe the query is that
simple.

--
Duane Hookom
MS Access MVP

it's not that simple. Old table: Member, has column Skills. Skills were
entered like this: BE;CA;AR etc New Model has a Skill table as a child
of
Member, and the skill member has Skill Type which is a lookup to
Skill_Type
table.

I need to take the skills eg BE;CA;AR and turn that into three rows in the
new Skill table, along with translating the BE to it's code (from Skill
Type), CA, to its code, etc. The other thing is, each old Skill row has N
skills typed in, some one (BE), some 5 (BE:CA:RE:TG:FD), etc. There are 25
possible skills.

Is that clearer?

Thanks for your help.

HB

Assuming a table of members, tblMembers, with fields MemberID and Skills.
Also a skill type table, tblSkillTypes, with fields SkillID and
SkillTitle.
You can append records (MemberID and SkillID) to your new table based on
a
query like:

SELECT tblMembers.MemberID, tblSkillTypes.SkillId
FROM tblMembers, tblSkillTypes
WHERE (((InStr([Skills],[SkillTitle]))>0));


--
Duane Hookom
MS Access MVP


since I have been amazed with the amount of knowledge and help on here
I'll
throw out another problem, which as of now we are solving manually.

Old Member table has a field "Skills" with codes CO, BE, AR etc. and
the
column can hold mulitple codes separated by a comma. New format has a
child
Skills table, and the skill column has a lookup to Skill Type table; so
1
=
BE, 2 = AR, etc. (However, the codes are now expanded to words, the
above
is
just to show the reln from number to code).

Is there a query to read the skills row, parse it, and insert a child
row
for the corresponding skill type? The format of the old skill row is
two
letters "," two letters, etc. eg BE,AR,PS etc.

tia

 

[Guest]

found reason for prompt, fixed that, query runs but no data returned. But at
least I have some idea on what to work with.

Thanks again.

H.

You are ruffling my feathers (if I actually had feathers .

I took the time to create two tables in a sample database (Northwinds) with
the exact structure as you describe and I mentioned in my posting. The table
and field names were probably not the same since you didn't take the time to
provide them to us.

I then typed several records into each table. The values in the Skills field
in my members table contained multiple skills like "BE, CA, AR,..". My skill
type table contained one row for each skill type just as you described.

I then created a query with the SQL I provided in my posting and tested it
to see if a record from the members table with a Skill field like "BE, CA,
AR" created a recordset with three records containing the member ID and the
SkillID.

Apparently you got lost along the way or didn't try my suggestion. If my
solution didn't work, at least do me the courtesy of describing the results
of my query and why it wasn't appropriate. I believe the query is that
simple.

--
Duane Hookom
MS Access MVP

it's not that simple. Old table: Member, has column Skills. Skills were
entered like this: BE;CA;AR etc New Model has a Skill table as a child
of
Member, and the skill member has Skill Type which is a lookup to
Skill_Type
table.

I need to take the skills eg BE;CA;AR and turn that into three rows in the
new Skill table, along with translating the BE to it's code (from Skill
Type), CA, to its code, etc. The other thing is, each old Skill row has N
skills typed in, some one (BE), some 5 (BE:CA:RE:TG:FD), etc. There are 25
possible skills.

Is that clearer?

Thanks for your help.

HB

Assuming a table of members, tblMembers, with fields MemberID and Skills.
Also a skill type table, tblSkillTypes, with fields SkillID and
SkillTitle.
You can append records (MemberID and SkillID) to your new table based on
a
query like:

SELECT tblMembers.MemberID, tblSkillTypes.SkillId
FROM tblMembers, tblSkillTypes
WHERE (((InStr([Skills],[SkillTitle]))>0));


--
Duane Hookom
MS Access MVP


since I have been amazed with the amount of knowledge and help on here
I'll
throw out another problem, which as of now we are solving manually.

Old Member table has a field "Skills" with codes CO, BE, AR etc. and
the
column can hold mulitple codes separated by a comma. New format has a
child
Skills table, and the skill column has a lookup to Skill Type table; so
1
=
BE, 2 = AR, etc. (However, the codes are now expanded to words, the
above
is
just to show the reln from number to code).

Is there a query to read the skills row, parse it, and insert a child
row
for the corresponding skill type? The format of the old skill row is
two
letters "," two letters, etc. eg BE,AR,PS etc.

tia

 

[Duane Hookom]

I assume OldSkill1 is from the Member table. Your SQL suggests that
Member_Skill_Type_ID is the field containing a single value like "CA".
However I expect Member_SKill_Type_ID is probably your numeric field but I
can't see your data.

It would certainly help if you took the time to type in your significant
table and field names.

--
Duane Hookom
MS Access MVP

I think this is the correct corresponding sql from what you gave me but I
still get a prompt
SELECT Member.MemberID, Member_Skill_Type.Member_Skill_Type_Id
FROM Member, Member_Skill_Type
WHERE (((InStr([OldSkill1],[Member_Skill_Type_Id]))>0));

You are ruffling my feathers (if I actually had feathers .

I took the time to create two tables in a sample database (Northwinds)
with
the exact structure as you describe and I mentioned in my posting. The
table
and field names were probably not the same since you didn't take the time
to
provide them to us.

I then typed several records into each table. The values in the Skills
field
in my members table contained multiple skills like "BE, CA, AR,..". My
skill
type table contained one row for each skill type just as you described.

I then created a query with the SQL I provided in my posting and tested
it
to see if a record from the members table with a Skill field like "BE,
CA,
AR" created a recordset with three records containing the member ID and
the
SkillID.

Apparently you got lost along the way or didn't try my suggestion. If my
solution didn't work, at least do me the courtesy of describing the
results
of my query and why it wasn't appropriate. I believe the query is that
simple.

--
Duane Hookom
MS Access MVP

it's not that simple. Old table: Member, has column Skills. Skills
were
entered like this: BE;CA;AR etc New Model has a Skill table as a
child
of
Member, and the skill member has Skill Type which is a lookup to
Skill_Type
table.

I need to take the skills eg BE;CA;AR and turn that into three rows in
the
new Skill table, along with translating the BE to it's code (from Skill
Type), CA, to its code, etc. The other thing is, each old Skill row has
N
skills typed in, some one (BE), some 5 (BE:CA:RE:TG:FD), etc. There are
25
possible skills.

Is that clearer?

Thanks for your help.

HB

:

Assuming a table of members, tblMembers, with fields MemberID and
Skills.
Also a skill type table, tblSkillTypes, with fields SkillID and
SkillTitle.
You can append records (MemberID and SkillID) to your new table based
on
a
query like:

SELECT tblMembers.MemberID, tblSkillTypes.SkillId
FROM tblMembers, tblSkillTypes
WHERE (((InStr([Skills],[SkillTitle]))>0));


--
Duane Hookom
MS Access MVP


since I have been amazed with the amount of knowledge and help on
here
I'll
throw out another problem, which as of now we are solving manually.

Old Member table has a field "Skills" with codes CO, BE, AR etc.
and
the
column can hold mulitple codes separated by a comma. New format has
a
child
Skills table, and the skill column has a lookup to Skill Type table;
so
1
=
BE, 2 = AR, etc. (However, the codes are now expanded to words, the
above
is
just to show the reln from number to code).

Is there a query to read the skills row, parse it, and insert a
child
row
for the corresponding skill type? The format of the old skill row is
two
letters "," two letters, etc. eg BE,AR,PS etc.

tia

 

[Guest]

Ok: Table1 (parent) Member fields MemberID, OldSkills1 Table2 (child)
Member_Skill Fields Member ID, MemberSkill Table 3 (lookup)
Member_Skill_Type Fields Member_Skill_Type_ID, Member_Skill_Descn

I assume OldSkill1 is from the Member table. Your SQL suggests that
Member_Skill_Type_ID is the field containing a single value like "CA".
However I expect Member_SKill_Type_ID is probably your numeric field but I
can't see your data.

It would certainly help if you took the time to type in your significant
table and field names.

--
Duane Hookom
MS Access MVP

I think this is the correct corresponding sql from what you gave me but I
still get a prompt
SELECT Member.MemberID, Member_Skill_Type.Member_Skill_Type_Id
FROM Member, Member_Skill_Type
WHERE (((InStr([OldSkill1],[Member_Skill_Type_Id]))>0));

You are ruffling my feathers (if I actually had feathers .

I took the time to create two tables in a sample database (Northwinds)
with
the exact structure as you describe and I mentioned in my posting. The
table
and field names were probably not the same since you didn't take the time
to
provide them to us.

I then typed several records into each table. The values in the Skills
field
in my members table contained multiple skills like "BE, CA, AR,..". My
skill
type table contained one row for each skill type just as you described.

I then created a query with the SQL I provided in my posting and tested
it
to see if a record from the members table with a Skill field like "BE,
CA,
AR" created a recordset with three records containing the member ID and
the
SkillID.

Apparently you got lost along the way or didn't try my suggestion. If my
solution didn't work, at least do me the courtesy of describing the
results
of my query and why it wasn't appropriate. I believe the query is that
simple.

--
Duane Hookom
MS Access MVP


it's not that simple. Old table: Member, has column Skills. Skills
were
entered like this: BE;CA;AR etc New Model has a Skill table as a
child
of
Member, and the skill member has Skill Type which is a lookup to
Skill_Type
table.

I need to take the skills eg BE;CA;AR and turn that into three rows in
the
new Skill table, along with translating the BE to it's code (from Skill
Type), CA, to its code, etc. The other thing is, each old Skill row has
N
skills typed in, some one (BE), some 5 (BE:CA:RE:TG:FD), etc. There are
25
possible skills.

Is that clearer?

Thanks for your help.

HB

:

Assuming a table of members, tblMembers, with fields MemberID and
Skills.
Also a skill type table, tblSkillTypes, with fields SkillID and
SkillTitle.
You can append records (MemberID and SkillID) to your new table based
on
a
query like:

SELECT tblMembers.MemberID, tblSkillTypes.SkillId
FROM tblMembers, tblSkillTypes
WHERE (((InStr([Skills],[SkillTitle]))>0));


--
Duane Hookom
MS Access MVP


since I have been amazed with the amount of knowledge and help on
here
I'll
throw out another problem, which as of now we are solving manually.

Old Member table has a field "Skills" with codes CO, BE, AR etc.
and
the
column can hold mulitple codes separated by a comma. New format has
a
child
Skills table, and the skill column has a lookup to Skill Type table;
so
1
=
BE, 2 = AR, etc. (However, the codes are now expanded to words, the
above
is
just to show the reln from number to code).

Is there a query to read the skills row, parse it, and insert a
child
row
for the corresponding skill type? The format of the old skill row is
two
letters "," two letters, etc. eg BE,AR,PS etc.

tia

 

[Guest]

also to clarify, the Skill_Type table does not have BE, CA, etc as values. it
has words (business expoerience, cashier experience, etc.) Im guessing this
is why I get 0 rows returned. If I understand the InStr correctly, it's
looking for BE and CA in the
Skill_Type lookup table (which has two columns, ID (auto number) and
Description

I assume OldSkill1 is from the Member table. Your SQL suggests that
Member_Skill_Type_ID is the field containing a single value like "CA".
However I expect Member_SKill_Type_ID is probably your numeric field but I
can't see your data.

It would certainly help if you took the time to type in your significant
table and field names.

--
Duane Hookom
MS Access MVP

I think this is the correct corresponding sql from what you gave me but I
still get a prompt
SELECT Member.MemberID, Member_Skill_Type.Member_Skill_Type_Id
FROM Member, Member_Skill_Type
WHERE (((InStr([OldSkill1],[Member_Skill_Type_Id]))>0));

You are ruffling my feathers (if I actually had feathers .

I took the time to create two tables in a sample database (Northwinds)
with
the exact structure as you describe and I mentioned in my posting. The
table
and field names were probably not the same since you didn't take the time
to
provide them to us.

I then typed several records into each table. The values in the Skills
field
in my members table contained multiple skills like "BE, CA, AR,..". My
skill
type table contained one row for each skill type just as you described.

I then created a query with the SQL I provided in my posting and tested
it
to see if a record from the members table with a Skill field like "BE,
CA,
AR" created a recordset with three records containing the member ID and
the
SkillID.

Apparently you got lost along the way or didn't try my suggestion. If my
solution didn't work, at least do me the courtesy of describing the
results
of my query and why it wasn't appropriate. I believe the query is that
simple.

--
Duane Hookom
MS Access MVP


it's not that simple. Old table: Member, has column Skills. Skills
were
entered like this: BE;CA;AR etc New Model has a Skill table as a
child
of
Member, and the skill member has Skill Type which is a lookup to
Skill_Type
table.

I need to take the skills eg BE;CA;AR and turn that into three rows in
the
new Skill table, along with translating the BE to it's code (from Skill
Type), CA, to its code, etc. The other thing is, each old Skill row has
N
skills typed in, some one (BE), some 5 (BE:CA:RE:TG:FD), etc. There are
25
possible skills.

Is that clearer?

Thanks for your help.

HB

:

Assuming a table of members, tblMembers, with fields MemberID and
Skills.
Also a skill type table, tblSkillTypes, with fields SkillID and
SkillTitle.
You can append records (MemberID and SkillID) to your new table based
on
a
query like:

SELECT tblMembers.MemberID, tblSkillTypes.SkillId
FROM tblMembers, tblSkillTypes
WHERE (((InStr([Skills],[SkillTitle]))>0));


--
Duane Hookom
MS Access MVP


since I have been amazed with the amount of knowledge and help on
here
I'll
throw out another problem, which as of now we are solving manually.

Old Member table has a field "Skills" with codes CO, BE, AR etc.
and
the
column can hold mulitple codes separated by a comma. New format has
a
child
Skills table, and the skill column has a lookup to Skill Type table;
so
1
=
BE, 2 = AR, etc. (However, the codes are now expanded to words, the
above
is
just to show the reln from number to code).

Is there a query to read the skills row, parse it, and insert a
child
row
for the corresponding skill type? The format of the old skill row is
two
letters "," two letters, etc. eg BE,AR,PS etc.

tia

 

[Guest]

I Got it! I added a new column to the Member Skill Type table, the code BE,
AR, etc. and now it works correctly! Since the In Str can read that code,
and add the "correct" Skill Code I can't belive it! Thanks so much!

Adding that thrid column was the key to the solution.

Thanks again!

also to clarify, the Skill_Type table does not have BE, CA, etc as values. it
has words (business expoerience, cashier experience, etc.) Im guessing this
is why I get 0 rows returned. If I understand the InStr correctly, it's
looking for BE and CA in the
Skill_Type lookup table (which has two columns, ID (auto number) and
Description

I assume OldSkill1 is from the Member table. Your SQL suggests that
Member_Skill_Type_ID is the field containing a single value like "CA".
However I expect Member_SKill_Type_ID is probably your numeric field but I
can't see your data.

It would certainly help if you took the time to type in your significant
table and field names.

--
Duane Hookom
MS Access MVP

I think this is the correct corresponding sql from what you gave me but I
still get a prompt
SELECT Member.MemberID, Member_Skill_Type.Member_Skill_Type_Id
FROM Member, Member_Skill_Type
WHERE (((InStr([OldSkill1],[Member_Skill_Type_Id]))>0));

:

You are ruffling my feathers (if I actually had feathers .

I took the time to create two tables in a sample database (Northwinds)
with
the exact structure as you describe and I mentioned in my posting. The
table
and field names were probably not the same since you didn't take the time
to
provide them to us.

I then typed several records into each table. The values in the Skills
field
in my members table contained multiple skills like "BE, CA, AR,..". My
skill
type table contained one row for each skill type just as you described.

I then created a query with the SQL I provided in my posting and tested
it
to see if a record from the members table with a Skill field like "BE,
CA,
AR" created a recordset with three records containing the member ID and
the
SkillID.

Apparently you got lost along the way or didn't try my suggestion. If my
solution didn't work, at least do me the courtesy of describing the
results
of my query and why it wasn't appropriate. I believe the query is that
simple.

--
Duane Hookom
MS Access MVP


it's not that simple. Old table: Member, has column Skills. Skills
were
entered like this: BE;CA;AR etc New Model has a Skill table as a
child
of
Member, and the skill member has Skill Type which is a lookup to
Skill_Type
table.

I need to take the skills eg BE;CA;AR and turn that into three rows in
the
new Skill table, along with translating the BE to it's code (from Skill
Type), CA, to its code, etc. The other thing is, each old Skill row has
N
skills typed in, some one (BE), some 5 (BE:CA:RE:TG:FD), etc. There are
25
possible skills.

Is that clearer?

Thanks for your help.

HB

:

Assuming a table of members, tblMembers, with fields MemberID and
Skills.
Also a skill type table, tblSkillTypes, with fields SkillID and
SkillTitle.
You can append records (MemberID and SkillID) to your new table based
on
a
query like:

SELECT tblMembers.MemberID, tblSkillTypes.SkillId
FROM tblMembers, tblSkillTypes
WHERE (((InStr([Skills],[SkillTitle]))>0));


--
Duane Hookom
MS Access MVP


since I have been amazed with the amount of knowledge and help on
here
I'll
throw out another problem, which as of now we are solving manually.

Old Member table has a field "Skills" with codes CO, BE, AR etc.
and
the
column can hold mulitple codes separated by a comma. New format has
a
child
Skills table, and the skill column has a lookup to Skill Type table;
so
1
=
BE, 2 = AR, etc. (However, the codes are now expanded to words, the
above
is
just to show the reln from number to code).

Is there a query to read the skills row, parse it, and insert a
child
row
for the corresponding skill type? The format of the old skill row is
two
letters "," two letters, etc. eg BE,AR,PS etc.

tia

 

[Duane Hookom]

Congrats.

--
Duane Hookom
MS Access MVP
--

I Got it! I added a new column to the Member Skill Type table, the code
BE,
AR, etc. and now it works correctly! Since the In Str can read that code,
and add the "correct" Skill Code I can't belive it! Thanks so much!

Adding that thrid column was the key to the solution.

Thanks again!

also to clarify, the Skill_Type table does not have BE, CA, etc as
values. it
has words (business expoerience, cashier experience, etc.) Im guessing
this
is why I get 0 rows returned. If I understand the InStr correctly, it's
looking for BE and CA in the
Skill_Type lookup table (which has two columns, ID (auto number) and
Description

I assume OldSkill1 is from the Member table. Your SQL suggests that
Member_Skill_Type_ID is the field containing a single value like "CA".
However I expect Member_SKill_Type_ID is probably your numeric field
but I
can't see your data.

It would certainly help if you took the time to type in your
significant
table and field names.

--
Duane Hookom
MS Access MVP


I think this is the correct corresponding sql from what you gave me
but I
still get a prompt
SELECT Member.MemberID, Member_Skill_Type.Member_Skill_Type_Id
FROM Member, Member_Skill_Type
WHERE (((InStr([OldSkill1],[Member_Skill_Type_Id]))>0));

:

You are ruffling my feathers (if I actually had feathers .

I took the time to create two tables in a sample database
(Northwinds)
with
the exact structure as you describe and I mentioned in my posting.
The
table
and field names were probably not the same since you didn't take the
time
to
provide them to us.

I then typed several records into each table. The values in the
Skills
field
in my members table contained multiple skills like "BE, CA, AR,..".
My
skill
type table contained one row for each skill type just as you
described.

I then created a query with the SQL I provided in my posting and
tested
it
to see if a record from the members table with a Skill field like
"BE,
CA,
AR" created a recordset with three records containing the member ID
and
the
SkillID.

Apparently you got lost along the way or didn't try my suggestion.
If my
solution didn't work, at least do me the courtesy of describing the
results
of my query and why it wasn't appropriate. I believe the query is
that
simple.

--
Duane Hookom
MS Access MVP


it's not that simple. Old table: Member, has column Skills.
Skills
were
entered like this: BE;CA;AR etc New Model has a Skill table as
a
child
of
Member, and the skill member has Skill Type which is a lookup to
Skill_Type
table.

I need to take the skills eg BE;CA;AR and turn that into three
rows in
the
new Skill table, along with translating the BE to it's code (from
Skill
Type), CA, to its code, etc. The other thing is, each old Skill
row has
N
skills typed in, some one (BE), some 5 (BE:CA:RE:TG:FD), etc.
There are
25
possible skills.

Is that clearer?

Thanks for your help.

HB

:

Assuming a table of members, tblMembers, with fields MemberID and
Skills.
Also a skill type table, tblSkillTypes, with fields SkillID and
SkillTitle.
You can append records (MemberID and SkillID) to your new table
based
on
a
query like:

SELECT tblMembers.MemberID, tblSkillTypes.SkillId
FROM tblMembers, tblSkillTypes
WHERE (((InStr([Skills],[SkillTitle]))>0));


--
Duane Hookom
MS Access MVP


since I have been amazed with the amount of knowledge and help
on
here
I'll
throw out another problem, which as of now we are solving
manually.

Old Member table has a field "Skills" with codes CO, BE, AR
etc.
and
the
column can hold mulitple codes separated by a comma. New
format has
a
child
Skills table, and the skill column has a lookup to Skill Type
table;
so
1
=
BE, 2 = AR, etc. (However, the codes are now expanded to words,
the
above
is
just to show the reln from number to code).

Is there a query to read the skills row, parse it, and insert a
child
row
for the corresponding skill type? The format of the old skill
row is
two
letters "," two letters, etc. eg BE,AR,PS etc.

tia