What Calculation to use?

[WackoWOlf]

What would I use for a formuls to find all product that are in stock.
like I have a price for all products that i stock, but some i don't have in stock. How would I query for the ones that I have in stock?
In my table I have it as UnitsOnHand, I have it as how many, like 1 or 3 or 4 etc. And also a field for Paper or Metal.
How would I query just for paper and not metal?
I am also going to need a query for the lowest price and the highest price.
Please understand I am New and I have allready search through all the post and can't seem to find anything close to what I am trying to do.
Any and all help would be great

Thank You
WackoWolf

 

[alastair69]

The solution to the paper or metal:

Creat a query with all records you need (you can add to this later)
In the field were your Paper or metal is stored type the following like "Paper" to show all the records that are paper and the same funtion for metal (like "Metal")

Solution to "In Stock" you could use the same as above but (is not null) or to show out of stock (is Null) on the field were use record the stock.


Let me know if this works.

Alastair

 

[WackoWOlf]

I try what you said for "Paper"
I made another field for Paper
but the query return all -1 in that field
I want it to return just what I have for "Paper" and the same when I do it for "Metal"
I didn't use the " " when I type it in the field am I suppose to?

 

[alastair69]

I try what you said for "Paper"
I made another field for Paper
but the query return all -1 in that field

Paper and metal must be set up as yes/no fields (0 = No, -1 = Yes)

Try following Like "0" for not paper
Like "-1" for paper yes.

 

[WackoWOlf]

do I have to change the field in my table to 0 and -1? or do I keep it as "Yes" and "No"?


Not sure how to do the query for it, I am lost

 

[alastair69]

do I have to change the field in my table to 0 and -1?

Not should how to do the query for it, I am lost

If you can post your Database i will have a look at it for you.

 

[WackoWOlf]

ProductNumber ProductName AmtInStock UnitPrice Paper
1489 QQQQQQ 1 $16.95 No
1534 WWWWW 3 $5.99 Yes
2120 AAAAAA 4 $7.50 Yes
2291 ZZZZZZZ 0 $5.50 No

This is how I got the table set up
Lost as to how to do the query

Thank You for the help you are giveing me

WackoWolf

 

[neileg]

Some pointers:

1. Yes is stored by Access as -1 and No as 0. So you can use these interchangeably.
2. I assume that an item is either paper or metal but never both. It would be better to use one field for the material and store "paper" or "metal" in that field. This way, if you ever want to add "plastic" to your materials, you don't have to redesign the whole database.
3. It's not a good idea to store the quantity on hand. It is better to calculate this from the transactions in and out of stock.
4. I don't think alistair69 is correct in his suggestions about null values. Your quantity on hand will only be null if it has never had a value in it. Otherwise it's likely that the value will be zero rather than null. Confusingly, Access will show a numeric field with a null value as zero. There is a function, Nz() which converts nulls to zero (or any other value) which will enable you to handle a mixture of nulls and zeros.

And finally, inventory is not a trivial application. Many beginners start off with inventory, but it can get messy, very quickly. Do a search in here about inventory or stock to see what I mean.

 

[WackoWOlf]

I will do a search now

 

[WackoWOlf]

1. To find all products that are Paper in your query all you have to do is right click on the field that is paper and click total, then take out the check from the check box, and then in criteria type yes. This will give you a query for all the products that are paper. You can do the same for metal.

2. For amount on hand all that is to be done is in the field for UnitsOnHand right click on the field and click total and then take out the check in the box and in criteria type whatever number you want like 3 or 2 or 4, then click run and you will get your query.

I finally figure this out after some long hours of trial and error.

As far as finally the lowest price and the max price, you right click on the price field and click total and then in the menu next to the greek symbol you would put a 1, then in the sort in the price field make it either descending or acending and then run your query and you will get your query for either the min or max whicever it is that you want.
Hopefully this will be of some help to anyone that is new and wants to do something of this sort of query.

Thank You
WackoWolf

 

[neileg]

I'm pleased you're making progress. Some more pointers:

1. To find all products that are Paper in your query all you have to do is right click on the field that is paper and click total, then take out the check from the check box, and then in criteria type yes. This will give you a query for all the products that are paper. You can do the same for metal.

Not sure why you need a totals query, here, unless it's the number of products you want. I still think one field to identify the material is better than the checkbox fields you have.

2. For amount on hand all that is to be done is in the field for UnitsOnHand right click on the field and click total and then take out the check in the box and in criteria type whatever number you want like 3 or 2 or 4, then click run and you will get your query.

This will give you the total for all materials where you have the specified number on hand. I puzzled as to why you would find this information useful!

I finally figure this out after some long hours of trial and error.

That is a recognised technique with Access!

As far as finally the lowest price and the max price, you right click on the price field and click total and then in the menu next to the greek symbol you would put a 1, then in the sort in the price field make it either descending or acending and then run your query and you will get your query for either the min or max whicever it is that you want.

It would be easier to use the Max or Min options in the totals query. This way you don't have to keep sorting your data. The First or Last options tend to produce unreliable results.

Hopefully this will be of some help to anyone that is new and wants to do something of this sort of query.

Keep sharing, that way we all learn.