UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Get Record Difference, Access 2010    
 
   
Ayiramala
post Mar 26 2020, 11:09 AM
Post#1



Posts: 155
Joined: 16-December 14
From: Kerala, India


Hi,

Hope you are all keeping fine.

I have just made a database to track the number of Covid cases in my area, and came up with this problem: I want to get the number of new cases in each place. That is, the difference between two successive records. This is what I tried:

DiffInfected: [Infected]-Nz(DMax("Infected","tblData","(Infected <" & [Infected] & ")"),0)

This gave partial success. But I couldn't 'limit' the records to a particular place.

Tried this:

DiffInfected: [Infected]-Nz(DMax("Infected","tblData","(Infected <" & [Infected] & ") and (PlaceName = '" & [PlaceName] & "')"),0)

But it doesn't work.

What am I doing wrong?

As always, thanks for all the help.
Go to the top of the page
 
GroverParkGeorge
post Mar 26 2020, 11:23 AM
Post#2


UA Admin
Posts: 37,237
Joined: 20-June 02
From: Newcastle, WA


You need a better way to identify "the most recent" and the "previous" records.

Records have no inherent sort order, so "successive" records can change depending on the sort order applied.

Here, I assume the relevant sort will be on date. I.e. you want to compare "today's" cases with "yesterday's" cases. If you mistakenly use DMax() on an unsorted table, you'll always get the single HIGHEST infected number, whether that occurs today, yesterday or any other date. It is possible, is it not, that the number of infected will eventually start to go down again?




--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
theDBguy
post Mar 26 2020, 11:28 AM
Post#3


UA Moderator
Posts: 78,090
Joined: 19-June 07
From: SunnySandyEggo


Hi. Not sure if this will help, but maybe give it a try?

The Opposite of DSum()

Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Mar 26 2020, 11:31 AM
Post#4


UA Admin
Posts: 37,237
Joined: 20-June 02
From: Newcastle, WA


I just ran into an additional complication while putting in some sample data for testing.

You are assuming that the difference between two dates will represent "new cases". However, I wonder if that's valid because of two other factors.

  • Deaths
  • Recoveries


If you have 50 infected people on one day and 49 on the next day, does that mean one person recovered or that one person died? Or does it mean that the 49 are all new cases?

In other words, we need to step back and think carefully about how the data is defined for entry.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Ayiramala
post Mar 26 2020, 11:42 AM
Post#5



Posts: 155
Joined: 16-December 14
From: Kerala, India


Thanks for the quick reply. In addition to 'Infected', I have 'Recovered' and 'Dead', but I have mentioned only one of them. I thought I could use the same approach for the remaining ones too.

Or is that the right thing to do?
Go to the top of the page
 
GroverParkGeorge
post Mar 26 2020, 02:43 PM
Post#6


UA Admin
Posts: 37,237
Joined: 20-June 02
From: Newcastle, WA


That should work with those additional statuses.

The question then.

Here's a potential solution, based on my understanding of what should be involved.

Attached File  InfectedData.jpg ( 70.13K )Number of downloads: 1


Attached File  QueryInfectedSequence.jpg ( 216.49K )Number of downloads: 3


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Mar 26 2020, 06:57 PM
Post#7


UA Admin
Posts: 37,237
Joined: 20-June 02
From: Newcastle, WA


In proposing the solution above, I made the assumption that there will be one record per date, with no gaps. If there are missing dates in your data a different and more complex solution would be required to create the sequence that can be compared between "current" and "prior" .

Although this solution did not account for the possibility of other statuses, it could be adapted easily enough to filter on the relevant, "Infected" status.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Ayiramala
post Mar 28 2020, 08:59 PM
Post#8



Posts: 155
Joined: 16-December 14
From: Kerala, India


Hi,

I am sorry to say that somehow, I could not implement the method you suggested. But I found this (kb210504), which worked for me.

For some reasons Microsoft has removed it from their Knowledge Base, but I could find it elsewhere.

Thanks a lot for spending your time. Much appreciated.
Go to the top of the page
 
GroverParkGeorge
post Mar 29 2020, 07:24 AM
Post#9


UA Admin
Posts: 37,237
Joined: 20-June 02
From: Newcastle, WA


Congratulations on solving the problem.

Continued success with the project.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 02:45 AM