Query is too complex for Access 2007 ?

W

Will

Hi,

I'm getting the "Query is too complex" error in Access 2007.

I'm new to Access and am trying to move all my pricing calculations from
excel to access.

So here is my question, Is my query indeed too complex? And how might I
correct this?

Here is the SQL for my query:

SELECT NotActive.SKU, NotActive.Cost,
(([Cost]*[FeeMarkup]![PriceMarkup1to25])/(1-[FeeMarkup]![PriceMarkup1to25]))+[Cost]
AS AddProfit1to25,
((([Cost]-25)*[FeeMarkup]![PriceMarkup25to100])/(1-[FeeMarkup]![PriceMarkup25to100]))+([Cost]-25)+((25*[FeeMarkup]![PriceMarkup1to25])/(1-[FeeMarkup]![PriceMarkup1to25]))+25
AS AddProfit25to100,
((([Cost]-100)*[FeeMarkup]![PriceMarkup100to1000])/(1-[FeeMarkup]![PriceMarkup100to1000]))+([Cost]-100)+((25*[FeeMarkup]![PriceMarkup1to25])/(1-[FeeMarkup]![PriceMarkup1to25]))+25+((75*[FeeMarkup]![PriceMarkup25to100])/(1-[FeeMarkup]![PriceMarkup25to100]))+75
AS AddProfit100to1000,
(([AddProfit1to25]*[FeeMarkup]![eBayStoreFVF1to25])/(1-[FeeMarkup]![eBayStoreFVF1to25]))+[AddProfit1to25]
AS AddEbayFVF1to25,
((([AddProfit25to100]-25)*[FeeMarkup]![eBayStoreFVF25to100])/(1-[FeeMarkup]![eBayStoreFVF25to100]))+([AddProfit25to100]-25)+((25*[FeeMarkup]![eBayStoreFVF1to25])/(1-[FeeMarkup]![eBayStoreFVF1to25]))+25
AS AddEbayFVF25to100,
((([AddProfit100to1000]-100)*[FeeMarkup]![eBayStoreFVF100to1000])/(1-[FeeMarkup]![eBayStoreFVF100to1000]))+([AddProfit100to1000]-100)+((25*[FeeMarkup]![eBayStoreFVF1to25])/(1-[FeeMarkup]![eBayStoreFVF1to25]))+25+((75*[FeeMarkup]![eBayStoreFVF25to100])/(1-[FeeMarkup]![eBayStoreFVF25to100]))+75
AS AddEbayFVF100to1000,
Round(Switch([Cost]<=25,[AddEbayFVF1to25],[Cost]<=100,[AddEbayFVF25to100],[Cost]<=1000,[AddEbayFVF100to1000]),2)
AS ChoosePrice,
(([ChoosePrice]*[FeeMarkup]![PayPal])/(1-[FeeMarkup]![PayPal]))+[ChoosePrice]
AS AddPP,
Round(Switch([AddPP]<=25,[AddPP]*[FeeMarkup]![PriceMarkup1to25],[AddPP]<=100,([AddPP]-25)*[FeeMarkup]![PriceMarkup25to100]+[qryFeeMarkup]![Price25to100Addl],[AddPP]<=1000,([AddPP]-100)*[FeeMarkup]![PriceMarkup100to1000]+[qryFeeMarkup]![Price100to1000Addl]),2)
AS Profit1,
Round(Switch([AddPP]<=25,[AddPP]*[FeeMarkup]![eBayStoreFVF1to25],[AddPP]<=100,([AddPP]-25)*[FeeMarkup]![eBayStoreFVF25to100]+[qryFeeMarkup]![eBay25to100Addl],[AddPP]<=1000,([AddPP]-100)*[FeeMarkup]![eBayStoreFVF100to1000]+[qryFeeMarkup]![eBay100to1000Addl]),2)
AS eBayFVF1, Round([AddPP]*[FeeMarkup]![PayPal],2) AS PPFee1,
[AddPP]-[Cost]-[Profit1]-[eBayFVF1]-[PPFee1] AS PriceMinusCosts1,
Round([AddPP]+(-1*[PriceMinusCosts1])+((-1*[PriceMinusCosts1])*([FeeMarkup]![eBayStoreFVF1to25]+[FeeMarkup]![PriceMarkup1to25]+[FeeMarkup]![PayPal])),2)
AS NewPrice, [NewPrice]-Round([NewPrice],0) AS RoundIt, IIf([RoundIt] Between
0.02 And 0,(Round([NewPrice],0)-0.01),[NewPrice]) AS MakeAttractive
FROM NotActive, FeeMarkup, qryFeeMarkup
WHERE (((NotActive.Cost) Is Not Null Or (NotActive.Cost)<>0));

Any advice would be greatly appreciated.

Thanks, Will
 
J

John W. Vinson

Hi,

I'm getting the "Query is too complex" error in Access 2007.

I'm new to Access and am trying to move all my pricing calculations from
excel to access.

So here is my question, Is my query indeed too complex? And how might I
correct this?
Click to expand...

This looks like a really good case for writing a little VBA function with some
If... Then blocks. I'm hesitant to try it here (at least not until I've had
another double espresso) but that's how I'd handle it.
 
J

Jerry Whittle

