How to get a value from previous record in a query (1 Viewer)

ariansman

Registered User.
Local time
Today, 07:06
Joined
Apr 3, 2012
Messages
140
How to get a value from previous record in a query

The Following query has brought records for John’s math record from student’s score table named MyTable

Query1
ID ---- Studentname ------ subject ---- score ---- month
.
12 ----John ---- ---- ---- math ---- ---- 17 ---- ---- January
18 ---- John ---- ---- ---- math ---- ---- 12 ---- ---- February
54 ---- John ---- ---- ---- math ---- ---- 19 ---- ---- April
71 ---- John ---- ---- ----math ---- ---- 7 ---- -- ---- Jun
92 ---- John ---- ---- ---- math ---- ----15 ------ ---- Sep

How can we get the score from previous exam?

Query2
ID ---- Studentname ---- subject ---- score ------ month ----previous_Score
.
12 ---- ----John ---- ---- --- math ---- 17 ---- ---- January ---- ---- 17 (or null)
18 ---- ---- John ---- ------- math ---- 12 -- ------ February --- ----- 17
54 ---- ---- John ---- --- ---- math ---- 19 -- ------ April ---- ------- 12
71 ---- ---- John ---- --- ---- math ---- 7 ---- ---- Jun ---- ---------- 19
92 ---- ---- John ---- --- ----math ---- 15 ---- ---- Sep ---- ---- ------ 7


I put the dashes just to better picture the fields.
Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:06
Joined
Oct 29, 2018
Messages
13,168
I am not an expert. what will the SQL of of the Query2 be? should put the dlookup in the subquery?
Hi. I was saying "either/or" not necessary to use both at the same time (although, you could). Either way you go (DLookup or Subquery), it is going to be a slow query. So, assuming the ID column always increases each time you add a score, you could try something like:

DLookup
SQL:
SELECT ID, StudenName, Subject, Score, ExamMonth,
DLookup("Score", "TableName", "StudentName='" & [StudentName] & "' AND Subject='" & [Subject] & "' AND ID="
DMax("ID", "StudentName='" & [StudentName] & "' AND Subject='" & [Subject] & "' AND ID<" & [ID])) AS PreviousScore
FROM TableName

Subquery
SQL:
SELECT T1.ID, T1.StudentName, T1.Subject, T1.Score, T1.ExamMonth,
    (SELECT TOP 1 T2.Score FROM TableName T2
        WHERE T2.StudentName=T1.StudentName
            AND T2.Subject=T1.Subject
            AND T2.ID<T1.ID
        ORDER BY ID DESC) AS PreviousScore
FROM TableName
(both untested)
Hope that helps...
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 09:06
Joined
Feb 28, 2001
Messages
18,324
Ariansman, this is something that is harder than it might first seem. You got good advice from theDBguy but it probably confused you. Here is the theory behind why it is so complex looking.

In Access, the elements of a table have no guaranteed order intrinsically. That is because according to SET THEORY (which drives modern database systems), queries "appear" to occur all at once. I.e. if done right, you can't tell the difference between whether a query was executed in series or in parallel. Therefore, "order" has no meaning for a true table operation.

Order creeps in when you have queries for which there is an ORDER BY clause. Then, you can have an ordering of records (including the case you want, ordering by date). In ENGLISH, the problem is to identify the most recent (highest date) record for the same student where that date is earlier than (less than) the record for the current date. Without an ORDER BY clause based on dates, you cannot do this. NOTE CAREFULLY that having month names doesn't help since month names (being text) appear in alphabetic order. Your data set must include the full date even though you can choose to display the month name using any of several formatting functions. But if you don't actually have the real date in the record, you are stuck.

The difference between Access and Excel shows up in problems like this, because in Excel, rows DO have intrinsic order and this question would be much easier to answer.
 

ariansman

Registered User.
Local time
Today, 07:06
Joined
Apr 3, 2012
Messages
140
Hi. I was saying "either/or" not necessary to use both at the same time (although, you could). Either way you go (DLookup or Subquery), it is going to be a slow query. So, assuming the ID column always increases each time you add a score, you could try something like:

