Can't figure this out...

[B_Lyles]

I created that contains these fields: Ball Material, Ball Size, Ball
Tolerance, Quantity, Safety Stock, Location.
I want the query to return all balls whose Quantity is less than the Safety
Stock. I thought the solution would be to use <SafetyStock under the Quantity
field but it's returning ALL of the balls and just giving them a Quantity <
than SafetyStock. Any ideas?

 

[Sprinks]

Query statement should be:

SELECT YourTbl.[Ball Material], YourTbl.[Ball Size], YourTbl.[Ball
Tolerance], YourTbl.[Quantity], YourTbl.[Safety Stock], YourTbl.[Location]
WHERE (YourTbl.[Quantity] < YourTbl.[Safety Stock]);

Hope that helps.
Sprinks

 

[Madhivanan]

Select * from Table where Quantity<SafetyStock

Madhivanan

 

[B_Lyles]

Under criteria or table?

 

[Sprinks]

If you're doing the query in Query By Example view (the default), select all
fields that you wish to return, and in the Criteria row of the Quantity field
type:

< [Safety Stock]

If you're interested, you can see the SQL version of the query by switching
to SQL view (View, SQL).

Hope that helps.
Sprinks

 

[B_Lyles]

I've been trying that and instead of returning only the balls that are
greater than safety stock it will return all the balls and in the quantity it
will show the highest quantity for all the balls. If there's only one ball
less than safety stock and its value is 200, rather than just finding that
ball it returns all of them and assigns 200 to all of them. I'm stumped.

If you're doing the query in Query By Example view (the default), select all
fields that you wish to return, and in the Criteria row of the Quantity field
type:

< [Safety Stock]

If you're interested, you can see the SQL version of the query by switching
to SQL view (View, SQL).

Hope that helps.
Sprinks

Under criteria or table?

 

[Sprinks]

Sounds strange. You may want to try a Compact and Repair, but go into the
SQL view of the query, and cut and post the SQL.

Sprinks

I've been trying that and instead of returning only the balls that are
greater than safety stock it will return all the balls and in the quantity it
will show the highest quantity for all the balls. If there's only one ball
less than safety stock and its value is 200, rather than just finding that
ball it returns all of them and assigns 200 to all of them. I'm stumped.

If you're doing the query in Query By Example view (the default), select all
fields that you wish to return, and in the Criteria row of the Quantity field
type:

< [Safety Stock]

If you're interested, you can see the SQL version of the query by switching
to SQL view (View, SQL).

Hope that helps.
Sprinks

Under criteria or table?

:


Select * from Table where Quantity<SafetyStock

Madhivanan

 

[B_Lyles]

SELECT tblSafetyStock.[Ball Material], tblSafetyStock.[Ball Grade],
tblSafetyStock.[Ball Size], tblSafetyStock.[Ball Tolerance],
tblSafetyStock.[Safety Stock], [Material Traceability Table].Quantity,
tblSafetyStock.Location
FROM [Material Traceability Table], tblSafetyStock
WHERE ((([Material Traceability Table].Quantity)<[tblSafetyStock]![Safety
Stock]));

Sounds strange. You may want to try a Compact and Repair, but go into the
SQL view of the query, and cut and post the SQL.

Sprinks

I've been trying that and instead of returning only the balls that are
greater than safety stock it will return all the balls and in the quantity it
will show the highest quantity for all the balls. If there's only one ball
less than safety stock and its value is 200, rather than just finding that
ball it returns all of them and assigns 200 to all of them. I'm stumped.

If you're doing the query in Query By Example view (the default), select all
fields that you wish to return, and in the Criteria row of the Quantity field
type:

< [Safety Stock]

If you're interested, you can see the SQL version of the query by switching
to SQL view (View, SQL).

Hope that helps.
Sprinks

:

Under criteria or table?

:


Select * from Table where Quantity<SafetyStock

Madhivanan

 

[Sprinks]

Aha!

It is not working because you have no join specified that matches a ball in
one table with the same ball in the other. For example, if you had a BallID
field in each that uniquely identified each product, the SQL would be:

SELECT tblSafetyStock.[Ball Material], tblSafetyStock.[Ball Grade],
tblSafetyStock.[Ball Size], tblSafetyStock.[Ball Tolerance],
tblSafetyStock.[Safety Stock], [Material Traceability Table].Quantity
FROM [Material Traceability Table] INNER JOIN tblSafetyStock ON [Material
Traceability Table].BallID = tblSafetyStock.BallID
WHERE ((([Material Traceability Table].Quantity)<[tblSafetyStock]![Safety
Stock]));

