Solved Help with figuring difference between amounts from current and previous date

[LALovelady]

I am not a programmer and assume this needs some coding. But if there is a way to run a query please advise.

ScreenIN and ScreenOUT amounts are entered.
ScreenINDiff is the difference between the current date ScreenIN and the previous date ScreenIN
Same with ScreenOUTDiff (difference between current date ScreenOUT and previous date ScreenOUT)

Also, the first entry will not have ScreenINDiff or ScreenOUTDiff.

Thanks so much!!

Date	ScreenIN	ScreenOUT	ScreenINDiff	ScreenOUTDiff
10/14/2020​	2543​	2281​
10/19/2020​	3610​	2962​	1067​	681​
10/26/2020​	6671​	5411​	3061​	2449​
11/4/2020​	9185​	7251​	2514​	1840​
11/9/2020​	11697​	9270​	2512​	2019​
11/16/2020​	15092​	11910​	3395​	2640​
11/22/2020​	17043​	13382​	1951​	1472​
11/30/2020​	19978​	15700​	2935​	2318​
12/9/2020​	23106​	18190​	3128​	2490​
12/14/2020​	24250​	19078​	1144​	888​

 

[theDBguy]

Hi. Perhaps something like?

ScreenIN-(SELECT T1.ScreenIN FROM TableName T1 
  WHERE T1.[Date]=(SELECT Max(T2.Date) FROM TableName T2 
    WHERE T2.[Date]<TableName.[Date])) AS ScreenINDiff

(untested)

 

[LALovelady]

I'm attaching the database. I added the above code to Modul1 but am pretty clueless as to the best way to get it to work with my tblMetersTest. I think once I understand how the code works for ScreenIn/Out I can then create the one for the other 2 fields (MeterIn/Out).

 

[theDBguy]

Hi. The tblMetersTest table has a Asset# column. Does that mean you want to subtract the previous day's ScreenIn based on the Asset#?

 

[LALovelady]

Hi. The tblMetersTest table has a Asset# column. Does that mean you want to subtract the previous day's ScreenIn based on the Asset#?

Yes. I guess I need more help with this than I first thought. I want to run a report based on the Asset# that will figure the difference between the previous date's ScreenIn and the current date's ScreenIn and do the same for MeterIn and MeterOut. Then I'll compare the difference between the 2 (ScreenInDiff vs. MeterInDiff and ScreenOutDiff vs. MeterOutDiff) to see if they equal. However, I thought if someone could help me with the code for the first part, I can try to muddle through the rest. But, hey, if you are just dying to do it for me then I would be forever grateful!

 

[theDBguy]

Yes. I guess I need more help with this than I first thought. I want to run a report based on the Asset# that will figure the difference between the previous date's ScreenIn and the current date's ScreenIn and do the same for MeterIn and MeterOut. Then I'll compare the difference between the 2 (ScreenInDiff vs. MeterInDiff and ScreenOutDiff vs. MeterOutDiff) to see if they equal. However, I thought if someone could help me with the code for the first part, I can try to muddle through the rest. But, hey, if you are just dying to do it for me then I would be forever grateful!

Hi. I thought so, thanks for confirming that. Please take a look at the query I created for you.

 

[LALovelady]

You have made my day!!!

 

[theDBguy]

You have made my day!!!

Glad we could assist. Good luck with the rest of your project.