DLookup
SQL:
SELECT ID, StudenName, Subject, Score, ExamMonth,
DLookup("Score", "TableName", "StudentName='" & [StudentName] & "' AND Subject='" & [Subject] & "' AND ID="
DMax("ID", "StudentName='" & [StudentName] & "' AND Subject='" & [Subject] & "' AND ID<" & [ID])) AS PreviousScore
FROM TableName

Subquery
SQL:
SELECT T1.ID, T1.StudentName, T1.Subject, T1.Score, T1.ExamMonth,
    (SELECT TOP 1 T2.Score FROM TableName T2
        WHERE T2.StudentName=T1.StudentName
            AND T2.Subject=T1.Subject
            AND T2.ID<T1.ID
        ORDER BY ID DESC) AS PreviousScore
FROM TableName
(both untested)
Hope that helps...
Thank you,
i copied and pate both of them, separately, but received the following errors. i made the screen shot attached.
I tried to change he code in someway, with no success.:(
and advice?
 

Attachments

  • Dlookup.jpg
    Dlookup.jpg
    105.3 KB · Views: 13
  • subquery.jpg
    subquery.jpg
    32 KB · Views: 12

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:06
Joined
Jan 20, 2009
Messages
12,108
Relying on autonumber like this is not a good practice. As Docman says, better to use the date. If you only care about the month then arbitrarily choose the first of the month as the date to record.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:06
Joined
Jan 23, 2006
Messages
13,400
ariansman,
Allen Browne has an article specifically on subqueries and an example of getting the value from another record. This link is often referenced.
You may also get assistance by reviewing the Similar Threads at the bottom of this thread/page.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 09:06
Joined
Feb 28, 2001
Messages
18,324
but received the following errors. i made the screen shot attached.

That is an input box asking for a parameter. I think I know why. You used a sub-query and defined its source (FROM clause) with an alias of T2. No problem there. But nowhere in the query you showed us did you define T1. Therefore, when you qualified StudentName with T1, SQL has no clue as to where T1 actually exists.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:06
Joined
Oct 29, 2018
Messages
13,168
Thank you,
i copied and pate both of them, separately, but received the following errors. i made the screen shot attached.
I tried to change he code in someway, with no success.:(
and advice?
Oops, my bad. I forgot to put the alias for T1.
 

ariansman

Registered User.
Local time
Today, 07:06
Joined
Apr 3, 2012
Messages
140
Ariansman, this is something that is harder than it might first seem. You got good advice from theDBguy but it probably confused you. Here is the theory behind why it is so complex looking.

In Access, the elements of a table have no guaranteed order intrinsically. That is because according to SET THEORY (which drives modern database systems), queries "appear" to occur all at once. I.e. if done right, you can't tell the difference between whether a query was executed in series or in parallel. Therefore, "order" has no meaning for a true table operation.

Order creeps in when you have queries for which there is an ORDER BY clause. Then, you can have an ordering of records (including the case you want, ordering by date). In ENGLISH, the problem is to identify the most recent (highest date) record for the same student where that date is earlier than (less than) the record for the current date. Without an ORDER BY clause based on dates, you cannot do this. NOTE CAREFULLY that having month names doesn't help since month names (being text) appear in alphabetic order. Your data set must include the full date even though you can choose to display the month name using any of several formatting functions. But if you don't actually have the real date in the record, you are stuck.

The difference between Access and Excel shows up in problems like this, because in Excel, rows DO have intrinsic order and this question would be much easier to answer.
yeah. seems a tad complicated for a non-professional access worker like myself. as you said TheDBguy had a nice and smooth solution. the second one worked for me.
thank you for your reply :)
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 09:06
Joined
Feb 28, 2001
Messages
18,324
I'll let you in on an ill-kept secret. Almost NONE of us here started out as professional Access workers. We kind of fell into it or grew into it. By original training, I'm a chemist, but a complicated sequence of events in my family life caused me to become a systems administrator for the U.S. Navy Reserve. Access was just another tool that was available to help me do a job and when a project came up for my department, they knew I was familiar with Access. This is how you get "stuck" doing something, but I turned it into a positive thing that eventually led me to get better pay and more job security. But if you had asked me 35 years ago if I would be an "expert" in a database platform, I might have taken - and lost - a bet on it.
 

Users who are viewing this thread

Top Bottom