Hopefully someone can point me in the right direction ...
I have a mature "Expert" based application running with several hundred thousand records across numerous related tables ... I have just been requested to add a new link to an existing reference which is to be used as a possible alternate reference "IF" it exists ... without trying to reinvent the wheel, I am attempting to use the existing root cross table, listed below ... I was expecting I should just have to use a multilink Query to take this new reference and link it back to it's main root value ... I am attempting to use a query structure that has 4 links to the same table in one statement using a, b, c and d prefixes ... however ... when I used this query with only 2 prefixes a and b it is crashing Access 2K3 without providing any reasons? Is there anything obvious that have missed? About the only difference between this variation is that the original mutli query is it is returning numbers instead of text values:
SELECT a.Val1 as aVal1, NZ(b.Val2,-1) as bVal2, etc. with 3 LEFT JOINS instead of one.
This example shows my existing cross link table ... all I have done is add a new type of record that needs to be linked back to it's root record.
Table xrw:
SRC as text (Primary index) SEARCH
MGR as number (Primary index) MAIN GROUP NUMBER
PRT as text (Primary index) PRIMARY ROOT
MRT as text MAIN ROOT
The Primary root value is always in PRT
The MGR value had to be added to keep links unique as the "Experts" started creating duplicate Main Root entries
The query/search value is SRC
(SRC is just MRT stripped of all non alpha numeric chars)
The new Alternate Primary Root Value should be b.MRT when a.PRT matches b.PRT and b.MGR = 333 (special case)
Test Data:
SRC MGR PRT MRT
azt 1 abc a-z-t
azv 2 abc a-z-v
azv 3 abc a-z-v
xyz 250 abc x-y-z
qwe 333 abc q-we
azx 1 def a-z-x
xya 250 def x-y-a
Expected Query result for SRC = "xyz":
aRoot bAlt
abc q-we
Expected Query result for SRC = "xya":
aRoot bAlt
def
Query:
Select a.PRT AS aRoot,
b.MRT AS bAlt
FROM (xrw AS a LEFT JOIN [Select (xrw.MRT)
FROM xrw
WHERE (xrw.MGR) = 333].
AS b ON a.PRT = b.PRT)
WHERE ((a.SRC) LIKE ("xyz" & "*"));
Value "xyz" is being substituted for a function fnGetSRC() which I have removed in my testing and this example. 333 is a FIXED special case value which will never change.
I have a mature "Expert" based application running with several hundred thousand records across numerous related tables ... I have just been requested to add a new link to an existing reference which is to be used as a possible alternate reference "IF" it exists ... without trying to reinvent the wheel, I am attempting to use the existing root cross table, listed below ... I was expecting I should just have to use a multilink Query to take this new reference and link it back to it's main root value ... I am attempting to use a query structure that has 4 links to the same table in one statement using a, b, c and d prefixes ... however ... when I used this query with only 2 prefixes a and b it is crashing Access 2K3 without providing any reasons? Is there anything obvious that have missed? About the only difference between this variation is that the original mutli query is it is returning numbers instead of text values:
SELECT a.Val1 as aVal1, NZ(b.Val2,-1) as bVal2, etc. with 3 LEFT JOINS instead of one.
This example shows my existing cross link table ... all I have done is add a new type of record that needs to be linked back to it's root record.
Table xrw:
SRC as text (Primary index) SEARCH
MGR as number (Primary index) MAIN GROUP NUMBER
PRT as text (Primary index) PRIMARY ROOT
MRT as text MAIN ROOT
The Primary root value is always in PRT
The MGR value had to be added to keep links unique as the "Experts" started creating duplicate Main Root entries
The query/search value is SRC
(SRC is just MRT stripped of all non alpha numeric chars)
The new Alternate Primary Root Value should be b.MRT when a.PRT matches b.PRT and b.MGR = 333 (special case)
Test Data:
SRC MGR PRT MRT
azt 1 abc a-z-t
azv 2 abc a-z-v
azv 3 abc a-z-v
xyz 250 abc x-y-z
qwe 333 abc q-we
azx 1 def a-z-x
xya 250 def x-y-a
Expected Query result for SRC = "xyz":
aRoot bAlt
abc q-we
Expected Query result for SRC = "xya":
aRoot bAlt
def
Query:
Select a.PRT AS aRoot,
b.MRT AS bAlt
FROM (xrw AS a LEFT JOIN [Select (xrw.MRT)
FROM xrw
WHERE (xrw.MGR) = 333].
AS b ON a.PRT = b.PRT)
WHERE ((a.SRC) LIKE ("xyz" & "*"));
Value "xyz" is being substituted for a function fnGetSRC() which I have removed in my testing and this example. 333 is a FIXED special case value which will never change.
