Solved Access criteria (1 Viewer)

[teemax]

Hi, I am having issues with the criteria for the query. The instructions are as follows
Display all fields for all products with a selling price greater than $10.00 and the number on hand is fewer than 10

Sort the query in ascending order according to the Cost field.

Save this query as qry_Inventory_L10
However when i save the query and open the data. I am not getting the correct information
Please see snipping below:

 

[plog]

Use the right datatype for your data.

[On Hand] is a text field in your database, it should be a numeric field. Comparisons on text are different than comparisons on numbers. When you use > or < on text it does it character by character. That means:

"10" < "3" because "1" is less than "3"

As numbers, it does it value by value:

10 > 3 because it looks at the whole thing numerically.

So, you need to make [On Hand] a numeric datatype if you want to do numeric comparsions.

While you're changing your table, I suggest you also eliminate spaces in field names (On Hand = OnHand, Selling Price = SellinPrice). It'll make coding and querying easier.

 

[teemax]

Thank you, One more question as it relates to the same data but a different query. I am unsure of how to do this question .
Create a query that calculates and displays the average items On Hand in inventory by Supplier Code. Select the following fields for the query; Supplier Code, On Hand.

Sort the query in ascending order according to the Supplier Code field.

Save this query as qry_Avg_on_Hand

 

[Gasman]

Use the AVG option and Group by Supplier Code

Hope you get an A+ for this homework.

 

[plog]

When you need to group data together that's called an aggregate query. One of the functions available when you do that is AVG. Give this link a read:

Sum data by using a query - Microsoft Support

Learn how to use an aggregate function in Access to sum the data in a query result set.

support.microsoft.com

Then try it on your data, if you have any issues post back here the SQL you tried and what the issue is.

 

[gemma-the-husky]

What would "average items on hand" mean with reference to inventory? It seems a curious measure.

 

[teemax]

What would "average items on hand" mean with reference to inventory? It seems a curious measure.

i am still trying to understand the question my self, despite reading how to sum data by query. But initially the question ask me to create a query showing the inventory value by (on hand * cost) which is displayed in the pic below. The second question is asking for me to create a query that calculates and displays the average items On Hand in inventory by Supplier Code

 

[teemax]

What would "average items on hand" mean with reference to inventory? It seems a curious measure.

But the on hand is basically the amount of items they have in stock, So i think the question is asking me to average the amount of item that is in stock by the supplier code. I finally got it . Thank you guys