In query design view, drag the matching field from one table to the other to
establish the join.

Hope that helps.
Sprinks

SELECT tblSafetyStock.[Ball Material], tblSafetyStock.[Ball Grade],
tblSafetyStock.[Ball Size], tblSafetyStock.[Ball Tolerance],
tblSafetyStock.[Safety Stock], [Material Traceability Table].Quantity,
tblSafetyStock.Location
FROM [Material Traceability Table], tblSafetyStock
WHERE ((([Material Traceability Table].Quantity)<[tblSafetyStock]![Safety
Stock]));

Sounds strange. You may want to try a Compact and Repair, but go into the
SQL view of the query, and cut and post the SQL.

Sprinks

I've been trying that and instead of returning only the balls that are
greater than safety stock it will return all the balls and in the quantity it
will show the highest quantity for all the balls. If there's only one ball
less than safety stock and its value is 200, rather than just finding that
ball it returns all of them and assigns 200 to all of them. I'm stumped.

:

If you're doing the query in Query By Example view (the default), select all
fields that you wish to return, and in the Criteria row of the Quantity field
type:

< [Safety Stock]

If you're interested, you can see the SQL version of the query by switching
to SQL view (View, SQL).

Hope that helps.
Sprinks

:

Under criteria or table?

:


Select * from Table where Quantity<SafetyStock

Madhivanan

 

[B_Lyles]

Thanks a lot. I finally feel like I'm getting somewhere. 1 more thing. I
never ran a query through SQL and when I try to run it I'm having a problem
saving it. It says I'm entering an invalid file name.

SELECT tblSafetyStock.[Ball Material], tblSafetyStock.[Ball Grade],
tblSafetyStock.[Ball Size], tblSafetyStock.[Ball Tolerance],
tblSafetyStock.[Safety Stock], [Material Traceability Table].Quantity,
tblSafetyStock.Location
FROM [Material Traceability Table], tblSafetyStock
WHERE ((([Material Traceability Table].Quantity)<[tblSafetyStock]![Safety
Stock]));

Sounds strange. You may want to try a Compact and Repair, but go into the
SQL view of the query, and cut and post the SQL.

Sprinks

I've been trying that and instead of returning only the balls that are
greater than safety stock it will return all the balls and in the quantity it
will show the highest quantity for all the balls. If there's only one ball
less than safety stock and its value is 200, rather than just finding that
ball it returns all of them and assigns 200 to all of them. I'm stumped.

:

If you're doing the query in Query By Example view (the default), select all
fields that you wish to return, and in the Criteria row of the Quantity field
type:

< [Safety Stock]

If you're interested, you can see the SQL version of the query by switching
to SQL view (View, SQL).

Hope that helps.
Sprinks

:

Under criteria or table?

:


Select * from Table where Quantity<SafetyStock

Madhivanan

 

[B_Lyles]

Thanks a lot. I finally feel like I'm getting somewhere. 1 more problem. I
never ran a query through SQL and I'm having a problem saving it. It keeps
saying I'm entering an invalid file name.

Aha!

It is not working because you have no join specified that matches a ball in
one table with the same ball in the other. For example, if you had a BallID
field in each that uniquely identified each product, the SQL would be:

SELECT tblSafetyStock.[Ball Material], tblSafetyStock.[Ball Grade],
tblSafetyStock.[Ball Size], tblSafetyStock.[Ball Tolerance],
tblSafetyStock.[Safety Stock], [Material Traceability Table].Quantity
FROM [Material Traceability Table] INNER JOIN tblSafetyStock ON [Material
Traceability Table].BallID = tblSafetyStock.BallID
WHERE ((([Material Traceability Table].Quantity)<[tblSafetyStock]![Safety
Stock]));

In query design view, drag the matching field from one table to the other to
establish the join.

Hope that helps.
Sprinks

SELECT tblSafetyStock.[Ball Material], tblSafetyStock.[Ball Grade],
tblSafetyStock.[Ball Size], tblSafetyStock.[Ball Tolerance],
tblSafetyStock.[Safety Stock], [Material Traceability Table].Quantity,
tblSafetyStock.Location
FROM [Material Traceability Table], tblSafetyStock
WHERE ((([Material Traceability Table].Quantity)<[tblSafetyStock]![Safety
Stock]));

Sounds strange. You may want to try a Compact and Repair, but go into the
SQL view of the query, and cut and post the SQL.

Sprinks

:

