Calculating a difference between two values in a table.

[exRP12Nuke]

Good Afternoon,

This problem would seem to be a simple calculation, but I am finding that it is proving most difficult.

I have an access table that I need to find the total hours on a component by subtracting two different values in two different rows.

If this was only going to be a one time calculation it would be simple, but I have thousands of records that this calculation needs to be completed on, and more in the future.

If my table headers are CUNO (customer number), EQMFSN (Equipment Serial Number), SVMTHR (Service Meter Hours), CPTCD (Component Code), CPTMD3 (Modifier), WONO (WorkOrder), WOSGNO (Segment), JBCD (JobCode), OPNDT8 (OpenDate), and IVDT8 (Invoiced Date), what VBA code do I need to use to subtract SVMTHR from another when EQMFSN, CPTCD, and CPTMD3 are equal?

Thanks!

 

[dhookom]

Please take the time to give us a few sample records and desired output. We don't need any of the fields that have no significance to the question.

Duane
Hook'D on Access
MS Access MVP

 

[exRP12Nuke]

dhookom,

Here is an Excel representation of what I am trying to accomplish.

Let me know if you need more info.

Thanks!

 

[exRP12Nuke]

Let's try this again...

SVMTHR	CPTCD	CPTMD3	OPNDT8	Desired Result:
54938	7201	LR	20080630	16296
38642	7201	LR	20080316	12883
25759	7201	LR	20060228	12505
13254	7201	LR	20030131	0
54938	7201	RF	20080630	16296
38642	7201	RF	20080316	12883
25759	7201	RF	20060228	12505
13254	7201	RF	20030131	0
54938	1000		20080630	16296
38642	1000		20080316	12883
25759	1000		20060228	12505
13254	1000		20030131	0
54938	3101		20080630	16296
38642	3101		20080316	12883
25759	3101		20060228	12505
13254	3101		20030131	0
99999999

Where the desired result is the difference between the two SVMTHR values.

 

[PHV]

No VBA required:

SELECT A.SVMTHR, A.CPTCD, A.CPTMD3, A.OPNDT8, A.SVMTHR-Max(B.SVMTHR) AS Desired
FROM theTable AS A
LEFT JOIN theTable AS B ON A.CPTCD=B.CPTCD AND A.CPTMD3 & ''=B.CPTMD3 & '' AND A.OPNDT8>B.OPNDT8
GROUP BY A.SVMTHR, A.CPTCD, A.CPTMD3, A.OPNDT8
ORDER BY 2,3,4 DESC,1 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[exRP12Nuke]

PHV, that is providing close to what I need, my SQL skills are worse than my Access skills so I am having a hard time modifying your bit of code to show what I need. There are a few more columns that need to be shown in the result, and everytime I try to add them I get an error message.

Here is a full table of what I am trying to do.

EQMFSN	SVMTHR	CPTCD	CPTMD3	WONO	WOSGNO	OPNDT8	IVDT8	CompLife:
1	54938	7201	LR	GT11111	1	20080630	20080815	16296
1	38642	7201	LR	GT22222	32	20080316	20080615	12883
1	25759	7201	LR	GT33333	55	20060228	20060422	12505
1	13254	7201	LR	GT44444	8	20030131	20030322	13254
3	54938	7201	RF	CP11111	10	20080630	20080815	16296
3	38642	7201	RF	CP22222	11	20080316	20080615	12883
3	25759	7201	RF	CP33333	15	20060228	20060422	12505
3	13254	7201	RF	CP44444	18	20030131	20030322	13254
45	54938	1000		CY11111	22	20080630	20080815	16296
45	38642	1000		CY22222	34	20080316	20080615	12883
45	25759	1000		CY33333	71	20060228	20060422	12505
45	13254	1000		CY44444	19	20030131	20030322	13254
33	54938	3101		RS11111	2	20080630	20080815	16296
33	38642	3101		RS22222	52	20080316	20080615	12883
33	25759	3101		RS33333	16	20060228	20060422	12505
33	13254	3101		RS44444	78	20030131	20030322	13254

The columns are off a bit in this window, but these are all of the headers that I need some help with.

Thanks!

 

[PHV]

What about this ?

SELECT A.EQMFSN,A.SVMTHR,A.CPTCD,A.CPTMD3,A.WONO,A.WOSGNO,A.IVDT8,A.OPNDT8,A.IVDT8,A.SVMTHR-Nz(Max(B.SVMTHR),0) AS CompLife
FROM theTable AS A
LEFT JOIN theTable AS B ON A.EQMFSN=B.EQMFSN AND A.OPNDT8>B.OPNDT8
GROUP BY A.EQMFSN,A.SVMTHR,A.CPTCD,A.CPTMD3,A.WONO,A.WOSGNO,A.IVDT8,A.OPNDT8,A.IVDT8
ORDER BY A.EQMFSN,A.OPNDT8 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[exRP12Nuke]

That is pulling everything that I need it to now. I think that I just need to sort my actual table differently for the results to be accruate.

Thanks for all of your help!