I presume your dates are real dates and not the mm/dd formatted version. Otherwise you will have a lot more work to convert them to real dates
first question has to be - do you have any gaps in your dates in your real data?
second question - you appear to looking for a moving total.
So assuming you have real dates (with no time element hidden by formatting), no gaps in dates and needing a moving total
to get a list of dates either create a table or use this query
Code:
SELECT DISTINCT ReportDate
FROM myTable
call this QryDates
next create a query to sum the last 7 days
Code:
SELECT qryDates.ReportDate, sum(Cases) as Current7
FROM myTable INNER JOIN qryDates ON myTable.ReportDate BETWEEN qryDates.ReportDate and qryDates.ReportDate-7
GROUP BY qryDates.ReportDate
call this qryCurrent
finally do the same for the previous
Code:
SELECT qryDates.ReportDate, sum(Cases) as Previous7
FROM myTable INNER JOIN qryDates ON myTable.ReportDate BETWEEN qryDates.ReportDate-1 and qryDates.ReportDate-8
GROUP BY qryDates.ReportDate
call this qryPrevious
Note the non standard joins in the last 2 queries cannot be shown in the query builder, they can only be built in the sql builder. See example below for the cartesian equivalent - but is likely to be slower
now you can put a query together to join these 3 queries on reportDate
Code:
SELECT qryDates.ReportDate, Current7, Previous7
FROM (qryDates INNER JOIN qryCurrent ON qryDates.ReportDate=qryCurrent.ReportDate) INNER JOIN qryPrevious ON qryDates.ReportDate=qryPrevious.ReportDate
WHERE qryDates.ReportDate>(SELECT min(ReportDate)+8 FROM myTable)
the criteria in this query is to prevent reporting part weeks at the earliest part of your dataset
cartesian equivalent but likely to be slower
Code:
SELECT qryDates.ReportDate, sum(Cases) as Previous7
FROM myTable, qryDates
WHERE myTable.ReportDate BETWEEN qryDates.ReportDate-1 and qryDates.ReportDate-8
GROUP BY qryDates.ReportDate