I've been trying that and instead of returning only the balls that are
greater than safety stock it will return all the balls and in the quantity it
will show the highest quantity for all the balls. If there's only one ball
less than safety stock and its value is 200, rather than just finding that
ball it returns all of them and assigns 200 to all of them. I'm stumped.

:

If you're doing the query in Query By Example view (the default), select all
fields that you wish to return, and in the Criteria row of the Quantity field
type:

< [Safety Stock]

If you're interested, you can see the SQL version of the query by switching
to SQL view (View, SQL).

Hope that helps.
Sprinks

:

Under criteria or table?

:


Select * from Table where Quantity<SafetyStock

Madhivanan

 

[Sprinks]

Nothing magical about SQL--it's just another view of the same query that's
represented graphically in Query By Design view. Saving the definition is
the same in either case. What filename are you trying to save it under?
Define exactly what you mean by saving it also--it sounds like you're trying
to save it after you've executed it. If you are, I don't understand the
purpose of it, since the query can be run at anytime to return the recordset,
so describe what it is you're trying to do.

Sprinks

Thanks a lot. I finally feel like I'm getting somewhere. 1 more thing. I
never ran a query through SQL and when I try to run it I'm having a problem
saving it. It says I'm entering an invalid file name.

SELECT tblSafetyStock.[Ball Material], tblSafetyStock.[Ball Grade],
tblSafetyStock.[Ball Size], tblSafetyStock.[Ball Tolerance],
tblSafetyStock.[Safety Stock], [Material Traceability Table].Quantity,
tblSafetyStock.Location
FROM [Material Traceability Table], tblSafetyStock
WHERE ((([Material Traceability Table].Quantity)<[tblSafetyStock]![Safety
Stock]));

Sounds strange. You may want to try a Compact and Repair, but go into the
SQL view of the query, and cut and post the SQL.

Sprinks

:

I've been trying that and instead of returning only the balls that are
greater than safety stock it will return all the balls and in the quantity it
will show the highest quantity for all the balls. If there's only one ball
less than safety stock and its value is 200, rather than just finding that
ball it returns all of them and assigns 200 to all of them. I'm stumped.

:

If you're doing the query in Query By Example view (the default), select all
fields that you wish to return, and in the Criteria row of the Quantity field
type:

< [Safety Stock]

If you're interested, you can see the SQL version of the query by switching
to SQL view (View, SQL).

Hope that helps.
Sprinks

:

Under criteria or table?

:


Select * from Table where Quantity<SafetyStock

Madhivanan

 

[B_Lyles]

Maybe I'm using it wrong. I open the query in design view then I go to
Query->SQL Specif->Pass Through and it pulls up the SQL statement. Is there
another way to use SQL Query?

Nothing magical about SQL--it's just another view of the same query that's
represented graphically in Query By Design view. Saving the definition is
the same in either case. What filename are you trying to save it under?
Define exactly what you mean by saving it also--it sounds like you're trying
to save it after you've executed it. If you are, I don't understand the
purpose of it, since the query can be run at anytime to return the recordset,
so describe what it is you're trying to do.

Sprinks

Thanks a lot. I finally feel like I'm getting somewhere. 1 more thing. I
never ran a query through SQL and when I try to run it I'm having a problem
saving it. It says I'm entering an invalid file name.

SELECT tblSafetyStock.[Ball Material], tblSafetyStock.[Ball Grade],
tblSafetyStock.[Ball Size], tblSafetyStock.[Ball Tolerance],
tblSafetyStock.[Safety Stock], [Material Traceability Table].Quantity,
tblSafetyStock.Location
FROM [Material Traceability Table], tblSafetyStock
WHERE ((([Material Traceability Table].Quantity)<[tblSafetyStock]![Safety
Stock]));


:

Sounds strange. You may want to try a Compact and Repair, but go into the
SQL view of the query, and cut and post the SQL.

Sprinks

:

I've been trying that and instead of returning only the balls that are
greater than safety stock it will return all the balls and in the quantity it
will show the highest quantity for all the balls. If there's only one ball
less than safety stock and its value is 200, rather than just finding that
ball it returns all of them and assigns 200 to all of them. I'm stumped.

:

If you're doing the query in Query By Example view (the default), select all
fields that you wish to return, and in the Criteria row of the Quantity field
type:

< [Safety Stock]

If you're interested, you can see the SQL version of the query by switching
to SQL view (View, SQL).

Hope that helps.
Sprinks

:

Under criteria or table?

:


Select * from Table where Quantity<SafetyStock

Madhivanan

 

[B_Lyles]

