SORTING BY PERCENTAGE

J

jeanne

Hi I have this query,

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit, [Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job];

as you can see I am calculating profit percentage... but when I try to sort
my query on this precentage it says it is to difficult of an expression...

Any HElp?
 
K

Ken Snell [MVP]

You're saying that this query gives you the "too difficult" error?

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit, [Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job]
ORDER BY [Profit]/[SumOfBilled Amount];
 
J

jeanne

No this gives me all the data. It is when I try to take this query and have
it sorted in ascending order [order by] that I get the following error:

the expression is too complex to be evaluated.









Ken Snell said:
You're saying that this query gives you the "too difficult" error?

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit, [Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job]
ORDER BY [Profit]/[SumOfBilled Amount];

--

Ken Snell
<MS ACCESS MVP>

jeanne said:
Hi I have this query,

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By
Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit, [Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job];

as you can see I am calculating profit percentage... but when I try to
sort
my query on this precentage it says it is to difficult of an
expression...

Any HElp?
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Sorry, but I'm not following you here. Post the exact SQL statement that is
giving the error.


--

Ken Snell
<MS ACCESS MVP>

jeanne said:
No this gives me all the data. It is when I try to take this query and
have
it sorted in ascending order [order by] that I get the following error:

the expression is too complex to be evaluated.









Ken Snell said:
You're saying that this query gives you the "too difficult" error?

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By
Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit,
[Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job]
ORDER BY [Profit]/[SumOfBilled Amount];

--

Ken Snell
<MS ACCESS MVP>

jeanne said:
Hi I have this query,

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By
Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit,
[Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job];

as you can see I am calculating profit percentage... but when I try to
sort
my query on this precentage it says it is to difficult of an
expression...

Any HElp?
Click to expand...
Click to expand...
Click to expand...
 
J

jeanne

If you take the following SQL then run the query.

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit, [Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job];


Then click on the Acsending toolbar button you get the following error:

So then I tried making a new query...

SELECT [Truck Detail By Job with Profit].Job, [Truck Detail By Job with
Profit].[SumOfBilled Amount], [Truck Detail By Job with Profit].SumOfCost,
[Truck Detail By Job with Profit].Profit, [Truck Detail By Job with
Profit].[Profit Percentage]
FROM [Truck Detail By Job with Profit]
ORDER BY [Truck Detail By Job with Profit].[Profit Percentage];

And still get the error....



Ken Snell said:
Sorry, but I'm not following you here. Post the exact SQL statement that is
giving the error.


--

Ken Snell
<MS ACCESS MVP>

jeanne said:
No this gives me all the data. It is when I try to take this query and
have
it sorted in ascending order [order by] that I get the following error:

the expression is too complex to be evaluated.









Ken Snell said:
You're saying that this query gives you the "too difficult" error?

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By
Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit,
[Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job]
ORDER BY [Profit]/[SumOfBilled Amount];

--

Ken Snell
<MS ACCESS MVP>

Hi I have this query,

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By
Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit,
[Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job];

as you can see I am calculating profit percentage... but when I try to
sort
my query on this precentage it says it is to difficult of an
expression...

Any HElp?
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Is it possible for SumOfBilledAmount or Profit in the original query that
you posted to have a Null or zero value?

If yes, the error you're getting may be caused by that situation.
--

Ken Snell
<MS ACCESS MVP>



jeanne said:
If you take the following SQL then run the query.

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By
Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit, [Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job];


Then click on the Acsending toolbar button you get the following error:

So then I tried making a new query...

SELECT [Truck Detail By Job with Profit].Job, [Truck Detail By Job with
Profit].[SumOfBilled Amount], [Truck Detail By Job with Profit].SumOfCost,
[Truck Detail By Job with Profit].Profit, [Truck Detail By Job with
Profit].[Profit Percentage]
FROM [Truck Detail By Job with Profit]
ORDER BY [Truck Detail By Job with Profit].[Profit Percentage];

And still get the error....



Ken Snell said:
Sorry, but I'm not following you here. Post the exact SQL statement that
is
giving the error.


--

Ken Snell
<MS ACCESS MVP>

jeanne said:
No this gives me all the data. It is when I try to take this query and
have
it sorted in ascending order [order by] that I get the following error:

the expression is too complex to be evaluated.









:

You're saying that this query gives you the "too difficult" error?

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By
Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit,
[Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job]
ORDER BY [Profit]/[SumOfBilled Amount];

--

Ken Snell
<MS ACCESS MVP>

Hi I have this query,

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By
Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit,
[Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job];

as you can see I am calculating profit percentage... but when I try
to
sort
my query on this precentage it says it is to difficult of an
expression...

Any HElp?
Click to expand...
Click to expand...
Click to expand...
 
J

jeanne

Thanks,
I did another field that said if sales were zero make percentage zero and
it worked... Thanks...

Ken Snell said:
Is it possible for SumOfBilledAmount or Profit in the original query that
you posted to have a Null or zero value?

If yes, the error you're getting may be caused by that situation.
--

Ken Snell
<MS ACCESS MVP>



jeanne said:
If you take the following SQL then run the query.

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By
Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit, [Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job];


Then click on the Acsending toolbar button you get the following error:

So then I tried making a new query...

SELECT [Truck Detail By Job with Profit].Job, [Truck Detail By Job with
Profit].[SumOfBilled Amount], [Truck Detail By Job with Profit].SumOfCost,
[Truck Detail By Job with Profit].Profit, [Truck Detail By Job with
Profit].[Profit Percentage]
FROM [Truck Detail By Job with Profit]
ORDER BY [Truck Detail By Job with Profit].[Profit Percentage];

And still get the error....



Ken Snell said:
Sorry, but I'm not following you here. Post the exact SQL statement that
is
giving the error.


--

Ken Snell
<MS ACCESS MVP>

No this gives me all the data. It is when I try to take this query and
have
it sorted in ascending order [order by] that I get the following error:

the expression is too complex to be evaluated.









:

You're saying that this query gives you the "too difficult" error?

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By
Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit,
[Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job]
ORDER BY [Profit]/[SumOfBilled Amount];

--

Ken Snell
<MS ACCESS MVP>

Hi I have this query,

SELECT [Truck Detail By Job].Job, [Truck Detail By Job].[SumOfBilled
Amount], [Truck Detail By Job].SumOfCost, [Truck Detail By
Job]![SumOfBilled
Amount]-[Truck Detail By Job]![SumOfCost] AS Profit,
[Profit]/[SumOfBilled
Amount] AS [Profit Percentage]
FROM [Truck Detail By Job];

as you can see I am calculating profit percentage... but when I try
to
sort
my query on this precentage it says it is to difficult of an
expression...

Any HElp?
Click to expand...
Click to expand...
Click to expand...
 
Top