Separating Parent and Child Fields

[rama]

Hello,
I have table called “tblTag” in that one of the field name is called
“Tag”. The Tag field contains some equipment names which are in the
below format. In the below list there are two parent tags ie. 21-
TIC-1000 and 21-LIC-1000. Remaining are its child tags. I am looking
for some help to separate this parent and child in separate tables
with the help of some queries.
TAG
21-TIC-1000
21-TI-1000
21-TY-1000
21-TAH-1000
21-TAL-1000
21-LIC-1000
21-LI-1000
21-LY-1000
21-LAH-1000
21-LAL-1000

Thanks
Rama

 

[Tom van Stiphout]

On Sun, 24 Jan 2010 09:26:13 -0800 (PST), rama <[email protected]>
wrote:

You have not fully defined the problem yet. What is so special about
those two tags that you consider them parent tags versus all the other
ones?

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

Hello,
I have table called “tblTag” in that one of the field name is called
“Tag”. The Tag field contains some equipment names which are in the
below format. In the below list there are two parent tags ie. 21-
TIC-1000 and 21-LIC-1000. Remaining are its child tags. I am looking
for some help to separate this parent and child in separate tables
with the help of some queries.
TAG
21-TIC-1000
21-TI-1000
21-TY-1000
21-TAH-1000
21-TAL-1000
21-LIC-1000
21-LI-1000
21-LY-1000
21-LAH-1000
21-LAL-1000

Thanks
Rama

Ok, *you* can tell that 21-TIC-1000 and 21-LIC-1000 are parent tags, and the
rest are child tags. I cannot do more than guess that a tag containing the
text string "IC" is a parent, and that all others are children; and I can
speculate that perhaps the first letter after the hyphen identifies that tag's
children. And of course Access cannot (by itself) do even that much!

That is of course pure speculation, and (if it's so) indicates a really
dreadful use of "intelligent keys".

Is my speculation close to the mark? If so, would the IC value indeed flag
parents? What would flag that parent's children? What else is in this table,
and what do you want to end up in the "parent" and "child" tables?

 

[rama]

Ok, *you* can tell that 21-TIC-1000 and 21-LIC-1000 are parent tags, and the
rest are child tags. I cannot do more than guess that a tag containing the
text string "IC" is a parent, and that all others are children; and I can
speculate that perhaps the first letter after the hyphen identifies that tag's
children. And of course Access cannot (by itself) do even that much!

That is of course pure speculation, and (if it's so) indicates a really
dreadful use of "intelligent keys".

Is my speculation close to the mark? If so, would the IC value indeed flag
parents? What would flag that parent's children? What else is in this table,
and what do you want to end up in the "parent" and "child" tables?
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Thank you very much for the reply.
The tags contains *IC* will be parent tags.
The other fields are “Date”, “Time”, “Tag”, Type”, “Identifier” etc.
The data is originating from control system alarms. Why I am in
interested in “parent” / “child” is because the amount of data is
approximately 24000 records per day and due to this huge amount it is
difficult to analyze. Mostly Parent and child alarms are appearing
together. This will only overload the system and sometimes real ones
got ignored. If I am able to separate automatically the amount of
alarm can be reduced more than 60%. Also tags like 21-L*1000 will be
related to 21-LIC-1000 and 21-T*1000 belongs to 21-TIC-1000.

 

[John W. Vinson]

The tags contains *IC* will be parent tags.
The other fields are “Date”, “Time”, “Tag”, Type”, “Identifier” etc.
The data is originating from control system alarms. Why I am in
interested in “parent” / “child” is because the amount of data is
approximately 24000 records per day and due to this huge amount it is
difficult to analyze. Mostly Parent and child alarms are appearing
together. This will only overload the system and sometimes real ones
got ignored. If I am able to separate automatically the amount of
alarm can be reduced more than 60%. Also tags like 21-L*1000 will be
related to 21-LIC-1000 and 21-T*1000 belongs to 21-TIC-1000.

Just to clarify a bit more... if I find a tag containing *IC*, what part of
the Tag field identifies its children? That is, if I find 21-LIC-1000 does it
relate to any record containing an L after the first hyphen; or any record
starting with 21-L; or any record starting with 21-L and ending with 1000?

And what (if any) constraints are there on the size or contents of the three
components of this "intelligent" key?

 

[rama]

Just to clarify a bit more... if I find a tag containing *IC*, what part of
the Tag field identifies its children? That is, if I find 21-LIC-1000 does it
relate to any record containing an L after the first hyphen; or any record
starting with 21-L; or any record starting with 21-L and ending with 1000?

And what (if any) constraints are there on the size or contents of the three
components of this "intelligent" key?

All the parent tag will be like ??-?IC??? and child tags will have the
same prefix and suffix of parent tags. Also first letter after the
hyphen will same as parent tag. For example child tags of 21-LIC-1000
will be 21-L*1000.
Rama

 

[Tom van Stiphout]

Using the * and ? wildcards we can create two queries:

Parent tags:
select * from myTable
where Tag like '*-?IC-*'

Child tags:
select * from myTable
where Tag not like '*-?IC-*'

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

All the parent tag will be like ??-?IC??? and child tags will have the
same prefix and suffix of parent tags. Also first letter after the
hyphen will same as parent tag. For example child tags of 21-LIC-1000
will be 21-L*1000.
Rama

Parents:

INSERT INTO Parents
SELECT * FROM table
WHERE Tag LIKE "##-?IC-*";

Children:

INSERT INTO children (Tag, PTag, <other fields>)
SELECT T.Tag, P.Tag, T.<other fields>
FROM Parents AS P INNER JOIN table AS T
ON T.Tag LIKE (Left(P.Tag, InStr(P.Tag, "IC-") - 1) & "*" & T.Tag LIKE "*" &
Mid(P.Tag, "IC-") + 4;

should do it. You may have to play around with the LIKE clauses.