Filtering between values

C

Carl

I need to filter a datebase for freight charges that are in percentage.
Specifically, I only want to see invoices that have a GM after freight, that
are equal to or less that 5%. I have tried to modify my query, but only get
a 'Division by Zero' response. I have, also, tried to use the PivotTable and
am unable to figure it out. Has anyone tried to sort/filter by this type of
varible before?
 
S

Steve Schapel

Carl,

You haven't given enough detail for anyone to give a specific response.
Do you have a calculated field in your query, which involves dividing
one value by another? Sounds like that's the place to look for the
problem. If the value you are trying to divide by is sometimes 0 or
Null, your calculation expression will need to be modified to handle
this eventuality.
 
C

Carl Naake

Yes I do have a calculation it is GM After %:
Round(([price]-[cost]-[freight])/[price],2). This math varible is not the
problem, as it works just fine. I want to be able to have a report that ONLY
shows particular records (invoices) that are equal to or less than 5%.
 
S

Steve Schapel

Carl,

Well you can use a criteria in this calculated field, in this case it
would be...
<=.05

--
Steve Schapel, Microsoft Access MVP


Carl said:
Yes I do have a calculation it is GM After %:
Round(([price]-[cost]-[freight])/[price],2). This math varible is not the
problem, as it works just fine. I want to be able to have a report that ONLY
shows particular records (invoices) that are equal to or less than 5%.

:

Carl,

You haven't given enough detail for anyone to give a specific response.
Do you have a calculated field in your query, which involves dividing
one value by another? Sounds like that's the place to look for the
problem. If the value you are trying to divide by is sometimes 0 or
Null, your calculation expression will need to be modified to handle
this eventuality.
Click to expand...
Click to expand...
 
C

Carl Naake

I have tried to enter similar criteria, and always get the Division by Zero.
What I do not understand is whether it should be in bracket, parentheses, of
just by itself. And, if it is to be only what you indicated, then the same
response is generated.

Steve Schapel said:
Carl,

Well you can use a criteria in this calculated field, in this case it
would be...
<=.05

--
Steve Schapel, Microsoft Access MVP


Carl said:
Yes I do have a calculation it is GM After %:
Round(([price]-[cost]-[freight])/[price],2). This math varible is not the
problem, as it works just fine. I want to be able to have a report that ONLY
shows particular records (invoices) that are equal to or less than 5%.

:

Carl,

You haven't given enough detail for anyone to give a specific response.
Do you have a calculated field in your query, which involves dividing
one value by another? Sounds like that's the place to look for the
problem. If the value you are trying to divide by is sometimes 0 or
Null, your calculation expression will need to be modified to handle
this eventuality.

--
Steve Schapel, Microsoft Access MVP


Carl wrote:

I need to filter a datebase for freight charges that are in percentage.
Specifically, I only want to see invoices that have a GM after freight, that
are equal to or less that 5%. I have tried to modify my query, but only get
a 'Division by Zero' response. I have, also, tried to use the PivotTable and
am unable to figure it out. Has anyone tried to sort/filter by this type of
varible before?
Click to expand...
Click to expand...
Click to expand...
 
C

Carl Naake

As a side note

I entered =0.05 in the criteria, and
<0.05 in the or

and the query ran, but with #error in the column

Carl Naake said:
I have tried to enter similar criteria, and always get the Division by Zero.
What I do not understand is whether it should be in bracket, parentheses, of
just by itself. And, if it is to be only what you indicated, then the same
response is generated.

Steve Schapel said:
Carl,

Well you can use a criteria in this calculated field, in this case it
would be...
<=.05

--
Steve Schapel, Microsoft Access MVP


Carl said:
Yes I do have a calculation it is GM After %:
Round(([price]-[cost]-[freight])/[price],2). This math varible is not the
problem, as it works just fine. I want to be able to have a report that ONLY
shows particular records (invoices) that are equal to or less than 5%.

:


Carl,

You haven't given enough detail for anyone to give a specific response.
Do you have a calculated field in your query, which involves dividing
one value by another? Sounds like that's the place to look for the
problem. If the value you are trying to divide by is sometimes 0 or
Null, your calculation expression will need to be modified to handle
this eventuality.

--
Steve Schapel, Microsoft Access MVP


Carl wrote:

I need to filter a datebase for freight charges that are in percentage.
Specifically, I only want to see invoices that have a GM after freight, that
are equal to or less that 5%. I have tried to modify my query, but only get
a 'Division by Zero' response. I have, also, tried to use the PivotTable and
am unable to figure it out. Has anyone tried to sort/filter by this type of
varible before?
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Schapel

Carl,

And what happens if you runthe query with no criteria at all?

As mentioned previously, it would be good if you could give some more
information. For example, maybe posting the SQL view of the query would
help.
 
C

Carl Naake

Since I have had no trouble running the query, and just want to find a way to
narrow down the results. I can safely say that there is, again no problem
with the query itself.

However, having never used this method of trying to obtain information, I
feel that simply asking for more information is rather vague. I could
provide most anything, even if it has nothing to do with the issue.

I truly only want to know if Access can narrow down the return of a query to
just a few records.

Now that you what specific informtion, I can provide that. SQL of the query:

SELECT Freight.Company, Freight.Carrier, Freight.Product, Freight.Invoice,
Freight.Weight, Freight.Price, Freight.Cost, Freight.Freight,
Avg(Round([freight]/[weight],4)) AS [Frt/Lb], Sum([price]-[cost]) AS [GM
Before], Round(([price]-[cost])/[price],2) AS [GM Before %],
Sum([price]-[cost]-[freight]) AS [GM After],
Round(([price]-[cost]-[freight])/[price],2) AS [GM After %], Freight.Reviewed
FROM Freight
GROUP BY Freight.Company, Freight.Carrier, Freight.Product, Freight.Invoice,
Freight.Weight, Freight.Price, Freight.Cost, Freight.Freight,
Freight.Reviewed, Freight.[Billed Customer]
HAVING (((Freight.[Billed Customer])=No))
ORDER BY Freight.Company, Freight.Invoice;

If you are unble to provide a means to narrow the query results just say so.
 
S

Steve Schapel

Carl,

With all due respect, I have already provided "a means to narrow the
query results". I know what the answer is, that's not the problem. It
is the correct answer, and I have already told you what it is. If what
I suggested doesn't work, it means there is something unusual or
unexpected going on, either with the data or with the way you are doing
something. All I have been trying to do is help you track down what
that unusual or unexpected thing might be. Seeing the SQL of the query
helps me to understand your approach. For example, it is useful for me
to know that it is a Totals query, which you forgot to mention before.
Anyway, my best guess at the moment is that somewhere in your data you
have one or more records with a 0 in the Price field. This would
certainly result in the problem you are experiencing. If this guess is
not correct, let me know, and we'll think again.
 
Top