Subquery comparing two fields

[jdcollins]

I am trying to complete a query with:



-Product Name
-Quantity on Hand
-Reorder Level

I only want the query to return the product name when the Quantity Level is below the Reorder Level.

Should be pretty simple, I think, but I am completely new to subqueries.

Any help would be great!

[pbaldy]

Not sure what you mean by subquery, as you don't need one.

SELECT *
FROM TableName
WHERE QuantityOnHand < ReorderLevel

[jdcollins]

Sorry, just confused the terminology.

I tried that but got a syntax error (actually said it was a syntax within the subquery).

SELECT *
FROM [Product Table]
WHERE [Quantity on Hand] < [Reorder Level]

[pbaldy]

There should be no error if those are fields in the table. Can you post the db, or a representative sample?

[jdcollins]

I've attached the file... the query is called "low product query"

[pbaldy]

I get no error adding the criteria:

SELECT [Product Table].[Product Name], [Product Table].[Quantity on Hand], [Product Table].[Reorder Level]
FROM [Product Table] INNER JOIN [Supplier Table] ON [Product Table].[Supplier Number] = [Supplier Table].[Supplier Number]
WHERE [Product Table].[Quantity on Hand]<[Reorder Level]

The spaces in your names are not worth the trouble in the long run.

[jdcollins]

I am still getting a syntax error...am I supposed to be adding that to the criteria under Product Name? I've attached a screenshot... I'm obviously doing something wrong!

I agree about the spaces, but they weren't my idea (this is actually an assignment template I am working off of).

Thanks for your patience!

[jdcollins]

On a whim, I just tried adding:

[Quantity on Hand]<[Reorder Level] to the criteria field and that worked fine.... maybe this is an improvement to Access 2010???

[pbaldy]

The SQL I posted was the entire query. The criteria would simply have looked like:

<[Reorder Level]

In the Quantity on Hand field.