CMDAI
asked on
Cross Table , Interchange information
I've been cracking my head for some time on this now,
I need to record interchange infomration:
the records come in like this:
Record Type1: Item 'Q' = Item 'W' ::Item Q is the same as item W
Record Type2: Item 'A' -> Item 'B' ::Item A Replaced by Item B
Now my guest guess was to seperate this into 2 tables
Where Type 1:
Item | Group
'1' | 'A'
'2' | 'B'
'3' | 'C'
'4' | 'C'
'5' | 'C'
If i want to see #4 then trhough group i get "3,4,5" all '='
Type 2:
Item | Into Item
'1' | '2'
'1' | '3'
'4' | '5'
If i want to see #1 then though i get "2,3" all replace '1'
Now here comes the problem
1.With close to a million records it takes a really of a long time to process,
1.1 I Create an array in keep looping throught table 1 and 2 untill no new records are found
Here is now it works Based on the above tables
Query:#1
: 1
:2 | 3,4
:6 | 5
*** I also Identify loops with this procedure, if i have records like "5 ->1"
I need to record interchange infomration:
the records come in like this:
Record Type1: Item 'Q' = Item 'W' ::Item Q is the same as item W
Record Type2: Item 'A' -> Item 'B' ::Item A Replaced by Item B
Now my guest guess was to seperate this into 2 tables
Where Type 1:
Item | Group
'1' | 'A'
'2' | 'B'
'3' | 'C'
'4' | 'C'
'5' | 'C'
If i want to see #4 then trhough group i get "3,4,5" all '='
Type 2:
Item | Into Item
'1' | '2'
'1' | '3'
'4' | '5'
If i want to see #1 then though i get "2,3" all replace '1'
Now here comes the problem
1.With close to a million records it takes a really of a long time to process,
1.1 I Create an array in keep looping throught table 1 and 2 untill no new records are found
Here is now it works Based on the above tables
Query:#1
: 1
:2 | 3,4
:6 | 5
*** I also Identify loops with this procedure, if i have records like "5 ->1"
ASKER
Tbale 2:
Title: | FROM Item | To Item |
Record: | Item "A" | Item "B" |
Item "B" can replace Item "A"
It is not the same as A = B because
Item "A" canNot replace Item "B"
Title: | FROM Item | To Item |
Record: | Item "A" | Item "B" |
Item "B" can replace Item "A"
It is not the same as A = B because
Item "A" canNot replace Item "B"
Ok, CMDAI,
what query, do you like optimize?
what query, do you like optimize?
ASKER
Initially i instead o making 2 tables i had only 1
It looked like this:
|-|Item "A" |-| = |-| Item "B"|-|
|-|Item "C" |-| -> |-| Item "E"|-|
|-|Item "C" |-| -> |-| Item "D"|-|
|-|Item "D" |-| -> |-| Item "W"|-|
|-|Item "A" |-| = |-| Item "C"|-|
It looked like this:
|-|Item "A" |-| = |-| Item "B"|-|
|-|Item "C" |-| -> |-| Item "E"|-|
|-|Item "C" |-| -> |-| Item "D"|-|
|-|Item "D" |-| -> |-| Item "W"|-|
|-|Item "A" |-| = |-| Item "C"|-|
ASKER
1:Item | Group
'1' | 'A'
'2' | 'B'
'3' | 'C'
'4' | 'C'
'5' | 'C'
2:Item | Into Item
'1' | '2'
'1' | '3'
'4' | '5'
If i want to get all the items that replace #1
I start a loop in VB and collect and Array
1,( ) * Initial
2,(from Table 2) *Query 1
3,(from Table 2) * Query 1
4,(from Table 1) * Query 2
5,(from Table 1) * Query 2
5,(from Table 2) * Query 3
'1' | 'A'
'2' | 'B'
'3' | 'C'
'4' | 'C'
'5' | 'C'
2:Item | Into Item
'1' | '2'
'1' | '3'
'4' | '5'
If i want to get all the items that replace #1
I start a loop in VB and collect and Array
1,( ) * Initial
2,(from Table 2) *Query 1
3,(from Table 2) * Query 1
4,(from Table 1) * Query 2
5,(from Table 1) * Query 2
5,(from Table 2) * Query 3
ASKER
Sorry typo:
1,( ) * Initial
2,(from Table 2) *Query 1
3,(from Table 2) * Query 1
4,(from Table 1) * Query 2
5,(from Table 1) * Query 2
6,(from Table 2) * Query 3
Then it stops as no new numbers canbe found
1,( ) * Initial
2,(from Table 2) *Query 1
3,(from Table 2) * Query 1
4,(from Table 1) * Query 2
5,(from Table 1) * Query 2
6,(from Table 2) * Query 3
Then it stops as no new numbers canbe found
ASKER
If I covert everything into table 2 format this is what i am trying to do
but i cannot predict how long the cross is going to be.
Cross.jpg
but i cannot predict how long the cross is going to be.
Cross.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ops!
"@K -> item" and "item -> @K" querys are "FROM Q2" instead "FROM Q1" (obviously).
And check your index!!!!!! optimize.
The running cost are linear over initial total row count.
Good luck!
"@K -> item" and "item -> @K" querys are "FROM Q2" instead "FROM Q1" (obviously).
And check your index!!!!!! optimize.
The running cost are linear over initial total row count.
Good luck!
ASKER
Sorry i'm trying to recreate but i got stuck on this part
"""""""""""""""""""""""""" """""""""" ""
JOIN Q1 q
ON g.group = q.group
"""""""""""""""""""""""""" """""""""" """"
From:
INSERT INTO Q1 (
itemK,
item
)
SELECT g.itemK,
q.item
FROM (
SELECT group, itemK = MIN( item )
FROM T1
GROUP
BY group
) g
JOIN Q1 q
ON g.group = q.group
""""""""""""""""""""""""""
JOIN Q1 q
ON g.group = q.group
""""""""""""""""""""""""""
From:
INSERT INTO Q1 (
itemK,
item
)
SELECT g.itemK,
q.item
FROM (
SELECT group, itemK = MIN( item )
FROM T1
GROUP
BY group
) g
JOIN Q1 q
ON g.group = q.group
Yes, group field only is in T1, you must write
JOIN T1 q
ON g.group = q.group
Sorry!
JOIN T1 q
ON g.group = q.group
Sorry!
ASKER
Sorry i wans't arround yestruday,
I tried to recreate but got confused , maybe because im doing it in access
and the language structure is different.
So Q1 is
[Key Item] || [Item]
Basically it looks like the [group] has been replaced by one of the items in the [group]
Your result example:
itemK item
------- -------
1 1
2 2
3 3
3 4
3 5
Will this also be Inside?
4 4
4 3
THen Q2 I'm confused
I tried to recreate but got confused , maybe because im doing it in access
and the language structure is different.
So Q1 is
[Key Item] || [Item]
Basically it looks like the [group] has been replaced by one of the items in the [group]
Your result example:
itemK item
------- -------
1 1
2 2
3 3
3 4
3 5
Will this also be Inside?
4 4
4 3
THen Q2 I'm confused
Oh! Access... yes.
I suggest to you use a better database engine(like mysql, ms sql server 2005 express, ...).
Nevertheless, the only important change are the WHILE iteration and you can make this using the WHILE VB sentence.
On the other hand, my result example are good, only one itemK is needed for "linked relations". Tuples (3,4), (4,3) are equals and only one must be in table.
The Q1 table are a simple translation of your Type1 table.
For all ( itemK, item ) tuples than itemK<>item, then item must not be in Q2 (not is needed, if itemK are in Q2 then I deduce item are also in Q2).
A complete example:
Type1 relations
----------------------
1 A
2 B
3 A
4 C
5 B
6 D
7 B
Type2 relations
----------------------
3 7
5 6
4 6
Then we generate Q1 table as a simple translation of id group:
Minimum key for each Id group
-------------------------- ------
1 A
2 B
4 C
6 D
Q1
-----
1 1
1 3
2 2
2 5
2 7
4 4
6 6
In Q2, only itemK!
Q2 (+first insert)
--------
1 2
2 6
4 6
Q2 (+second insert)
--------
1 2
2 6
4 6
1 6
Stop!
Consequences:
1 and 3 elements are equals and equivalents.
2, 5 and 7 elements are equals and equivalents.
4 are alone.
6 are alone.
1 or 3 element can be replaced with any of 2, 5 and 7.
2, 5 or 7 element can be replaced with 6.
4 element can be replaced with 6.
1 or 3 element can be replaced with 6.
If I have not a error...
Good luck!
I suggest to you use a better database engine(like mysql, ms sql server 2005 express, ...).
Nevertheless, the only important change are the WHILE iteration and you can make this using the WHILE VB sentence.
On the other hand, my result example are good, only one itemK is needed for "linked relations". Tuples (3,4), (4,3) are equals and only one must be in table.
The Q1 table are a simple translation of your Type1 table.
For all ( itemK, item ) tuples than itemK<>item, then item must not be in Q2 (not is needed, if itemK are in Q2 then I deduce item are also in Q2).
A complete example:
Type1 relations
----------------------
1 A
2 B
3 A
4 C
5 B
6 D
7 B
Type2 relations
----------------------
3 7
5 6
4 6
Then we generate Q1 table as a simple translation of id group:
Minimum key for each Id group
--------------------------
1 A
2 B
4 C
6 D
Q1
-----
1 1
1 3
2 2
2 5
2 7
4 4
6 6
In Q2, only itemK!
Q2 (+first insert)
--------
1 2
2 6
4 6
Q2 (+second insert)
--------
1 2
2 6
4 6
1 6
Stop!
Consequences:
1 and 3 elements are equals and equivalents.
2, 5 and 7 elements are equals and equivalents.
4 are alone.
6 are alone.
1 or 3 element can be replaced with any of 2, 5 and 7.
2, 5 or 7 element can be replaced with 6.
4 element can be replaced with 6.
1 or 3 element can be replaced with 6.
If I have not a error...
Good luck!
ASKER
Thank's for your help, sorry for long responsiveness, had wasnt arround for a long time,
Works great now ! :)
Works great now ! :)
I don't understan what is table 2.
explain please.