I am getting "Query is too complex" (1 Viewer)

[sirkistova]

I am getting "Query is too complex" when I try to open a report.
The DB was working fine, but 4 values that were hard coded really needed to be soft coded, because a. they repeated themselves in several queries and reports, and b. they need to be configured easily in 1 place.
So I made a new table to hold the 4 values, and a form to configure them. And I changed in the queries from the constant scalars that these values used to be, to fields from a table. And then 1 report stopped working - I get "Query is too complex" when I try to open it.
3 of the 4 values are numbers and take part in calculations in queries. The 4th 1 is text.
What can I do.
Thanks
Tova

 

[spikepl]

http://www.blueclaw-db.com/accessquerysql/too_complex.htm

 

[sirkistova]

OK, here are the 2 queries that had changed when I changed the 4 values from scalars to DB fields:

qryInvoice:
SELECT IIf(ReciprocityCount>FreeRecieversNum,(ReciprocityCount-FreeRecieversNum),0) AS ReciprocityBillCount,
IIf(ReciprocityBillCount>0,ReciprocityBillCount*RecieverCost,0) AS ReciprocityCost,
IIf(RegularCount>0,RegularCount*RecieverCost,0) AS RegularCost,
(ReciprocityCost+RegularCost+ReciprocityOptCost) AS TotalCost,
(TotalCost-AmountPaid) AS BalanceDue, (AmountPaid*1) AS AmountPaidCur,
FullName, RegularCount, ReciprocityCount, ReciprocityOptCost, AmountPaid, PetekFooter
FROM qryInvoiceSendCnts;

qryInvoiceSendCnts:
SELECT MEMBER.LASTNAME+', '+MEMBER.FIRSTNAME AS FullName,
(select count(SenderReciever.RecieverMemberCode) from SenderReciever where SenderReciever.Reciprocity = False and SenderReciever.SenderMemberCode = MEMBER.MemberCode) AS RegularCount,
(select count(SenderReciever.RecieverMemberCode) from SenderReciever where SenderReciever.Reciprocity = True and SenderReciever.SenderMemberCode = MEMBER.MemberCode) AS ReciprocityCount,
IIf(MEMBER.Reciprocity,Prices.ReciprocityCost,0) AS ReciprocityOptCost, Nz(MEMBER.AmountPaid,0) AS AmountPaid, Prices.Reciever AS RecieverCost, Prices.FreeRecievers AS FreeRecieversNum, PetekFooter
FROM MEMBER, Prices;

qryInvoice is the query used by the report that fails to open due to query too complex.
Prices is the table that holds the 4 scalar fields. It contains 1 row.
The query qryInvoiceSendCnts duplicates the row N times where N is the length of Members table. This doesn't seem right, but is there another way to use the values from Prices table, for each member?
Also, the 2nd and 3rd IIFs in qryInvoice, I added when I changed from scalars to DB fields. When the values were hardcoded integers, when the multiplication was 0 it showed 0.
But when I changed to DB fields, when the multiplication was 0 it showed empty. (null?) I don't know why. But the IIF is in order to solve that.

Thanks
Tova