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

Hi Will,

the "Too Complex" message is a sort of semantics thing meaning the query
is either too long or there is some circular refence like thing going
on.

What I would try is to just query on one item for starters. Most
likely, your current query is trying to query on apples and oranges - so
to speak - and can't make a definite determination of what is the
desired/correct resultset. Usually, this would be caused by null values
in one item and there are actual values in another item. This is why I
would start by just querying on one item and see how that works. Then
you could create a loop in VBA to query the rest of items individually.
But start with just one item first.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Will wrote:
in other locations for calculating as well.

As Rich said, and I agree, it's too complicated you might want to cut
you a columns down 1 by 1 and see if they work.

I'd consider another approach as well. Calculate all of the static
columns first. For example. create a query that calcs Profit1To25 and
any others that used in further calculations. Save it as Query1 for
example. You might not need all of the tables to get the static values.
Now create a new query and link your tables to Query1 and calc the
columns out.

This is a cheap example.
'Query1
Select [FirstName] & " " & [LastName] As FullName From Table1...

'Query2
Select FullName, Issues.MoreIssues From Query1 inner join Issues....

You'd run Query2 to present the results. Ex even further
Run query4
it uses data from query3
Query3 uses data from query2
Query2 runs data from query1