Solved Find last but one date

[telstar]

Hi guys, been struggling with this for a while, wondering if anyone can help me.

I have set up a music pop charts database with the following relationship

I am producing charts for each week and have managed to write a query to get various positions for each song, eg entry position, highest position, weeks in charts etc. What I would like to do is get the previous weeks position for each song if it was in the chart the previous week. If i click on tables - T-Artist, T-Songs, T-ChartPosition, it shows the following for a particular song, so in this example would like to get the position for 29/11/1952.

Thank you - any help appreciated

 

[June7]

You want each record to pull a value from prior record? Review http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[telstar]

Thanks June7. I have previously looked through that page on AllenBrowne.com, but i can't get my head around sub-queries on multiple tables

 

[jdraw]

Can you post a copy of your database? We only need a sample of the data and any code you have.

 

[telstar]

Sure, thanks jdraw. Database with sample data attached.

 

[arnelgp]

you already have it in your form?

 

[telstar]

Arnelgp - it's in the T-Chartpositions table but I need to extract it in a query. I am trying to get something looking like the attached, I can work out Entry Position (First of SongPosition), High Position (Min of SongPosition) Weeks in Chart (Count of Song Position) - but stuck on Previous Weeks Position.

 

[June7]

Recommend you change those hyphens in table names to underscores. I NEVER build lookups in tables. I would probably save date instead of ChartDateID.

Allen Browne's nested TOP 1 would pull the previous position but won't care if it was last week or 20 weeks ago.

Consider this:

Query1: qryCalcLastWeek
SELECT [T-ChartPositions].ChartPostitionID, [T-ChartPositions].Song_ID, [T-ChartPositions].SongPosition, T_ChartDates.Chart_Date, [Chart_Date]-7 AS LastWeek
FROM T_ChartDates INNER JOIN [T-ChartPositions] ON T_ChartDates.ChartdateID = [T-ChartPositions].SongPosn_date;

Query2: qryPosDate
SELECT [T-ChartPositions].ChartPostitionID, [T-ChartPositions].Song_ID, [T-ChartPositions].SongPosition, T_ChartDates.Chart_Date
FROM T_ChartDates INNER JOIN [T-ChartPositions] ON T_ChartDates.ChartdateID = [T-ChartPositions].SongPosn_date;

Query3:
SELECT qryCalcLastWeek.ChartPostitionID, qryCalcLastWeek.Song_ID, qryCalcLastWeek.SongPosition, qryCalcLastWeek.Chart_Date, qryCalcLastWeek.LastWeek, qryPosDate.SongPosition, qryPosDate.Chart_Date
FROM qryPosDate INNER JOIN qryCalcLastWeek ON (qryPosDate.Chart_Date = qryCalcLastWeek.LastWeek) AND (qryPosDate.Song_ID = qryCalcLastWeek.Song_ID)
ORDER BY qryCalcLastWeek.Song_ID;

 

[telstar]

Thanks June7 - will take on board your comments and I will have a play around with your code and let you know how I get on.

 

[arnelgp]

the first 2, i don't know.

 

[telstar]

Hey, thanks arnelgp, that looks to be doing what I wanted. I'll have a play around and let you know how I get on

 

[telstar]

Morning all. Sorry I have not replied in this thread as I have been away for a week. Just to let you know that I adapted arnelgp's code slightly which now gives me all of the information that I needed. Thanks to everyone for your help.