Select Query - Max

[Jim Dudley]

I have two tables.
Table one contains a list of Workshops, Key is WCode
56 Records in Table. One Table.
Table two contains a list of students' Attendance at workshops and contains a Key of WCode
433 Records in table. Many Table

I need to extract 3 things. via 3 different queries.
1. The Average attendance per workshop. 433/56=7.73 but how do I do this in a query?
2. The Maximum attendance per workshop. How do I do this in a query?
3. The Minimum attendance per workshop. The same as 2 except different selection.

Is it possible to do this in a single query? or Combine the 3 results into a single query?

Looking forward to suggestions.

Thank you Jim

 

[tehNellie]

You should be able to do all of that in one query.

SELECT wcode, AVERAGE(attendance), MIN(attendance), MAX(attendance), SUM(attendance), Count(wcode)
FROM tblAttendence
GROUP BY wcode

The GROUP BY, does just that, so you've got your many records for each workshop in your attendance table, so by telling ACCESS that you want to group on the work code ID you can now apply calculations and expressions to the other columns in that table so I've included a few more as examples.

I'm on a Mac at the moment so I'm not 100% sure that Access includes the Average Function, the others certainly exist.

 

[jzwp22]

I think you will need 2 queries. The first query is to get the count of students for each workshop. Something like this

query name: qryCountByWorkshop
SELECT tblWorkShops.WCode, tblWorkShops.txtWorkshopName, Count(tblWSAttendance.fkStudentID) AS CountOffkStudentID
FROM tblWorkShops INNER JOIN tblWSAttendance ON tblWorkShops.WCode = tblWSAttendance.WCode
GROUP BY tblWorkShops.WCode, tblWorkShops.txtWorkshopName;

Then another query that uses the above to get your statistics

SELECT Min(qryCountByWorkshop.CountOffkStudentID) AS MinOfCountOffkStudentID, Max(qryCountByWorkshop.CountOffkStudentID) AS MaxOfCountOffkStudentID, Sum(qryCountByWorkshop.CountOffkStudentID) AS NumberOfStudentsAttending, Count(qryCountByWorkshop.WCode) AS NumberOfWorkshops,NumberOfStudentsAttending/NumberOfWorkshops as AvgAttendance
FROM qryCountByWorkshop;

 

[Jim Dudley]

Thank you for your input folks, I will try your suggestions and report back tomorrow with the results.

Jim

 

[Jim Dudley]

Below is the structure of tbl_attendance.

The query you suggested works but the result is not useful. If we could modify it to use the SNum field as a Count as opposed to a literal of the content. As it stands now the query is treating the SNum field numbers in its calculations.

SQL I created from your input looks like this.

SELECT tbl_Attendance.WCode, Avg(tbl_Attendance.SNUM) AS AvgOfSNUM, Max(tbl_Attendance.SNUM) AS MaxOfSNUM, Min(tbl_Attendance.SNUM) AS MinOfSNUM, Sum(tbl_Attendance.SNUM) AS SumOfSNUM, Count(tbl_Attendance.WCode) AS CountOfWCode
FROM tbl_Attendance
GROUP BY tbl_Attendance.WCode;

Structure of tbl_attendance: (Many table)

