to display the maximum value in a field.you would press Totals button in query design Grid.its button on toolbar.when you press it it has group by option besides others in the design grid.Press the Drop down button in Totals row in design grid and select Max and it would display the Maximum Value.BUT IF YOU WANT TO DISPLAY THE MAXIMUM TWO VALUES IN A FIELD.THIS DOES NOT WORK. I MEAN THE MAXIMUM VALUE IN A FIELD AND THE SECOND LAST MAXIMUM VALUE IN A FIELD.WHAT OPTIONS ARE THERE IN ACCESS TO DO THIS. regards.
Why does the Totals Button not working here (1 Viewer)
- Thread starter trkl
- Start date
- Local time
- Tomorrow, 06:52
- Joined
- Jan 20, 2009
- Messages
- 12,853
Use the TOP predicate in the SQL.
http://office.microsoft.com/en-au/a...t-distinctrow-top-predicates-HA001231351.aspx
http://office.microsoft.com/en-au/a...t-distinctrow-top-predicates-HA001231351.aspx
Thanks.but there are ways to do it in MS Access 2010.i dont understand it because i dont know VBA and other Database Languages Yet.so plz tell me how to do it without any coding.Also Glaxom it would include all the Records.and by using other options,i can get top 5,top 25,Top 25%,100,All Records in a query.But to Get Only the Maximum VALUE and the next Maximum Value What would you do.plz help.thx for your input. Regards.
- Local time
- Tomorrow, 06:52
- Joined
- Jan 20, 2009
- Messages
- 12,853
It is time to start learning and SQL doesn't get much easier than this.
Create your query without using the Totals. Sort the field desecending.
Switch to SQL View and type TOP 2 immediately after the SELECT.
It is time to start learning and SQL doesn't get much easier than this.
Create your query without using the Totals. Sort the field desecending.
Switch to SQL View and type TOP 2 immediately after the SELECT.
Totally agree with Galaxiom.
But, this time, Access can do the job for you without using the SQL view.
After you Create your query without using the Totals. Sort the field desecending. as Galaxiom teach you, go to the query Property Sheet and type 2 in Top Values property.
CazB a similar problem comes.for example in the following Table whish is Items Table. Item Number Description Price Quantity Date Posted Condition Seller Code 1663 Metal Chairs $5.00 4 3/1/2012 Excellent ER 1683 Patio Set $60.00 1 2/26/2012 Excellent SE 2563 Loveseat $45.00 1 2/25/2012 Good SE 2593 Bar Stools $15.00 2 3/7/2012 Good HI 3923 End Tables $10.00 2 3/7/2012 Excellent MT 3953 Metal Cabinet $4.00 1 3/4/2012 Fair ER 4343 Computer Desk $25.00 1 3/2/2012 Good HI 5810 Bookcase $8.00 2 3/3/2012 Good ER 5930 Clothes Hamper $3.00 1 2/27/2012 Fair HI 6103 Twin Bed Frame $15.00 2 3/4/2012 Excellent HI 6270 Duvet $75.00 1 3/4/2012 Good MT 6350 Recliner $50.00 1 3/4/2012 Fair MT 6443 Kitchen Table $30.00 1 3/6/2012 Fair ER 7250 Coffee Table $10.00 1 3/7/2012 Excellent MT if i want to create a query which has the minimum i.e which displays a record which has the minimum field value in Date field.if you use Totals Button.and select Minimum then again its not showing the expected results.but something else.property sheets also does not work here.because if you bring property sheets.then it has only 4 opotions.there is no top values option there.and i think to solve this problem there should be minimum values option.plz help. Regards.
- Local time
- Tomorrow, 06:52
- Joined
- Jan 20, 2009
- Messages
- 12,853
It isn't actually a property per se but a design ribbon feature. It is the same one where the dropdown selects 25% etc. You can type in a number.
I have never used it because I know SQL.
TOP returns the first n records. Maximum or minimum depends on the direction the records are sorted on the field you want. Ascending or descending.
the problem is how to find the maximum and minimum balances in the above database. if we include balance field as a single field in design grid of query and hit the totals button.it would show maximum if we select maximum in the dropdown arrow of Totals Row in design grid.and would show minimum if we select minimum in dropdown of Totals in design grid.but we have to show maximum and minimum balances at the same time.how that can be achieved Regards.