It could be a couple of things. There is a limit to the lenght of an SQL
statement however, I don't think that you hit it. You could use an alias for
the tables to shorten the SQL statement. Something like:
FeeMarkup As FM
Then you would put FM in place of the table name thus saving some characters.
[FM]![PriceMarkup1to25]

But the biggest problem is your data structure. That you have field named
like PriceMarkup1to25, PriceMarkup25to100, etc., shows that your data isn't
properly normalized. This will cause all kinds of grief in a relational
database like Access.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Will said:
Hi,

I'm getting the "Query is too complex" error in Access 2007.

I'm new to Access and am trying to move all my pricing calculations from
excel to access.

So here is my question, Is my query indeed too complex? And how might I
correct this?

Here is the SQL for my query:

SELECT NotActive.SKU, NotActive.Cost,
(([Cost]*[FeeMarkup]![PriceMarkup1to25])/(1-[FeeMarkup]![PriceMarkup1to25]))+[Cost]
AS AddProfit1to25,
((([Cost]-25)*[FeeMarkup]![PriceMarkup25to100])/(1-[FeeMarkup]![PriceMarkup25to100]))+([Cost]-25)+((25*[FeeMarkup]![PriceMarkup1to25])/(1-[FeeMarkup]![PriceMarkup1to25]))+25
AS AddProfit25to100,
((([Cost]-100)*[FeeMarkup]![PriceMarkup100to1000])/(1-[FeeMarkup]![PriceMarkup100to1000]))+([Cost]-100)+((25*[FeeMarkup]![PriceMarkup1to25])/(1-[FeeMarkup]![PriceMarkup1to25]))+25+((75*[FeeMarkup]![PriceMarkup25to100])/(1-[FeeMarkup]![PriceMarkup25to100]))+75
AS AddProfit100to1000,
(([AddProfit1to25]*[FeeMarkup]![eBayStoreFVF1to25])/(1-[FeeMarkup]![eBayStoreFVF1to25]))+[AddProfit1to25]
AS AddEbayFVF1to25,
((([AddProfit25to100]-25)*[FeeMarkup]![eBayStoreFVF25to100])/(1-[FeeMarkup]![eBayStoreFVF25to100]))+([AddProfit25to100]-25)+((25*[FeeMarkup]![eBayStoreFVF1to25])/(1-[FeeMarkup]![eBayStoreFVF1to25]))+25
AS AddEbayFVF25to100,
((([AddProfit100to1000]-100)*[FeeMarkup]![eBayStoreFVF100to1000])/(1-[FeeMarkup]![eBayStoreFVF100to1000]))+([AddProfit100to1000]-100)+((25*[FeeMarkup]![eBayStoreFVF1to25])/(1-[FeeMarkup]![eBayStoreFVF1to25]))+25+((75*[FeeMarkup]![eBayStoreFVF25to100])/(1-[FeeMarkup]![eBayStoreFVF25to100]))+75
AS AddEbayFVF100to1000,
Round(Switch([Cost]<=25,[AddEbayFVF1to25],[Cost]<=100,[AddEbayFVF25to100],[Cost]<=1000,[AddEbayFVF100to1000]),2)
AS ChoosePrice,
(([ChoosePrice]*[FeeMarkup]![PayPal])/(1-[FeeMarkup]![PayPal]))+[ChoosePrice]
AS AddPP,
Round(Switch([AddPP]<=25,[AddPP]*[FeeMarkup]![PriceMarkup1to25],[AddPP]<=100,([AddPP]-25)*[FeeMarkup]![PriceMarkup25to100]+[qryFeeMarkup]![Price25to100Addl],[AddPP]<=1000,([AddPP]-100)*[FeeMarkup]![PriceMarkup100to1000]+[qryFeeMarkup]![Price100to1000Addl]),2)
AS Profit1,
Round(Switch([AddPP]<=25,[AddPP]*[FeeMarkup]![eBayStoreFVF1to25],[AddPP]<=100,([AddPP]-25)*[FeeMarkup]![eBayStoreFVF25to100]+[qryFeeMarkup]![eBay25to100Addl],[AddPP]<=1000,([AddPP]-100)*[FeeMarkup]![eBayStoreFVF100to1000]+[qryFeeMarkup]![eBay100to1000Addl]),2)
AS eBayFVF1, Round([AddPP]*[FeeMarkup]![PayPal],2) AS PPFee1,
[AddPP]-[Cost]-[Profit1]-[eBayFVF1]-[PPFee1] AS PriceMinusCosts1,
Round([AddPP]+(-1*[PriceMinusCosts1])+((-1*[PriceMinusCosts1])*([FeeMarkup]![eBayStoreFVF1to25]+[FeeMarkup]![PriceMarkup1to25]+[FeeMarkup]![PayPal])),2)
AS NewPrice, [NewPrice]-Round([NewPrice],0) AS RoundIt, IIf([RoundIt] Between
0.02 And 0,(Round([NewPrice],0)-0.01),[NewPrice]) AS MakeAttractive
FROM NotActive, FeeMarkup, qryFeeMarkup
WHERE (((NotActive.Cost) Is Not Null Or (NotActive.Cost)<>0));

Any advice would be greatly appreciated.

Thanks, Will
Click to expand...
 
Top