Working with SQL in Access - more features using Access 2000 and looking back at Access 97
SELECT DDriveNo, Round(Avg(DAmtCont),2) AS Aver
FROM newdonation
GROUP BY DDriveNo;
In the example above, I am using the GROUP BY clause to group the information by DdriveNo. Since I am asking to see things in groups, the SELECT statement has to be compatible with the group concept. Since I am grouping by DDriveNo, I can use that field in the SELECT. I could not write DAmtCont here because that is not a group number. However, I can use a group function such as average, which applies to the group. In this case I also wanted to round the average that I generate.
Note the syntax:
Avg(DAmtCont) would simply show the average to the default number of decimal places.
Round(Avg(DAmtCont),2) shows that average rounded to two decimal places. When two group functions are used the inner function is resolved before the outer function.
The results of this query are:
|
DDriveNo |
Aver |
|
123 |
760 |
|
124 |
833.33 |
|
125 |
175 |
When I save this query, Access makes some changes of its own and the following code is shown.
SELECT newdonation.DDriveNo, Round(Avg([DAmtCont]),2) AS Aver
FROM newdonation
GROUP BY newdonation.DDriveNo;
I went in and made the changes to get back to what I entered, closed and saved and the following was returned:
SELECT [DDriveNo], Round(Avg([DAmtCont]),2) AS Aver
FROM newdonation
GROUP BY [DDriveNo];
Trying to get rid of the square brackets was not a successful endeavor. Square brackets should only be required if I have a two-word data name.
When I work in Access97, I get the following: (Note the names are slightly different but the table information is basically the same). Note that the round was not accepted.
SELECT DriveNo, Avg(contribu.AmtCont) AS AvgOfAmtCont
FROM contribu
GROUP BY DriveNo;
Access97 added the table names before the fields, and put in the AS clause that I forgot to include. When I took out the table names, it did not try to reinstate them or insert the brackets. Interesting!!!!
Now I am going to look at the SUM function with the GROUP BY:
SELECT DDriveNo, SUM(DAmtCont) AS Sum
FROM newdonation
GROUP BY DDriveNo;
|
DDriveNo |
Sum |
|
123 |
3800 |
|
124 |
2500 |
|
125 |
350 |
Access2000 in all of its wisdom, changed the SQL to the following:
SELECT [DDriveNo], SUM([DAmtCont]) AS [Sum]
FROM newdonation
GROUP BY [DDriveNo];
When I did the same query in Access97, the results were left as I saved them:
SELECT DriveNo, SUM(AmtCont) AS SUM
FROM contribu
GROUP BY DriveNo;
The function COUNT allows me to count the records in the group when used with the group by clause. Note that the brackets used here are mine to allow for two words.
SELECT DDriveNo, Count(*) AS [# Donations]
FROM newdonation
GROUP BY DDriveNo;
|
DDriveNo |
# Donations |
|
123 |
5 |
|
124 |
3 |
|
125 |
2 |
This one got saved and reentered and it appeared the same. I have yet to completely figure out the Access 2000 knows best pattern and how to avoid it. It appears that when I close and it asks to save, it revises but when I do a save under file I have a better chance of retention. Please let me know if you figure out the total answer. I have checked a variety of sources and they all note that what is returned is not what you key! This was not the same level of problem when I worked in Access97.
SELECT DDriveNo, Count(*) AS [# Donations]
FROM newdonation
GROUP BY DDriveNo;
Here is the entire new donation table to use to check the results:
|
DIdno |
DDriveNo |
DDateCont |
DAmtCont |
|
11111 |
123 |
1/5/00 |
1000 |
|
11111 |
123 |
7/5/00 |
1200 |
|
11111 |
124 |
5/14/00 |
500 |
|
22222 |
123 |
5/15/00 |
500 |
|
33333 |
123 |
2/20/00 |
600 |
|
33333 |
124 |
1/12/00 |
1000 |
|
33333 |
125 |
5/5/00 |
100 |
|
44444 |
123 |
1/1/00 |
500 |
|
44444 |
124 |
5/1/00 |
1000 |
|
44444 |
125 |
8/1/00 |
250 |
SELECT DDriveNo, COUNT(DAmtCont) as Count, SUM(DAmtCont) as Sum
FROM newdonation
GROUP BY DDriveNo;
Note that COUNT can also be done on a field and that you can include two functions in the same query. The results are shown below. This time I saved the query from file save and when I came back in, by query was as written!!!
|
DDriveNo |
Count |
Sum |
|
123 |
5 |
3800 |
|
124 |
3 |
2500 |
|
125 |
2 |
350 |
SELECT DDriveNo, COUNT(DAmtCont) as Count, SUM(DAmtCont) as Sum
FROM newdonation
GROUP BY DDriveNo;
The following SELECT uses the MAX and MIN functions.
SELECT DDriveNo, MAX(DAmtCont) AS Maximum, MIN(DAmtCont) AS Minimum
FROM newdonation
GROUP BY DDriveNo;
|
DDriveNo |
Maximum |
Minimum |
|
123 |
1200 |
500 |
|
124 |
1000 |
500 |
|
125 |
250 |
100 |
In the following example, I want to group as I have been with one exception. I only want to include contribution amounts greater than 100 in the group. To do this, I use the Where clause to exclude other contributions.
SELECT DDriveno, SUM(DAmtCont) AS Sum
FROM newdonation
WHERE DAmtCont > 100
GROUP BY DDriveNo;
Notice that here I am excluding prior to forming the group. If you want to exclude after forming the group, you use the HAVING clause.
SELECT DDriveNo, Sum(DAmtCont) AS Sum
FROM newdonation
GROUP BY DDriveNo
HAVING Sum(DAmtCont) > 500;
In this example, the groups are formed and then groups that do not meet the criteria of having a sum greater than 500 are excluded.
|
DDriveNo |
Sum |
|
123 |
3800 |
|
124 |
2500 |