INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query causing exception error???

Query causing exception error???

(OP)
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.

RE: Query causing exception error???

(OP)
Just looked up NZ() and it can be used for Text as well

so added the following change to the QUERY:

SELECT a.PRT as aRoot,
       NZ(b.MRT,"") AS bAlt ...

Query still crashing ACCESS 2K3

RE: Query causing exception error???

(OP)
Seaching for "derived" Joins to SAME table, I came up with what appears to be a simple solution:

http://databases.about.com/od/sql/a/selfjoins.htm

SELECT x.prt AS xprt, y.mrt AS ymrt
FROM xrw AS x LEFT JOIN xrw AS y ON (x.prt & 333)=(y.prt & y.mdd)
WHERE (x.src like "xyz" & "*");
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close