Return values record -n

[CoffeeGuru]

I have a grouped query that returns a list of all periods

qryPeriod

SELECT [tblMaster].Year, [tblMaster].Week, ([Year] & " - " & Right(0 & [Week],2)) AS Period
FROM [tblMaster]
GROUP BY [tblMaster].Year, [tblMaster].Week, ([Year] & " - " & Right(0 & [Week],2));

Year | Week | Period
2013 | 48 | 2013 - 48
2013 | 49 | 2013 - 49
2013 | 50 | 2013 - 50
2013 | 51 | 2013 - 51
2013 | 52 | 2013 - 52
2014 | 1 | 2014 - 01
2014 | 2 | 2014 - 02
2014 | 3 | 2014 - 03
2014 | 4 | 2014 - 04


I want to work out what the last period - 1 is (2014 - 03)
I want to work out what the last period - 4 is (2013 - 52)
I want to work out what the last period - 6 is (2013 - 50)
I want to work out what the last period - 8 is (2013 - 48)

these are then used as the start of a between lookup against [tblMaster] along with the last record in the qryPeriod (2014 - 04)

I am guessing that I will need 4 queries so If someone can sort out last period - 8 I can probably do the others.

Question: would DLookUp do this say return the last 4 values, Last 6 Values and Last 8 Values

 

[plog]

First, "Year" and "Week" are poor choices for field names because they are reserved words. Keeping them like that can give you trouble when you try to write code--which is going to be required for this. I would suggest prefixing those field names with the data they are for (i.e. PeriodYear, PeriodWeek).

Second, I wouldn't use queries to get this data (you'll need them to help), but I would use a function that takes a number to determine how many weeks from the last one you want. To do this, you do need queries. Use this SQL for qryPeriod:

SELECT tblMaster.Year AS PeriodYear, tblMaster.Week AS PeriodWeek, ([Year] & " - " & Right(0 & [Week],2)) AS Period, ([Year] & Right(0 & [Week],2))*1 AS PeriodValue
FROM tblMaster
GROUP BY tblMaster.Year, tblMaster.Week, ([Year] & " - " & Right(0 & [Week],2)), ([Year] & Right(0 & [Week],2))*1;

Then build another query which will rank your queries to determine the order in which they occur:

SELECT qryPeriod.Period, 1*DCount("[PeriodValue]","qryPeriod","[PeriodValue]<=" & [PeriodValue]) AS PeriodRank
FROM qryPeriod;

Name that query "qryPeriodRanks". Finally, paste this code into a module:

Public Function get_Period(p)
    ' returns period value from qryPeriodRanks where the rank is p less than the upper rank
    
    Dim ret As String           ' return value of function, error by default
    Dim toprank As Integer      ' value of highest rank in qryPeriodRanks
    
    ret = "Invalid Period"
    
    toprank = DMax("[PeriodRank]", "qryPeriodRanks")
    ' gets last period
    
    If IsNull(DLookup("[Period]", "qryPeriodRanks", "[PeriodRank]=" & (toprank - p))) = False Then ret = DLookup("[Period]", "qryPeriodRanks", "[PeriodRank]=" & (toprank - p))
    ' verifies that p periods ago exists, if so returns period
    
    get_Period = ret
    
    
End Function

The simplest example of how to use this is in a query. It would look like this in Design view:

PeriodMinus4: get_Period(4)


Run that and it will return the period that occured 4th from the most current (based on your sample data it would return "2013 - 52"). Whatever number you put in the parenthesis it will return that many periods ago. So to get the current period you would do this:

CurrentPeriod: get_Period(0)

You can also use that function in forms and reports by setting it as the control source.

 

[CoffeeGuru]

plog

That is an amazing feat of work, I owe you so much gratitude.
Not sure what is going on with my laptop but running "qryPeriodRanks" has caused a major stall in Access it is taking an age to run.

tblMaster has around 1.5M records in it maybe thats the problem.
The data is in the process of being migrated to SQL Server so hopefully that will be easier then.

As soon as Access becomes free again I will try the last part of your work.

I note your comments on naming conventions. I am on a severe learning curve here.

 

[plog]

I was afraid of that. The DCount() in qryPeriodRanks is the culprit for the slowness. What you want can be done without qryPeriodRanks, it would require the function to be more complex, but it can be sped up.

Is it intolerably slow? How long is it taking and how often do you have to run this query?

 

[CoffeeGuru]

I was afraid of that. The DCount() in qryPeriodRanks is the culprit for the slowness. What you want can be done without qryPeriodRanks, it would require the function to be more complex, but it can be sped up.

Is it intolerably slow? How long is it taking and how often do you have to run this query?

I killed it in the end, so 30 mins to run and I got worried.

It will be run many many times, apologies for the bad news, but a brave attempt as you have no idea of my work load.

====
I meant to add I looked at this

SELECT * FROM
     (SELECT TOP 4 * FROM qryPeriod ORDER BY Period DESC)
ORDER BY period ASC

 

[plog]

Its going to take me an hour or so to get back to this, but I will work on my code to remove qryPeriodRanks.

The TOP query you posted might work for you. See if it gives you what you want and let me know. Otherwise I will work on revising my code to make it faster.

 

[CoffeeGuru]

Thank you I will let you know if I get it to work.