Weekly snapshots converted to month

[accessliz]

Hi

i have a dataset containing snotshots run every friday; But its getting too big to churn in excel in my dashboard so i want to add some criteria to my query which picks out the latest friday/date of each month?

i am at a complete pickle on how to do this, as it would have been easier if i had every day in there to find the last day of the month, but i just need to find the last reported snapshot of the month?
if that makes sense?

thanks for you help

 

[plog]

You didn't provide any table/field names so I can only help you in general. Lets say you have a date field called SnapshotDate in a table called SnapshotData. Then to find the last SnapshotDate of each month, you would create a sub-query like so:

SELECT MAX(SnapshotDate) AS LastSnapshot
FROM SnapshotData
GROUP BY  Month(SnapshotDate), Year(SnapshotDate)

Save that and then use that sub-query in your existing query, linking it via LastSnapshot to SnapshotDate.

 

[accessliz]

wow thanks, very fast

my table is called
RTTsnapshots

and my field is called
DownloadDate

but i have just overwritten your fields in brackets with mine this has worked brilliantly! but the years dont make sense and it hasnt gone and further back than 12 months?

thanking you kindly

Query1LastSnapshotSnapshotMonthSnapshotYear


30/01/20151189927/02/20152190027/03/20153190024/04/20154190029/05/20155190026/06/20156190031/07/20157190028/08/20158190025/09/20159190030/10/201510190020/11/201511190031/12/2014121900

 

[accessliz]

I am quite happy to look at a years worth anyway! just to get a monthly trend.
and im not interested in the year column; although it is a nice bit of code

So thank you for all your help! - i can get what i need from this perfectly

 

[plog]

but the years dont make sense and it hasnt gone and further back than 12 months?

'but' isn't a word that usually starts sentences that are questions. From the rest of it, I am unable to determine if or what you are asking.

There's no WHERE clause in that query, so my SQL will provide you with records for every Month/Year in RTTsnapshots.

 

[accessliz]

sorry if i made no sense ( i do that a lot) I have re-looked at it this morning and I had inputted the fields incorrectly; I had left year(snapshotmonth) in rather than changing that part to year(DownloadDate) - once I viewed it in design view it made sense. (I really need some training in SQL)
thank you for your help you have been most helpful - it is absolutely perfect