A query that compares Top 3 from two different dates (1 Viewer)

[Aurelius]

I have two tables in my Guitar practice database.

"phrases" contains short musical phrases with a target tempo to play it at(phraseTempo)
Fields: ID, phraseName, phraseTempo.

"logPhrases" records each practice session.
Fields: ID, phrasesID,logDate,logtime,logTempo,logMastery,logRelaxation

logTempo is the tempo I practiced at
logRelaxation is how relaxed I was on a scale from (0 to 10)
logMastery is how much I mastered the piece at that tempo (from 0 to 10)

I have over a year's worth of data, and I've been trying to design a query called qryStickingPoints that will pull out phrases that I've made the least progress in. I've struggled all evening and I can't even get beyond step 1

It should work something like this.

1. Take the 3 most recent entries from >6 months ago, and build an average score. We'll call this "OldGuitarScore"
2. Take the 3 most recent entries from now and, build an average score. We'll call this NewGuitarScore
3. The difference between New and Old is the improvement. The smaller the difference the more I need to work on this piece

I came up with a formula for score called GuitarScore. It's (logMastery+logRelaxation)+((logMastery+logRelaxation)*logTempo)). I tested this with my data in Excel and it gives me the results I want. But how to get MS Access to do this in a query is beyond me.

Would appreciate any help.

 

[June7]

I have an idea. Could you provide db so I have data to test with?

 

[Aurelius]

Here's just the phrases and logphrases data

 

[arnelgp]

i made 5 queries (if this is correct for you):

OldGuitarScoreQ
OldGuitarAverageScoreQ
NewGuitarScoreQ
NewGuitarAverageScoreQ

CombineNewOldAverageGuitarScoreQ (your final query).

 

[Aurelius]

That's really helpful, and the final result is more or less what I expected. Just to be clear, are you calculating the OldAverage by taking the average of everything from greater than 6 months ago? If I improved rapidly from 1 year ago to 6 months ago that would be included as Old Average,correct?

When I learn a piece I might improve rapidly while I figure out the notes, but when it comes to building tempo that's where I might get stuck or bored. So early improvement might skew OldAverage.

Ideally when I'm looking for the OldAverage I want the most recent average from 6 months ago, using the 3 most recent entries. For example, if I improved a lot from January to March, not so much May to June, then 6 months go by where I don't improve at all, I would really only be interested in comparing a June snapshot with a December snapshot.

I was trying to calculate it using Top 3 but I was struggling to get the subqueries to work.

However, you've given me something great to work with so I'm sure I can figure it out from here.

 

[arnelgp]

are you calculating the OldAverage by taking the average of everything from greater than 6 months ago? If I improved rapidly from 1 year ago to 6 months ago that would be included as Old Average,correct?

yes 6 months (earlier and below).

if 1 year ago, you will need to modify the Criteria of the Query:

< DateAdd("yyyy",-1, Date())

and for new:

>= DateAdd("yyyy",-1, Date())

 

[ebs17]

I can't open your database because my access version is too old.

1. Take the 3 most recent entries from >6 months ago, and build an average score. We'll call this "OldGuitarScore"

SELECT
   O.phrasesID,
   AVG(O.Score) AS OldAVGScore
FROM
   (
      SELECT
         U.phrasesID,
         (U.logMastery + U.logRelaxation) + (U.logMastery + U.logRelaxation) * U.logTempo AS Score
      FROM
         logPhrases AS U
      WHERE
         U.ID IN
            (
               SELECT TOP 3
                  U1.ID
               FROM
                  logPhrases AS U1
               WHERE
                  U1.phrasesID = U.phraseID
                     AND
                  U1.logDate + U1.logtime < DateAdd("m", - 6, Date())
               ORDER BY
                  U1.logDate + U1.logtime DESC,
                  U1.ID
            )
         ) AS O
GROUP BY
   O.phrasesID

2. Take the 3 most recent entries from now and, build an average score. We'll call this NewGuitarScore

SELECT
   N.phrasesID,
   AVG(N.Score) AS NewAVGScore
FROM
   (
      SELECT
         U.phrasesID,
         (U.logMastery + U.logRelaxation) + (U.logMastery + U.logRelaxation) * U.logTempo AS Score
      FROM
         logPhrases AS U
      WHERE
         U.ID IN
            (
               SELECT TOP 3
                  U1.ID
               FROM
                  logPhrases AS U1
               WHERE
                  U1.phrasesID = U.phraseID
               ORDER BY
                  U1.logDate + U1.logtime DESC,
                  U1.ID
            )
         ) AS N
GROUP BY
   N.phrasesID

3. The difference

SELECT
   P.phraseName,
   OS.OldAVGScore,
   NS.NewAVGScore,
   OS.OldAVGScore - NS.NewAVGScore AS Diff
FROM
   (phrases AS P
      INNER JOIN qryOldGuitarScore AS OS
      ON P.ID = OS.phraseID
   )
   INNER JOIN qryNewGuitarScore AS NS
   ON P.ID = NS.phraseID
ORDER BY
   OS.OldAVGScore - NS.NewAVGScore

I could combine the three queries into one. But you might lose the overview.