Solved Find last but one date (1 Viewer)

telstar

New member
Local time
Today, 18:10
Joined
Sep 3, 2021
Messages
7
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

Screenshot 2021-09-04 103521.jpg


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.

Screenshot 2021-09-04 104319.jpg


Thank you - any help appreciated
 

telstar

New member
Local time
Today, 18:10
Joined
Sep 3, 2021
Messages
7
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

Super Moderator
Staff member
Local time
Today, 14:10
Joined
Jan 23, 2006
Messages
15,364
Can you post a copy of your database? We only need a sample of the data and any code you have.
 

telstar

New member
Local time
Today, 18:10
Joined
Sep 3, 2021
Messages
7
Sure, thanks jdraw. Database with sample data attached.
 

Attachments

  • telstar_040921.accdb
    544 KB · Views: 337

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:10
Joined
May 7, 2009
Messages
19,169
you already have it in your form?
 

Attachments

  • telstar_040921.accdb
    500 KB · Views: 361

telstar

New member
Local time
Today, 18:10
Joined
Sep 3, 2021
Messages
7
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.
 

Attachments

  • IMG_20210904_0002.pdf
    842.3 KB · Views: 365

June7

AWF VIP
Local time
Today, 10:10
Joined
Mar 9, 2014
Messages
5,423
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;
 
Last edited:

telstar

New member
Local time
Today, 18:10
Joined
Sep 3, 2021
Messages
7
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:10
Joined
May 7, 2009
Messages
19,169
the first 2, i don't know.
 

Attachments

  • telstar_040921.accdb
    1.2 MB · Views: 270

telstar

New member
Local time
Today, 18:10
Joined
Sep 3, 2021
Messages
7
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

New member
Local time
Today, 18:10
Joined
Sep 3, 2021
Messages
7
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.
 

Users who are viewing this thread

Top Bottom