WCode - txt - Unique Alpha/Numeric identifier for each worshop Key to tbl_WC (One table

Type - txt - Category Code of each workshop. Key to tbl_WS
Credits - numeric - Number of credits earned by student for attending workshop. Key to tbl_WS
WDate -Date/Time - Key Links to tbl_WS.
LName - txt - Last Name of student.
FName - txt - First Name of student.
Campus - txt - Unique Alpha identifier for each Campus
SNum - txt - Unique 9 digit identifier for each student. - Key - Links to tbl_WS.
EMail - txt - Students' EMail address.
ta_DateModified - Date/Time - System date of last modification to record.

Please let me know if you require any further information.

Look forward to your response.

Thank you.

Jim

 

[jzwp22]

The query you suggested works but the result is not useful.

How is it not useful?

The query you suggested works but the result is not useful. If we could modify it to use the SNum field as a Count as opposed to a literal of the content. As it stands now the query is treating the SNum field numbers in its calculations.

You can use the SNum in the count, but you should not be using it for the average (averaging a text field does not make sense) as you show below. You cannot get the average with 1 query since you need to do the counting first. That does not say that you cannot use a nested query.

SELECT tbl_Attendance.WCode, Avg(tbl_Attendance.SNUM) AS AvgOfSNUM, Max(tbl_Attendance.SNUM) AS MaxOfSNUM, Min(tbl_Attendance.SNUM) AS MinOfSNUM, Sum(tbl_Attendance.SNUM) AS SumOfSNUM, Count(tbl_Attendance.WCode) AS CountOfWCode
FROM tbl_Attendance
GROUP BY tbl_Attendance.WCode;

 

[jzwp22]

BTW, your attendance table is not structured properly. It violates normalization rules.

For example, you say :SNum - txt - Unique 9 digit identifier for each student. - Key - Links to tbl_WS.

If the student information is in tbl_WS why do you repeat their name and e-mail address in the attendence table, you only need the unique identifier. Repeating the same information in more than one table (other than the key field) violates a key normalization rule.

The same would hold for the workshop category code. That should be in the workshop table.

 

[Jim Dudley]

To answer your question about normalization regarding names and email etc. being duplicated. When first designed that information came from the Student Table based on the Key Field SNum. When the Contract programmer finished the programming he rearranged the relationships and the lookup no longer worked. I added the data back into the file via an update query from the Student table. This was done for readability purposed. I am not happy with the Program Code doing this but it is paid for and does handle the imports from Excel that we wanted it to. If I rearrange the relationships and reset the relational integrity options, the code does not work. Rock and a hard place.

I have developed the suggested second query and it runs. The answer it gets for the Total Attendance though (15155) is incorrect. If I Sum the numbers in the Query QCountByWorkshop for CountOffSNum I get 433. I believe this number to be the Total Attendance. BTW It ask for a Parameter when run for The CountOffSNum. I did not understand why? If I put in 433 it runs but the numbers are still not correct?
These are the first queries I have done using SQL. Up to this point I have used either the Wizards or the Query Design screen. Please bear with me while I learn a better way.

SQL:

SELECT Min(QCountByWorkshop.CountOFFSNum) AS MinOfCountOffSNum,
Max(QCountByWorkshop.CountOffSNum) AS MaxofCountOffSNum,
Sum(QCountOfWorkshop.CountOffSNum) AS NumberOfStudentsAttending,
Count(QCountByWorkshop.WCode) AS
NumberOfWorkshops,NumberOfStudentsAttending/NumberOfWorkshops AS AvgAttendance
FROM QCountByWorkshop;

Thank you.

Look forward to your feed back.

Jim

 

[jzwp22]

BTW It ask for a Parameter when run for The CountOffSNum

Typically if a parameter was not specifically added, it means a field name is spelled wrong or something along those lines.

Is there any chance that you can post your database (or a stripped down version)? I cannot see why a count would give you more records than what is in the table.

Can you post the SQL of this query: QCountByWorkshop

 

[Jim Dudley]

Two Questions.
What does a stripped down database consist of?
How do I post it to this forum or can I WinZip it and send it via email?

Jim

 

[jzwp22]

For a striped down version, I would just need your attendance table with the student last names and student numbers removed. The easiest way to accomplish this is to create a new database and import the attendance table into it. Then go into design view of the table and delete the student last name field and the student number field. Run the compact and repair utility that Access has (tools menu). Then use winzip to zip the database file.

You can then attach the database to your next reply by clicking the paper clip icon

 

[Jim Dudley]

The Key Fields in tbl_Attendance are:
WCode
Type
Credits
WDate
SNum

I have enclosed I think, everything you requested. Thank you in advance for your help. The data requested is attached.

Jim

 

[jzwp22]

I've attached the modified database with qryOverallStats that shows the minimum attendance for a workshop, the maximum attendance and the average attendance. These values are across all workshops. I assume that is what you meant by the following:

1. The Average attendance per workshop. 433/56=7.73 but how do I do this in a query?
2. The Maximum attendance per workshop. How do I do this in a query?
3. The Minimum attendance per workshop. The same as 2 except different selection.

If my interpretation is incorrect, then forgive me, but please provide an example of what you are after.

Now if you want to find which workshop had the maximum attendance and which one had the minimum attendance then we would have to us a slightly different approach.

Looking at your data, I see that you have similarly identified workshops such as CD-1011-01-A and CD-1011-02-A. Are these different sessions of the same workshop, i.e. are they related?

 

[Jim Dudley]

Thank you for your response I will study it and hopefully learn.

To answer your last question in the response. The two Workshop Codes you quoted are similar only in that they are both Category CD (Community Development Workshops). If they were 2 incidents of the same workshop they would be Coded CD-1011 -01-A & -B pr CD-1011-02-A & -B respectively.
The -01 segment refers to the number in this Category of the academic year (1011). The final section is an indicator of multiple incidents of the same Workshop Title/Name. There are at this school only 3 Categories. The others are OD and PD (Organizational Development and Personal Development)

It is late so I will examine and comment on the attachment tomorrow.

Thank you.

Jim

 

[Jim Dudley]

I have examined the file you attached and the queries work very nicely. Obviously SQL is much more flexible than the Query Designer and more powerful. Can you recommend some reading material that would start a Novice into the realm of SQL.

I can see that other queries I have created could be re-written as SQL Statements and achieve the results more directly that some of the methods I have used in the past.

I really appreciate your input. I am going to apply it to the other tables we do a Periodic Analysis on. From all the records, the user can select a subset of records based on a Date Range and then look at the Analysis.

This is great. Have a nice day.

Regards,

Jim

 

[tehNellie]

I have examined the file you attached and the queries work very nicely. Obviously SQL is much more flexible than the Query Designer and more powerful. Can you recommend some reading material that would start a Novice into the realm of SQL.

I can see that other queries I have created could be re-written as SQL Statements and achieve the results more directly that some of the methods I have used in the past.

I really appreciate your input. I am going to apply it to the other tables we do a Periodic Analysis on. From all the records, the user can select a subset of records based on a Date Range and then look at the Analysis.

This is great. Have a nice day.

Regards,

Jim

If you can get used to using SQL view (in conjunction with the design view which does have its place, especially for speeding up making fairly basic queries) then you'll find that you have much more flexbility and control available to you. the worst thing about SQL view is that it exposes you to Access' weird fetish with brackets which will slowly start to drive you insane

To be honest, the easiest way to learn is to DO. Starting off by building your queries in the query designer and then switching to SQL view to review what it's done can be a good way to start bridging that divide and thinking about your queries in SQL to begin with.

http://www.w3schools.com/sql/ Is an ok place to start and has a decent reference of SQL commands

 

[Jim Dudley]

I've attached the modified database with qryOverallStats that shows the minimum attendance for a workshop, the maximum attendance and the average attendance. These values are across all workshops. I assume that is what you meant by the following:

Now if you want to find which workshop had the maximum attendance and which one had the minimum attendance then we would have to us a slightly different approach.

I would love to see your approach to this. I am attaching the tbl_WS for your information.

Looking forward to your reply.

Regards,

Jim

 

[jzwp22]

I have examined the file you attached and the queries work very nicely. Obviously SQL is much more flexible than the Query Designer and more powerful.

Actually, I start with the design grid view and do a lot of the query creation there (saves on typing) and flip over to SQL view to do minor editing.

The only books I have purchased over the years had to do with Visual Basic for Application (VBA) coding in Access. With respect to queries, I have learned more by looking at posts on the various Access forums. I suppose that there are books out there, so you might try doing a search.

 

[jzwp22]

You did not include the attendance table in your most recently posted database, so the queries I created will not work. I went ahead and imported the additional tables from the database you posted into the one I posted last night.

If you run the query QCountByWorkshop and scroll down the list you will see the workshop with the max attendees (74). To extract this from the QCountByWorkshop, we need another query that finds the max value. Essentially this is just one portion of the qryOverallStats

query name: qryGetMax
SELECT Max(QCountByWorkshop.CountOfWCode) AS MaxOfCountOfWCode
FROM QCountByWorkshop;


Now we need to join the above query back to the QCountByWorkshop to get the corresponding WCode. The join needs to be made on via the MaxOfCountOfWCode field of the qryGetMax and the CountOfWCode field of the QCountByWorkshop.

We can also add in the tbl_WS (I had to clean up your key field in that table). I just brought in a couple fields from tbl_WS for illustration purposes. You could include all of them if you wanted.

query name: qryMaxAttendWSDetail
SELECT QCountByWorkshop.WCode, QCountByWorkshop.CountOfWCode, tbl_WS.Workshop, tbl_WS.Type, tbl_WS.Credits
FROM (qryGetMax INNER JOIN QCountByWorkshop ON qryGetMax.MaxOfCountOfWCode = QCountByWorkshop.CountOfWCode) INNER JOIN tbl_WS ON QCountByWorkshop.WCode = tbl_WS.Wcode;


Now, looking at your tbl_WS, you have several normalization issues. First, if a workshop has many sessions (I see the same workshop name repeated in several records). That describes a one-to-many relationship which dictates that the sessions/incidents should be in a separate but related table. This will require you to split your WCode field. Additionally, the sequentially numbered fields LO1, LO2, etc. are an example of repeating groups which is a clear sign that your structure is not normalized. If a workshop has many learning outcomes, that describes a one-to-many relationship. Further, if a learning outcome can apply to many workshops, you have a second one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (workshops & Learning outcomes) you have a many-to-many relationship which is handled with a junction table.

tbl_WS
-pkWSID primary key, autonumber
-WCode (adjusted to remove incident part)
-WorkShop

LearningOutcomes
-pkLOID primary key, autonumber
-Description
-Outcome

tblWSLearningOutcomes
-pkWSLOID primary key, autonumber
-fkWSID foreign key to tbl_WS
-fkLOID foreign key to LearningOutcomes

I typically use an autonumber primary key field in all of my tables and make joins to other tables using that field. Relational databases are generally more efficient when using numeric fields as compared to text fields.

I would recommend getting your tables normalized before going any further. It will save you a lot of headaches in the future.

I have attached the DB with the additional queries above, but I have not modified your tables to correct the normalization issues.

 

[Jim Dudley]

That is a lot to digest but I thank you very much. I will work on your suggestions to try and build a better solution for the application. There is a difference between 'It works' and 'It works properly'. I have spent an hour or so reading the Link on SQL that you suggested. I am finding it very helpful and have added it to my Bookmarks.

Thank you again for all your input, it is sincerely appreciated.

Regards,

Jim