About Manish Batola Expertise ALL MICROSOFT ACCESS DESIGN AND Development QUESTIONS which occur in a Access Projects Lifecycle. Can offer constructive help on Designing Advanced MS-ACCESS Applications such CRM/FRM(Follow-up),Chemicals Indenting,Import/Export,MRP - Manufacturing Resource Planning, ERP - Enterprise Resource Planning, Automotive Management Systems, Business Intelligence for Oil & Gas Drilling Operations, CRM,KPI- Key Performance Indicators,HealthCare, Hospitality, Constructions, Loyalty Clubs, Call Centers, Travel & Tourism, Educational Institutions and many more being added every year.
Experience 25 yrs in the Software industry with 19yrs completely dedicated on more than 250 Various Access Projects from different Industry Sector[s]. I'm using Access ever since it was born.
Organizations Worked in various multinationals catering from Aerospace, Oil & Gas etc.
Using MS Access - Display best result according to parameter input in query
Expert: Manish Batola - 4/9/2009
Question Version: Access 2003
Topic: Query Design
Hi,
I am trying to make a query that prompts the user for input then accordingly the correct results are shown on a form.
The query's objective is to highlight/show what is the best vehicle for a particular job. E.g. Large payload = large truck, small payload = small truck and NOT small payload = large truck.
The query is made up from a table called vehicles. Within this table there are 5 field headings. They are: Vehicle Reg, Make, Max Payload in KG's, & Max No. of Pallets.
There are a total of 3 trucks in the table. All of which have different payload and pallet capacities.
I would like the query to prompt the user with 2 parameters. 'What is the total KG's?' and 'How many pallets'. According to these variables, the query should recommend the most suitable truck.
Can you help me with this?
Answer Hi,
The query will display results but will they really be best all depends on the data in your tables,cause this aint Artificial Intelligence like Prolog.
In your query you need to add a Parameter in the Max Payload in KG's field and another in the Max No of Pallets field. If you need a total then you need to Group by Make and Sum by Max No of Pallets field.
