SORTING BY PERCENTAGE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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];
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top