David has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
Access query
how do you enter criteria that relates on field to another (in the same or different table)?
eg you have a table called 'product' with the following fields:
-prod id (primary key)
-prod name
-qty available
-minimum order
and you want to run a query that shows all records where quantity available > minimum order
Thanks
RE: access query
Hi David,
create the query with the fields you wish to show as usual. Then to a blank grid column at the end type into where the field name normally goes Expr1:([qty available]-[minimum order]) so that you are subtracting the one from the other. Then in the criterea cell beneath it put simply >0 Also, uncheck the show box. Press run and it should work. Thinking about what you may be trying to achieve if you actually need greater or equal the the criteria will be >=0
This should work.
Regards, Mark (delegate)
RE: access query
Dear David
Thank you for attending Access Intermediate!! I hope you enjoyed the course and benefited from it.
Download all the fields that you require in Query desgin view.
In the Criteria box for Qty_Available please enter the following criteria:
>[Min_Order]
You'll get your result.
Please remeber that any field name gets put inside the square brackets!!
I hope this has answered your query.
If this has answered your query then I would request you to please mark the question as resolved!! If not and you have a specific question related to this then please post it as a new question and we should be able to provide you the solution for it!!
Kindest Regards
Rajeev Rawat
MOS Master Instructor 2000 and 2003
MCAS Master Instructor 2007
