Compute change in Fields from Previous day

[Day Falde via AccessMonster.com]

I have a table which has the following data:

I need to group each Directory(chkpntdisk, dske, etc) and then calculate
the the difference in blocks, files, and directories from the previous day.
I have a date field and I have fields to hold the changes. How would I do
this?


Date_File_Name Dir_File_Name Number_of_Dir Change_In_Number_Dir
Number_of_Files Change_In_Number_Files Number_of_Blocks
Change_In_Number_Blocks
4/13/2005 CHKPNTDISK 3 0 146 0 1983747 0
4/1/2005 CHKPNTDISK 3 0 143 0 2107250 0
4/17/2005 CHKPNTDISK 3 0 147 0 2128817 0
4/16/2005 CHKPNTDISK 3 0 147 0 2128886 0
4/14/2005 CHKPNTDISK 3 0 146 0 2024541 0
4/18/2005 CHKPNTDISK 3 0 147 0 2121818 0
4/12/2005 CHKPNTDISK 3 0 146 0 1980216 0
4/11/2005 CHKPNTDISK 3 0 146 0 1979195 0
4/10/2005 CHKPNTDISK 3 0 146 0 1981704 0
4/4/2005 CHKPNTDISK 3 0 145 0 1901584 0
4/8/2005 CHKPNTDISK 3 0 146 0 1978918 0
4/7/2005 CHKPNTDISK 3 0 146 0 1964595 0
4/6/2005 CHKPNTDISK 3 0 146 0 1963580 0
4/3/2005 CHKPNTDISK 3 0 144 0 2103924 0
4/5/2005 CHKPNTDISK 3 0 146 0 1957128 0
4/9/2005 CHKPNTDISK 3 0 146 0 1981988 0
4/15/2005 CHKPNTDISK 3 0 146 0 2028000 0
4/2/2005 CHKPNTDISK 3 0 143 0 2111843 0
4/11/2005 DSKE 95 0 2683 0 5272885 0
4/12/2005 DSKE 95 0 2693 0 5273348 0
4/13/2005 DSKE 96 0 2737 0 5276003 0
4/14/2005 DSKE 96 0 2755 0 5279535 0
4/15/2005 DSKE 96 0 2872 0 5352261 0
4/17/2005 DSKE 98 0 2913 0 5356957 0
4/10/2005 DSKE 95 0 2676 0 5274190 0
4/16/2005 DSKE 98 0 2913 0 5358589 0
4/9/2005 DSKE 95 0 2677 0 5276713 0
4/8/2005 DSKE 94 0 2642 0 5260692 0
4/7/2005 DSKE 94 0 2619 0 5260636 0
4/6/2005 DSKE 94 0 2599 0 5259649 0
4/5/2005 DSKE 93 0 2555 0 5247231 0
4/4/2005 DSKE 93 0 2526 0 5241826 0
4/3/2005 DSKE 93 0 2518 0 5241806 0
4/1/2005 DSKE 92 0 2480 0 5238323 0
4/18/2005 DSKE 98 0 2921 0 5356973 0
4/2/2005 DSKE 93 0 2516 0 5245407 0
4/13/2005 PLUSPROD 76 0 14875 0 4046154 0
4/1/2005 PLUSPROD 76 0 13889 0 4059162 0
4/2/2005 PLUSPROD 76 0 13908 0 4061286 0
4/17/2005 PLUSPROD 76 0 15328 0 4202250 0
4/16/2005 PLUSPROD 76 0 15243 0 4197403 0
4/14/2005 PLUSPROD 76 0 14994 0 4085142 0
4/18/2005 PLUSPROD 76 0 15399 0 4207595 0
4/12/2005 PLUSPROD 76 0 14825 0 4033476 0
4/11/2005 PLUSPROD 76 0 14725 0 4026596 0
4/3/2005 PLUSPROD 76 0 13989 0 3884592 0
4/9/2005 PLUSPROD 76 0 14568 0 4014900 0
4/8/2005 PLUSPROD 76 0 14518 0 3995038 0
4/7/2005 PLUSPROD 76 0 14364 0 3976451 0
4/6/2005 PLUSPROD 76 0 14244 0 3968941 0
4/5/2005 PLUSPROD 76 0 14183 0 3949124 0
4/4/2005 PLUSPROD 76 0 14060 0 3890003 0
4/10/2005 PLUSPROD 76 0 14653 0 4021424 0
4/15/2005 PLUSPROD 76 0 15169 0 4098614 0
4/10/2005 SIDATA 3 0 101 0 1196774 0
4/11/2005 SIDATA 3 0 101 0 1196774 0
4/12/2005 SIDATA 3 0 102 0 1197472 0
4/13/2005 SIDATA 3 0 101 0 1197471 0
4/14/2005 SIDATA 3 0 103 0 1232646 0
4/15/2005 SIDATA 3 0 103 0 1232646 0
4/16/2005 SIDATA 3 0 103 0 1232646 0
4/9/2005 SIDATA 3 0 101 0 1196774 0
4/17/2005 SIDATA 3 0 101 0 1317729 0
4/8/2005 SIDATA 3 0 103 0 1196690 0
4/7/2005 SIDATA 3 0 102 0 1185231 0
4/6/2005 SIDATA 3 0 101 0 1185232 0
4/5/2005 SIDATA 3 0 102 0 1172487 0
4/4/2005 SIDATA 3 0 101 0 1126058 0
4/3/2005 SIDATA 3 0 101 0 1126058 0
4/2/2005 SIDATA 3 0 101 0 1301782 0
3/31/2005 SIDATA 3 0 107 0 1302679 0
4/18/2005 SIDATA 3 0 101 0 1317729 0
4/1/2005 SIDATA 3 0 107 0 1302679 0

 

[John Vinson]

I have a table which has the following data:

I need to group each Directory(chkpntdisk, dske, etc) and then calculate
the the difference in blocks, files, and directories from the previous day.
I have a date field and I have fields to hold the changes. How would I do
this?

A "SELF JOIN" query should help here. For (literally) the preceding
day, create a query by adding your table to the query grid *twice*.
Join the two instances by the Dir_File_Name field; Access will append
a _1 to the name of the table in the second instance. Put a criterion
on table_1.Date_File_Name of

= DateAdd("d", -1,

.[Date_File_Name])

using your table names of course.

You can then get both Blocks fields in the same record, and can use an
expression

BlocksChange: [table_1].[Blocks] -

.[Blocks]

to calculate the change.

John W. Vinson[MVP]