Calculate the value difference between two consecutive records in my subform

[Takiman]

Hi how do I calculate the difference between two consecutive records?
Both consecutive record values are shown in my subform (continuous form view) linked to my mainform by AuditID. I have also limited the subform to only allow two records to be created per mainform record. This is as per my requirements, so I would only need to find the difference between two values per Mainform record.

Below shows the source table for my subform. Therefore, based on my requirements I would need my first calculation to be: $50,000-$20,000 =$30,000. And therefore, my second calculation would be $15,000-$35,000 = -$20,000.. and so on.



Any help with trying to work this out would be great. Thanks.

tblAuditCost

AuditCostID	AuditID	CostID	CostValue
1	1	1	$20,000
2	1	4	$50,000
3	2	9	$35,000
4	2	7	$15,000

[andy49]

Grouped query using audit id? And difference between max and min?


Sent from my iPhone using Tapatalk

[andy49]

Or dmax - dmin functions where auditid = 2


Sent from my iPhone using Tapatalk

[Takiman]

Or dmax - dmin functions where auditid = 2
Sent from my iPhone using Tapatalk

thanks for advice andy. I am new to programming in access so any help with scripts would be appreciated. thanks.

[andy49]

If you had this in a textbox then maybe

Dmax("yourfield","yourtable", "where auditid = 1") -dmin( "yourfield","yourtable","where auditId = 1")

might get you started. Yourfield is costValue and table is tblauditcost.


Sent from my iPhone using Tapatalk

[Micron]

Based on your two examples, it appears that you want to subtract the first record value FROM the second, so using the min/max of the AuditID field won't give you the order I think you're after. If you include the AuditCostID in the subform records (assuming this is a subform datasheet) then either DLookup or DMax in a calculated control might be possible. I think the solution depends on what the subform is based on. If that is a table, then there's more records to deal with than if it is a query that only returns two records. If it's a table, a function would be one way to filter the records as well as place them in the correct order for subtraction. This would be far easier if you only wanted the difference and didn't care about the order. I'm also assuming what you have is a subform datasheet.

[andy49]

If order matters whatabout group query then use first and last rather than min and max?


Sent from my iPhone using Tapatalk

[Takiman]

Ok thanks Andy and Micron for advice I will give it go tmw eve and let you know if i can get it to work -- I had some pc h/w problems today so have been offline :-(
and Micron yes my Subform feeds into a table (tblAuditCost) so there would be more than two records as each Audit will creates two records which are added to the subform table. The PK of that subform table is AuditCostID, however, the AuditID number will be the same for each of the two records created, so was thinking I would just need to work out the difference between AuditID's that are equal in value, and as you have aptly suggest subtract the first record value FROM the second (so the logic would be (based on my example): AuditCostID 2 (CostID 4 CostValue) subtract AuditIDCost 1 (CostID 1 CostValue), for AuditID 1. Again I will have a look tmw eve ,thanks for tips so far

tblAuditCost

AuditCostID	AuditID	CostID	CostValue
1	1	1	$20,000
2	1	4	$50,000
3	2	9	$35,000
4	2	7	$15,000

[Missinglinq]

Allen Browne gives an example of doing just this, using a Subquery, here

http://allenbrowne.com/subquery-01.html

Go down to the heading

Get the value in another record

I suppose you may be able to base the Subform on such a Subquery to accomplish your goal.

Linq ;0)>