Maybe I'm using SQL wrong. I open the Query in design view. Then I go to
Query->SQL Specific->Pass-Through. Is there another way use SQL?

Nothing magical about SQL--it's just another view of the same query that's
represented graphically in Query By Design view. Saving the definition is
the same in either case. What filename are you trying to save it under?
Define exactly what you mean by saving it also--it sounds like you're trying
to save it after you've executed it. If you are, I don't understand the
purpose of it, since the query can be run at anytime to return the recordset,
so describe what it is you're trying to do.

Sprinks

Thanks a lot. I finally feel like I'm getting somewhere. 1 more thing. I
never ran a query through SQL and when I try to run it I'm having a problem
saving it. It says I'm entering an invalid file name.

SELECT tblSafetyStock.[Ball Material], tblSafetyStock.[Ball Grade],
tblSafetyStock.[Ball Size], tblSafetyStock.[Ball Tolerance],
tblSafetyStock.[Safety Stock], [Material Traceability Table].Quantity,
tblSafetyStock.Location
FROM [Material Traceability Table], tblSafetyStock
WHERE ((([Material Traceability Table].Quantity)<[tblSafetyStock]![Safety
Stock]));


:

Sounds strange. You may want to try a Compact and Repair, but go into the
SQL view of the query, and cut and post the SQL.

Sprinks

:

I've been trying that and instead of returning only the balls that are
greater than safety stock it will return all the balls and in the quantity it
will show the highest quantity for all the balls. If there's only one ball
less than safety stock and its value is 200, rather than just finding that
ball it returns all of them and assigns 200 to all of them. I'm stumped.

:

If you're doing the query in Query By Example view (the default), select all
fields that you wish to return, and in the Criteria row of the Quantity field
type:

< [Safety Stock]

If you're interested, you can see the SQL version of the query by switching
to SQL view (View, SQL).

Hope that helps.
Sprinks

:

Under criteria or table?

:


Select * from Table where Quantity<SafetyStock

Madhivanan

 

[Rick Brandt]

Maybe I'm using SQL wrong. I open the Query in design view. Then I go
to Query->SQL Specific->Pass-Through. Is there another way use SQL?

A Pass-Through is a completely different animal where you send (or pass
through) the SQL untouched by Jet to another database engine via an ODBC
connection. The database engine at the other end of the ODBC connection
then processes the SQL statement and returns the results. Is that your
intent?

If you just want the SQL statement for a query just go to SQL view without
changing the type to Pass-Through.

 

[B_Lyles]

Duh, you can just switch to SQL view. Sorry.

Aha!

It is not working because you have no join specified that matches a ball in
one table with the same ball in the other. For example, if you had a BallID
field in each that uniquely identified each product, the SQL would be:

SELECT tblSafetyStock.[Ball Material], tblSafetyStock.[Ball Grade],
tblSafetyStock.[Ball Size], tblSafetyStock.[Ball Tolerance],
tblSafetyStock.[Safety Stock], [Material Traceability Table].Quantity
FROM [Material Traceability Table] INNER JOIN tblSafetyStock ON [Material
Traceability Table].BallID = tblSafetyStock.BallID
WHERE ((([Material Traceability Table].Quantity)<[tblSafetyStock]![Safety
Stock]));

In query design view, drag the matching field from one table to the other to
establish the join.

Hope that helps.
Sprinks

SELECT tblSafetyStock.[Ball Material], tblSafetyStock.[Ball Grade],
tblSafetyStock.[Ball Size], tblSafetyStock.[Ball Tolerance],
tblSafetyStock.[Safety Stock], [Material Traceability Table].Quantity,
tblSafetyStock.Location
FROM [Material Traceability Table], tblSafetyStock
WHERE ((([Material Traceability Table].Quantity)<[tblSafetyStock]![Safety
Stock]));

Sounds strange. You may want to try a Compact and Repair, but go into the
SQL view of the query, and cut and post the SQL.

Sprinks

:

I've been trying that and instead of returning only the balls that are
greater than safety stock it will return all the balls and in the quantity it
will show the highest quantity for all the balls. If there's only one ball
less than safety stock and its value is 200, rather than just finding that
ball it returns all of them and assigns 200 to all of them. I'm stumped.

:

If you're doing the query in Query By Example view (the default), select all
fields that you wish to return, and in the Criteria row of the Quantity field
type:

< [Safety Stock]

If you're interested, you can see the SQL version of the query by switching
to SQL view (View, SQL).

Hope that helps.
Sprinks

:

Under criteria or table?

:


Select * from Table where Quantity<SafetyStock

Madhivanan