trouble with CIRCULAR REFERENCE

[humvie]

I'm having trouble running this query. Someone suggested that I break down the query into two parts but I have no idea how to do this. I'm hoping anyone can help shed some light.

SELECT DISTINCTROW 
tblRatesHeader.Quote, 
tblRatesHeader.Origin, 
tblRatesHeader.Destination, 
tblRatesHeader.Mode, 
tblRateDetail.Rate, 
tblRateDetail.Weight, 
tblRateDetail.WgtBrk, 
tblRateDetail.Qty, 
IIf([qty]="MC" 
Or [RateType]="FLAT" 
Or [qty]="1" 
Or [qty]="2" 
Or [qty]="3",
[RATE],
(IIf([txtWeight]<=[Weight],[Weight]*[Rate],[txtWeight]*[Rate]))) AS Net,

(IIf([qty] = "MAX" AND Net >= [Rate]),[Rate], Net) As Net2

FROM tblRatesHeader INNER JOIN tblRateDetail ON tblRatesHeader.Quote = tblRateDetail.Quote;
FROM tblRatesHeader 
INNER JOIN tblRateDetail ON tblRatesHeader.Quote = tblRateDetail.Quote;

This query works without the bold lettering but when adding the bold leterring, I get a circular reference. Is there a way around this?

Thanks in advance.

 

[jebry]

Hi!

Try this:

query1:

SELECT DISTINCTROW
tblRatesHeader.Quote,
tblRatesHeader.Origin,
tblRatesHeader.Destination,
tblRatesHeader.Mode,
tblRateDetail.Rate,
tblRateDetail.Weight,
tblRateDetail.WgtBrk,
tblRateDetail.Qty,
IIf([qty]="MC"
Or [RateType]="FLAT"
Or [qty]="1"
Or [qty]="2"
Or [qty]="3",
[RATE2],
(IIf([txtWeight]<=[Weight],[Weight]*[Rate],[txtWeight]*[Rate]))) AS Net
FROM tblRatesHeader INNER JOIN tblRateDetail ON tblRatesHeader.Quote = tblRateDetail.Quote;
FROM tblRatesHeader
INNER JOIN tblRateDetail ON tblRatesHeader.Quote = tblRateDetail.Quote;

query2:

Select Quote, Origin, Destination, Mode, Rate, Weight, WgtBrk, Qty, Rate2, Net, (IIf([qty] = &quot;MAX&quot; AND Net >= [Rate]),[Rate], Net) As Net2 From query1

hth


Jeff Bridgham
bridgham@purdue.edu

 

[humvie]

thanks Jeff, I'll give it a try