Combining two or more records and summing a field

[AChir]

I have a database storing exam info:

Studentid
exam
date
session
duration

Some exams are actually due in the same session on the same day, so a student may be doing 20 exams, two or three of which are due in the same session. I therefore need to combine those records into

Studentid (unchanged)
exam 1 followed by exam2
date (unchanged)
session (unchanged)
durations of exams added together + 5 minutes changeover per exam

I have searched and found stuff to enable me to find the relevant "exam clash" students but am really stuck now. I need to do later calculations based on the number of students in an exam room at any time so the fact that it is one student in for a longer time means (I think) that I need to combine the records

Any ideas gratefully received

Many thanks

 

[Jon K]

Any ideas gratefully received

You will need to use VBA. If the table is small, a Totals Query using two public functions will do. If the table is large, a temporary table is more efficient.


The following additional table structure info determines how the VBA code is written:-

Is StudentID a text field or a numeric field?
What is the data type of the Duration field? How is duration entered in the field?


The following info determines whether to use a Totals Query or a temporary table, and whether to use ADO or DAO when writing the code:-

Approximately how many records are there in the table?
Is the database run in Access 97, or in Access 2000 (or higher)?

.

 

[AChir]

Thanks for the reply

In answer to your questions:

Is StudentID a text field or a numeric field? Numeric
What is the data type of the Duration field? Short time
How is duration entered in the field? In minutes e.g. "90"

The following info determines whether to use a Totals Query or a temporary table, and whether to use ADO or DAO when writing the code:-

Approximately how many records are there in the table? 3000
Is the database run in Access 97, or in Access 2000 (or higher)? 2000

Many thanks, Jon

Sally

 

[Jon K]

For a table of 3000 records, I think updating a temporary table is faster than running a query with two public functions.

I have attached a sample database which contains both methods.

You can open the form and click on the command button to update the temporary table "tblTemp". The code used is in the On Click event of the command button. 5 minutes (i.e. #0:5#) is allowed between exams taken in the same Session by a student.

You can also run the query "Query1", which uses the two functions contained in Module1: CombineExams() and SumDuration(). The query should produce the same results as the data in the temporary table.


Note
You can't enter 90 in a data/time field formatted as "Short Time". You can only enter 1:30 in the field (meaning 1 hour 30 minutes).

.

 

[AChir]

That's brilliant, thank you

Thank you so much. I know some (self-taught) VBA but would never have managed what you have done & following it through has been really interesting. I have used the VBA approach for the sake of speed and it works beautifully

Thanks again

Sally

 

[DLB]

Jon K,

I'm new to VBA and eager to learn. Your database runs fine.

When I read your code, I find this SQL string.

SQL = "SELECT [Exam]" & _
      " FROM [tblExams]" & _
      " WHERE [StudentID]=" & rstList![StudentID] & " AND" & _
      " [b]Format([ExamDate],'yyyymmdd')='" & Format(rstList!ExamDate, "yyyymmdd") & "'[/b] AND" & _
      " [Session]='" & rstList!Session & "'"

It is contrary to the VBA book that I have. My book says I must use the # sign to delimit dates. So I change the SQL string to this.

SQL = "SELECT [Exam]" & _
      " FROM [tblExams]" & _
      " WHERE [StudentID]=" & rstList![StudentID] & " AND" & _
      " [b][ExamDate]=#" & rstList!ExamDate & "#[/b] AND" & _
      " [Session]='" & rstList!Session & "'"

But it doesn't work. I get an error in the update that says: Field 'tblTemp.Exams' cannot be a zero-length string. So obviously no record is returned by the SQL.


Since you haven't used the # sign in your code, I think there must be a reason for not using it. Can you tell me why the # sign doesn't work?


Thanks and best regards.

 

[WayneRyan]

Sally,

You get that message because in at least one of your records,
ExamDate has no value.

SQL is going to try to make your statement something like this:

" [ExamDate] = #6/14/2004# AND" & _
               ===========

which reads fine, but if there is no data for ExamDate:

" [ExamDate] = ## AND" & _
               ==

Which is not good.

What Jon did was use the format function:

" Format([ExamDate],'yyyymmdd')='" & Format(rstList!ExamDate, "yyyymmdd") &

Even with no data in ExamDate, the format returns an empty
string and his comparison is good --> '' = ''

Wayne

 

[DLB]

Thank you for your reply, Wayne.

But, if I read Jon K's code right, the recordset rstList in the sample

' open distinct list of exam date, session and student id as a recordset.
SQL = "SELECT [ExamDate], [Session], [StudentID], " & _
" Sum([tblExams].[Duration])+(Count(*)-1)*#0:5# as [Duration]" & _
" FROM [tblExams]" & _
" GROUP BY [ExamDate], [Session], [StudentID]"

rstList.Open SQL, cnn, adOpenStatic, adLockReadOnly

Do While Not rstList.EOF
............
............
Loop​

pulls the ExamDate from table tblExams:

Studentid	Exam	  ExamDate	Session	    Duration
     1001	Exam 1	  5/7/2004	Session A 	1:30
     1001	Exam 2	  5/7/2004	Session A	1:30
     1001	Exam C	 14/7/2004	Session B	1:30
     1002	Exam 3	  5/7/2004	Session B	1:30
     1002	Exam A	 14/7/2004	Session A	1:30
     1002	Exam B	 14/7/2004	Session A	1:30

So rstList!ExamDate can never be empty.

Doris

 

[Jon K]

Doris,

It is a system date format issue.


The # signs surrounding a date will always return the date in the US format. So Access will treat #5/7/2004# as May 7, 2004.

Hence on your UK date format system, the line:-
" [ExamDate]=#" & rstList!ExamDate & "# AND" & _

is equivalent to:-
[ExamDate] equals to May 7, 2004

But there are no exams on May 7, 2004 in the table, so no records will be returned, Hence you will get a zero-length string of sExams.


When we format the dates as "yyyyymmdd" before making the comparison, we are actually avoiding the system date issue.

The # sign works only on US date format systems. Format "yyyymmdd" works universally.


Since Sally is in the UK, I used Format "yyyymmdd" when I wrote the sample database.


Hope this helps.
.

 

[DLB]

Jon K,

Thank you so much.

I did some testing. I changed the Short date style in Regional Settings to m/d/yyyy and the # sign worked.

Now I know that on my UK systems, I shouldn't use the # sign to delimit dates.

Thank you again!

Doris