Filter Report Data

  • Thread starter Thread starter Kathy
  • Start date Start date
K

Kathy

The resource for one of my reports is a table. I want to
only view specific data ie. show me all records where the
value of one field is greater than zero. What is the best
way to achieve this? How can I filter the report data? I
need step by step instructions on filtering to achieve
this goal.
 
Thanks, I tried that and was not able to get the data I
wanted. Perhaps I am not setting the criteria properly.
I have two fields called Order Qty and Total Shipped Qty.
I want to see only those records that have a Total Shipped
Qty that is less than the Order Quantity. Can you explain
how to set the criteria in the query?

Thanks!
 
Thanks, I tried that and was not able to get the data I
wanted. Perhaps I am not setting the criteria properly.
I have two fields called Order Qty and Total Shipped Qty.
I want to see only those records that have a Total Shipped
Qty that is less than the Order Quantity. Can you explain
how to set the criteria in the query?

Thanks!
Click to expand...

When you have a problem like this, it's best to include the actual
criteria you used. Saying something doesn't work is of no help to us
unless we see what it is you have done.

Here is an SQL.

SELECT tblBasicData.*
FROM tblBasicData
WHERE (((tblBasicData.TotalShippedQty)<[OrderQty]));

Change tblBasicData to your table name.
 
Thank you...I am new to Access and self taught.
Therefore, this is what I have done...

I designed a table called Work Order Entry and then
designed a query from that table. If I am looking at the
query in Design view, where do I enter your suggested SQL
statement?

SELECT tblWorkOrderEntry.*
FROM tblWorkOrderEntry
WHERE (((tblWorkOrderEntry.TotalShippedQty)<[OrderQty]));
-----Original Message-----
Thanks, I tried that and was not able to get the data I
wanted. Perhaps I am not setting the criteria properly.
I have two fields called Order Qty and Total Shipped Qty.
I want to see only those records that have a Total Shipped
Qty that is less than the Order Quantity. Can you explain
how to set the criteria in the query?

Thanks!
data?
I
Click to expand...

When you have a problem like this, it's best to include the actual
criteria you used. Saying something doesn't work is of no help to us
unless we see what it is you have done.

Here is an SQL.

SELECT tblBasicData.*
FROM tblBasicData
WHERE (((tblBasicData.TotalShippedQty)<[OrderQty]));

Change tblBasicData to your table name.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Thank you...I am new to Access and self taught.
Therefore, this is what I have done...

I designed a table called Work Order Entry and then
designed a query from that table. If I am looking at the
query in Design view, where do I enter your suggested SQL
statement?

SELECT tblWorkOrderEntry.*
FROM tblWorkOrderEntry
WHERE (((tblWorkOrderEntry.TotalShippedQty)<[OrderQty]));
-----Original Message-----
Thanks, I tried that and was not able to get the data I
wanted. Perhaps I am not setting the criteria properly.
I have two fields called Order Qty and Total Shipped Qty.
I want to see only those records that have a Total Shipped
Qty that is less than the Order Quantity. Can you explain
how to set the criteria in the query?

Thanks!

-----Original Message-----
Base your report off a query which is based on your table

Jim
-----Original Message-----
The resource for one of my reports is a table. I want
to
only view specific data ie. show me all records where
the
value of one field is greater than zero. What is the
best
way to achieve this? How can I filter the report data?
I
need step by step instructions on filtering to achieve
this goal.
.

.
Click to expand...

When you have a problem like this, it's best to include the actual
criteria you used. Saying something doesn't work is of no help to us
unless we see what it is you have done.

Here is an SQL.

SELECT tblBasicData.*
FROM tblBasicData
WHERE (((tblBasicData.TotalShippedQty)<[OrderQty]));

Change tblBasicData to your table name.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...

If you already have a query written, you can jump directly to #2.

1) Click on Query + New.
Select Design View from the new query dialog.
Add your table to the query from the Show Table dialog.

2) Click on the arrow on the View Tool Button (usually the left most
button).
The SQL window will open with some existing SQL

SELECT
FROM tblWorkOrderEntry;

already written.

Change that SQL to

SELECT tblWorkOrderEntry.*
FROM tblWorkOrderEntry
WHERE (((tblWorkOrderEntry.TotalShippedQty)<[OrderQty]));

Remember to move the semicolon to the end of the SQL code.

Click on the Bang tool button (!) and run the query.
If all is well save the query.
 
Still no luck...
1) Click on Query + New. DID THIS FROM SCRATCH
Click to expand...
Select Design View from the new query dialog.
Add your table to the query from the Show Table dialog.

2) Click on the arrow on the View Tool Button (usually the left most
button).
The SQL window will open with some existing SQL

SELECT
FROM tblWorkOrderEntry;
Click to expand...

ACTUAL SQL:
SELECT
FROM [WORK ORDER ENTRY];
already written.

Change that SQL to

SELECT tblWorkOrderEntry.*
FROM tblWorkOrderEntry
WHERE (((tblWorkOrderEntry.TotalShippedQty)<[OrderQty]));
Click to expand...

CHANGED SQL TO:
SELECT tblWork Order Entry.*
FROM tblWork Order Entry
WHERE (((tblWork Order Entry.Total Qty Shipped)<[Order
Qty]));

RESULTS IN THE FOLLOWING ERROR:
Syntax Error in ORDER BY clause

Next Steps????
 
Still no luck...
1) Click on Query + New. DID THIS FROM SCRATCH
Click to expand...
Select Design View from the new query dialog.
Add your table to the query from the Show Table dialog.

2) Click on the arrow on the View Tool Button (usually the left most
button).
The SQL window will open with some existing SQL

SELECT
FROM tblWorkOrderEntry;
Click to expand...

ACTUAL SQL:
SELECT
FROM [WORK ORDER ENTRY];
already written.

Change that SQL to

SELECT tblWorkOrderEntry.*
FROM tblWorkOrderEntry
WHERE (((tblWorkOrderEntry.TotalShippedQty)<[OrderQty]));
Click to expand...

CHANGED SQL TO:
SELECT tblWork Order Entry.*
FROM tblWork Order Entry
WHERE (((tblWork Order Entry.Total Qty Shipped)<[Order
Qty]));

RESULTS IN THE FOLLOWING ERROR:
Syntax Error in ORDER BY clause

Next Steps????
Click to expand...

There is no ORDER BY in your SQL, but you do have a table name and
field names with spaces in it.
If you have a Field or a Table name with spaces you MUST enclose it
within brackets. You did it with one of the Field names but not with
the table name or other field name.

SELECT [tblWork Order Entry].*
FROM [tblWork Order Entry]
WHERE ((([tblWork Order Entry].[Total Qty Shipped])<[Order
Qty]));

The above is the reason most experienced user use object names like
tblWorkOrderEntry or tbl_Work_Order_Entry.
Both are easy enough to read, but are considered, by Access, to be one
word, and would work without brackets.

If you notice, my previous example was:
SELECT tblBasicData.*
FROM tblBasicData
WHERE (((tblBasicData.TotalShippedQty)<[OrderQty]));

No brackets needed because the table and field names were one word (no
spaces).
 
Back
Top