SQL Query Help

[springboardjg]

Hi



I have the following table (see screenshot attached) - I couldn't work out how to insert seperate columns using the table tags to post the table in the actual post, help on this would also be appreciated.

The 'HSI Months' have a number value to save space eg. January is 1, February is 2 etc.

I need a query that returns the 'Weekly Average' for each 'Site Serial' for the current month and the previous month (in seperate columns). But only to return a row if the current month and previous month IS NOT BLANK.

The end goal is to have a form that runs this query where the user selects the month (in a given year) they want to analyse, so the months that would be returned would be current month and current month-1. If this could be considered in the SQL query it would be great.


Thanks

James

[jzwp11]

You will need a nested query to accomplish what you want to do

Something along these lines:

SELECT tbl_SiteSerialsAndWeeklyAverages.[Site Serial], tbl_SiteSerialsAndWeeklyAverages.[HSI Month], tbl_SiteSerialsAndWeeklyAverages.[HSI Year], tbl_SiteSerialsAndWeeklyAverages.[Weekly Average] as ThisMonth, (SELECT TOP 1 Q1.[Weekly Average] FROM tbl_SiteSerialsAndWeeklyAverages as Q1 WHERE Q1.[Site Serial]=tbl_siteSerialsAndWeeklyAverages.[Site Serial] AND dateserial(tbl_SiteSerialsAndWeeklyAverages.[HSI Year],tbl_SiteSerialsAndWeeklyAverages.[HSI Month],1)>dateserial(Q1.[HSI Year],Q1.[HSI Month],1) order by Q1.[Site Serial], dateserial(Q1.[HSI Year],Q1.[HSI Month],1) desc ) as LastMonth
FROM tbl_SiteSerialsAndWeeklyAverages
ORDER BY tbl_SiteSerialsAndWeeklyAverages.[Site Serial],dateserial(tbl_SiteSerialsAndWeeklyAverages.[HSI Year],tbl_SiteSerialsAndWeeklyAverages.[HSI Month],1) desc ;