Results 1 to 12 of 12
  1. #1
    DBS1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    47

    Adding value from previous row on query

    I’ve got an Access database I’m building trying to track Covid cases. The table has fields called Report Date, Locality, and Cases. I’m trying to create a query that will show all the total cases for the past 7 days for each day and total for the previous day. Even better would be to have the daily difference in place of the prior day, which is what I’m really after. If I could get it to look like this, I can figure the rest out.
    Report Date Cases Prior Day
    11/17 215,000 213,500
    11/16 213,500 211,000
    11/15 211,000 209,500
    11/14 209,500 208,500


    11/13 208,500 207,000
    11/12 207,000 205,000
    11/11 205,000 203,500
    11/10 203,500 Null

    This will be used to build a line chart, and perhaps a report. If I was just doing a report, I’d be able to use the query I have now, which shows the first two columns like I need them. I was able to get that query by grouping on the Report Date column, which has criteria “Between Date() and Date()-7”, not including the Locality field in the query, and by summing the Cases field. This gives me the query with the total number of cases per day, but I can’t figure out how to get the prior day’s cases as a third field on the same row. I’ve found the Allen Browne solution, which adds a sub query to get the third field, but applying that gives me a message saying something like it can only return one record. I don’t have the database on this computer, so the Cases numbers are not actual, but representative. If anyone can walk me through this or post what I need to make this happen so that I can change the field names, I would be very appreciative.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    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

  3. #3
    DBS1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    47
    Thanks Ajax. I'll try it when I get back to work next week.

  4. #4
    DBS1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    47
    Ajax, I tried using your instructions but ran into problems. The table is imported daily from a spreadsheet, which has a long default name. To simplify the query building, I built a query that mirrors the table data and renames some of the fields to more user friendly names. I named the query as tblData, although it is really a query. I was able to build the qryDates query, but when I built the query for qryCurrent, I used the following code:

    SELECT qryDates.ReportDate, sum(Cases) as Current7
    FROM tblData INNER JOIN qryDates ON tblData.ReportDate BETWEEN qryDates.ReportDate and qryDates.ReportDate-7
    GROUP BY qryDates.ReportDate

    When I run it, I get the following error - "Between operator without And in query expression 'tblData.ReportDate BETWEEN qryDates.ReportDat'.

    I created a similar query with the following code, which gives me the daily sums by date:

    SELECT qryDates.ReportDate, Sum(tblData.Cases) AS CasesCur
    FROM tblData INNER JOIN qryDates ON tblData.ReportDate = qryDates.ReportDate
    GROUP BY qryDates.ReportDate
    HAVING (((qryDates.ReportDate) Between Date() And Date()-7))
    ORDER BY qryDates.ReportDate DESC;

    I created a similar query for the previous 7 using Between Date()-1 And Date()-8, which gives me the previous dates (starting with the date before). The names of these queries are qrySWcurr and qrySWprev.

    I created a query, named qrySWsumm to combine them using the code you provided, shown below.

    SELECT qryDates.ReportDate, qrySWcurr.CasesCur, qrySWprev.CasesPrev
    FROM (qryDates INNER JOIN qrySWcurr ON qryDates.ReportDate = qrySWcurr.ReportDate) INNER JOIN qrySWprev ON qryDates.ReportDate = qrySWprev.ReportDate
    WHERE qryDates.ReportDate>(SELECT min(ReportDate)+8 FROM tblData)
    ORDER BY qryDates.ReportDate DESC;

    This gives me the following results:

    ReportDate CasesCur CasesPrev
    12/2/2020 242480 242480
    12/1/2020 240063 240063
    11/30/2020 237835 237835
    11/29/2020 235942 235942
    11/28/2020 233617 233617
    11/27/2020 230444 230444
    11/26/2020 228900 228900

    My goal is to have CasesPrev on the first row show 240063. Could it be an issue with the date format that you mentioned?

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Your are joining on the same dates so you're getting the same results. In the qryDates add a field called PreviousDate:
    SELECT DISTINCT ReportDate, [ReportDate] - 1 As PreviousDate FROM tblData;

    Now in your summary query join the previous query on this new field:
    SELECT qryDates.ReportDate, qrySWcurr.CasesCur, qrySWprev.CasesPrev
    FROM (qryDates INNER JOIN qrySWcurr ON qryDates.ReportDate = qrySWcurr.ReportDate) INNER JOIN qrySWprev ON qryDates.PreviousDate= qrySWprev.ReportDate
    WHERE qryDates.ReportDate>(SELECT min(ReportDate)+8 FROM tblData)
    ORDER BY qryDates.ReportDate DESC;


    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    thought you could have a non standard join using between, but would appear not

    try these two alternatives (based on the previous cases query)

    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
    Code:
    SELECT qryDates.ReportDate, sum(Cases) as Previous7
    FROM myTable INNER JOIN  qryDates ON myTable.ReportDate <= qryDates.ReportDate-1 and myTable.ReportDate >= qryDates.ReportDate-8
    GROUP BY qryDates.ReportDate
    On reflection, also check the number of days - it might be returning 8 days

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    perhaps another way to do this which may be simpler

    Code:
    SELECT qryDates.ReportDate, 
    sum(cases * abs(myTable.ReportDate BETWEEN qryDates.ReportDate and qryDates.ReportDate-7)) as Current7,
    sum(Cases * abs(myTable.ReportDate BETWEEN qryDates.ReportDate-1 and qryDates.ReportDate-8)) as Previous7
    FROM myTable, qryDates
    GROUP BY qryDates.ReportDate
    WHERE qryDates.ReportDate>(SELECT min(ReportDate)+8 FROM tblData)
    ORDER BY qryDates.ReportDate DESC;

  8. #8
    DBS1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    47
    Gicu - that did the trick. Thanks to both you and Ajax for your suggestions. I knew it would be something simple.

  9. #9
    DBS1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    47
    Ok guys, one more question. I was hoping to subtract CasesPrev from CasesCurr to get the amount of increase in one day. I created a new query where I selected ReportDate, CasesCur and CasesPrev, and tried to create a new field using the following code:

    sum([CasesCur]-[CasesPrev])

    I get an error saying "Your query does not include the specified expression 'ReportDate' as part of an aggregate function."

    What is the best way to accomplish what I'm trying to get? This daily difference was the only reason I was trying to get the previous day count on the same row.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you show your SQL for the last one? If it is a totals query make sure you change totals group to Expression instead of GroupBy.
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    DBS1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    47
    That did it Gicu. Thanks again. Greatly appreciated.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome, but I think Ajax did most of the work .

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 04-16-2020, 10:25 AM
  2. Replies: 3
    Last Post: 07-01-2016, 08:11 AM
  3. Replies: 2
    Last Post: 11-19-2013, 01:43 PM
  4. Replies: 8
    Last Post: 07-18-2012, 11:11 AM
  5. Replies: 6
    Last Post: 05-12-2012, 03:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums