By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Counting records in Access

P: 1
I am a teacher and have created a database to store students marks for coursework. The table that actually stores marks is called tbl_marks and contains 3 fields (studentid, taskid and mark) I need to be able to find all students who have got 3 marks of 0. I have tried to use the Dcount function in a query but it finds all the 0 marks in the whole table.

Can anyone help PLEASE!!!!!!!!!!!!!!!!!!
Dec 21 '07 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,356
I quoted a paragraph from the following link. If you have any questions feel free to post back. Hope this helps.

"How to Group Records and Create Sums and Other Totals
The following steps show you how to create a totals query to retrieve summary information about orders from the Northwind sample database.
1. Start Microsoft Access.
2. Open the Northwind.mdb sample database.
3. On the View menu, point to Database Objects, and then click Queries.
4. In the Database window, double-click Create Query in Design View.
5. In the Show Table dialog box, click to select Orders, and then click Add.
6. Click Close to close the Show Table dialog box.
7. In the Orders table field list in the top half of the query design window, double-click to add the following fields: ShipCountry, Freight.
8. On the View menu, click Totals. Note that a row named Total is added to the query design grid.
9. Click in the Total row cell under the Freight column, click the arrow that appears, and then click Sum.
10. On the File menu, click Save. Type qryFreightByCountry for the name of the query.
11. On the Query menu, click Run to view the results of the query. Note that the query returns the total freight for each country in the Orders table.
Dec 21 '07 #2

Expert 100+
P: 254
Without getting too complex with the query - this will display all students with a mark of 0 but will put the highest counts at the top.

Put together a query using tbl_marks, select studentid and select mark twice. Now from the View option from menu bar select "Totals" or click on the "Totals" icon on menu bar. By default it will display "Group By" for each column selected. Under the 1st mark column set criteria to 0. Under the 2nd mark column change the "Group By" to "Count". Under the 2nd mark column change the sort to "Descending".
Dec 21 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.