Criteria based on the value in another field

[Ten Speed]

Hey guys, apologies in advance for a question which you may have answered many times, I have searched the net, my textbook and these forums but to no avail.

I have a table, 'tblStock'.

I have two fields within this table, 'Stock Level' and 'Minimum Stock'.

I want to run a query on this table, finding all records where the value in 'Stock Level' is LOWER than the value in 'Minimum Stock'.

How would I go about setting up this query? What criteria would I type? I have tried typing <"Minimum Stock" in the criteria for 'Stock Level' but it didn't work.

Thanks in advance... this will be a big help.

 

[Brianwarnock]

Did you type <"Minimum Stock" or <[Minimum Stock]

Brian

 

[FoFa]

Try <[Minimum Stock] (brackets instead of quotes

 

[Ten Speed]

Absolutely brilliant. So simple yet I could've spent ages trying to work that out.

Many thanks to both of you.

 

[marleyuk]

any chance with mine

i want a similar thing but the table im querying is brought in by the '*' because most of the fields will change depending on what info is in the table. The only 2 constant fields are time and seconds. I want to show how many of these changing fields are below 0.001. Any ideas??

 

[neileg]

I have a table, 'tblStock'.

I have two fields within this table, 'Stock Level' and 'Minimum Stock'.

I want to run a query on this table, finding all records where the value in 'Stock Level' is LOWER than the value in 'Minimum Stock'.

It is a very poor design to store the stock level in a field. For lots of reasons, you should hold the transactions in and out and calculate the stock level when you need it.

 

[Ten Speed]

It is a very poor design to store the stock level in a field. For lots of reasons, you should hold the transactions in and out and calculate the stock level when you need it.

Hi Neil,

Your input is much appreciated, I understand your point. Were I designing a system of any significance, I would heed your advice.

Fortunately this is an A-level project which just needs to demonstrate some basic skills with Access. As such, the database won't be 'used' and my current method will get me the marks no problem. (Furthermore, I am dealing with an abstract 'inventory' system which doesn't really deal with transactions per se.)

Thanks again

 

[neileg]

OK, I understand.