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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Split delimited field data TWICE to populate specific other fields 3
Subquery not integrating to query to display previous sum of quantities 3
multiple critera for one field in a query 10
Running balance for debit and Credit using query in Access 2007 0
need help making a query (crosstab instead of Pivot tables?) 3
Number range as a percentage 2
Query Help Please 2
Need Help With SQL Query 2

Top