HELP SOUGHT: Access query returning Error in one column

C

Clinton M James

I am having a problem with a query i have written and was wondering if
anybody can help tell me why.

If I do not put the end where statment (Where tiered.posi....) it works fine
but when i include a where clause the column for "claimnumber" displays
#error all down it and then i get excel tell me there is no current record.

I can't see anything wrong with the query so would appreciate anybody
telling what may be the cause.

The query is as follows:


SELECT tiered.assessorcode, tiered.claimnumber, tiered.posit, tiered.tier
FROM [select
claim.assessorcode,
claim.claimnumber,
(SELECT x.tier FROM `E:\NQA\NQA\NQA_Operational.MDB`.stafftiers X where
x.unumber = (SELECT TOP 1 U.uNumber from
`E:\NQA\NQA\NQA_Operational.MDB`.historicalInfo U where u.assessorcode =
claim.assessorcode and u.dateFROM <= (SELECT R.dateExtracted from
auditscrapeIndex r where r.runnum = 1) order by u.datefrom desc)) as tier,

(select COUNT(claim2.claimnumber)
from auditscrapes claim2
where
claim2.claimnumber <= claim.claimnumber
and claim2.runnum = 1
and claim2.assessorcode = claim.assessorcode) as posit

from
auditscrapes claim
where runnum = 1
group by claim.assessorcode, claim.claimnumber
]. AS tiered
WHERE tiered.posit <=
iif((SELECT tt.tiertype from `E:\NQA\NQA\NQA_Operational.MDB`.tierInfo tt
where tt.tier = tiered.tier) = 'N',(SELECT tt.tierNumber from
`E:\NQA\NQA\NQA_Operational.MDB`.tierInfo tt where tt.tier = tiered.tier),
(SELECT COUNT(*) from auditscrapes cl where cl.assessorcode =
tiered.assessorcode and cl.runnum = 1) *((SELECT tt.tierNumber from
`E:\NQA\NQA\NQA_Operational.MDB`.tierInfo tt where tt.tier =
tiered.tier)/100.00) );

Thank you in advance for your assistance.

Cheers,
Clint
 
D

Dale Fye

Clint,

I believe your problem is that you are trying to use a computed field (tier)
in your WHERE clause.

Why did you choose to use the syntax:

SELECT .. FROM 'E:\NQA\NQA\NQA_Operational...'

Instead of linking the table to your database? Linking the various tables
from that datafile to your application would be significantly cleaner, and
probably faster as well.

What is the relationship between your auditscrapeIndex and AuditScrapes
tables? It might be simplier and is most likely more efficient to create
some sort of join between those tables to get at your Tier and Posit values
than using the nested subqueries.

My guess is that if you broke this up into two queries (one to extract the
various fields you need from the NQA_Operational.mdb (all of which appear to
be limited by a Runnum = 1) and then jion that query to your main query, this
would be a lot cleaner.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Clinton M James said:
I am having a problem with a query i have written and was wondering if
anybody can help tell me why.

If I do not put the end where statment (Where tiered.posi....) it works fine
but when i include a where clause the column for "claimnumber" displays
#error all down it and then i get excel tell me there is no current record.

I can't see anything wrong with the query so would appreciate anybody
telling what may be the cause.

The query is as follows:


SELECT tiered.assessorcode, tiered.claimnumber, tiered.posit, tiered.tier
FROM [select
claim.assessorcode,
claim.claimnumber,
(SELECT x.tier FROM `E:\NQA\NQA\NQA_Operational.MDB`.stafftiers X where
x.unumber = (SELECT TOP 1 U.uNumber from
`E:\NQA\NQA\NQA_Operational.MDB`.historicalInfo U where u.assessorcode =
claim.assessorcode and u.dateFROM <= (SELECT R.dateExtracted from
auditscrapeIndex r where r.runnum = 1) order by u.datefrom desc)) as tier,

(select COUNT(claim2.claimnumber)
from auditscrapes claim2
where
claim2.claimnumber <= claim.claimnumber
and claim2.runnum = 1
and claim2.assessorcode = claim.assessorcode) as posit

from
auditscrapes claim
where runnum = 1
group by claim.assessorcode, claim.claimnumber
]. AS tiered
WHERE tiered.posit <=
iif((SELECT tt.tiertype from `E:\NQA\NQA\NQA_Operational.MDB`.tierInfo tt
where tt.tier = tiered.tier) = 'N',(SELECT tt.tierNumber from
`E:\NQA\NQA\NQA_Operational.MDB`.tierInfo tt where tt.tier = tiered.tier),
(SELECT COUNT(*) from auditscrapes cl where cl.assessorcode =
tiered.assessorcode and cl.runnum = 1) *((SELECT tt.tierNumber from
`E:\NQA\NQA\NQA_Operational.MDB`.tierInfo tt where tt.tier =
tiered.tier)/100.00) );

Thank you in advance for your assistance.

Cheers,
Clint
Click to expand...
 
Top