How to get a value from previous record in a query

[ariansman]

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]

Hi. You can use DLookup() or a subquery.

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

 

[ariansman]

Hi. You can use DLookup() or a subquery.

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

I am not an expert. what will the SQL of of the Query2 be? should put the dlookup in the subquery?

 

[theDBguy]

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

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

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]

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]

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

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

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?

 

[Galaxiom]

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]

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]

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]

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]

Oops, my bad. I forgot to put the alias for T1.

thank you very much it works

 

[theDBguy]

thank you very much it works

Congratulations! Glad to hear you got it to work. Good luck with your project.

 

[ariansman]

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]

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.