Calculation - Subtraction 1

[jammerdk]

Hi Guys

I'm trying to create at query that subtracts 2 values from 2 different queries get a calculated value.

My Data are as folow

OD1 : 20 ID1 : 10
OD2 : 20 ID2 : 5
OD3 : 15 ID3 : 5
OD4 : 10 ID4 : 10
OD5 : 15 ID5 : 5

I've tried to create a query that does subtract the values

SELECT Right(QryGetOD.Measuring,1) AS Nr, [QryGetOD].[Value]-[QryGetID].[Value] AS W
FROM QryGetOD, QryGetID;

But instaid of 5 values I get 25 values.

(seems like the subtractions are done 5 * 5)

But want to subtract OD1 with ID1 and so on

 

[PHV]

SELECT Mid(O.Measuring,3) AS Nr, O.Value-I.Value AS W
FROM QryGetOD O, QryGetID I
WHERE Mid(O.Measuring,3)=Mid(I.[name of ID field],3)

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

 

[jammerdk]

Thanks PHV....have a star ....always nice to explore new things

 

[jammerdk]

SELECT Mid(O.Measuring,3) AS Nr, O.Value-I.Value AS W
FROM QryGetOD O, QryGetID I
WHERE Mid(O.Measuring,3)=Mid(I.[name of ID field],3)

thanks once again PHV the line got me some of the way but if I only got OD.value and still like to show the records how's that done???

the line depends on me having both OD and ID...i'd like to calculate W if it's possible (OD-ID) and if it's not just show OD or ID

 

[PHV]

Could you please post the SQL code of QryGetOD and QryGetID ?

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

 

[jammerdk]

it's just 2 simple select queries

QryGETOD :

SELECT Right(Rapport.[Measure],2) & Mid(Rapport.[Measure],Len(Rapport.[Measure])-9,1) AS Measure, Rapport.Value
FROM Rapport
WHERE (((Rapport.[Measure]) Like "*" & "OD" & "*"));

QryGETID :

SELECT Right(Rapport.[Measure],2) & Mid(Rapport.[Measure],Len(Rapport.[Measure])-9,1) AS Measure, Rapport.Value
FROM Rapport
WHERE (((Rapport.[Measure]) Like "*" & "ID" & "*"));

 

[jammerdk]

Ex. of what I want

1.

OD1 : 20 ID1 : 10
OD2 : 20 ID2 : 5
OD3 : 15 ID3 : 5
OD4 : 10 ID4 : 10
OD5 : 15 ID5 : 5

and Calculates

W1 : 10
W2 : 15
W3 : 10
W4 : 0
W5 : 10

Showing all 3 features in the same query

No OD ID W
1 : 20 10 10
2 : 20 5 15
3 : 15 5 10
4 : 10 10 0
5 : 15 5 10

and if ID isn't there the results as follows

ex 2.

No OD ID W
1 : 20 0 0
2 : 20 0 0
3 : 15 0 0
4 : 10 0 0
5 : 15 0 0


and still Showing all 3 features in the same query

using the query for a listbox.

 

[jammerdk]

SELECT Mid(O.Measure,3) AS Measure, O.Value AS Outer, IIF(I.Value <> 0, I.Value,0) AS Inner, (O.Value-I.Value)/2 AS W
FROM QryGetOuter AS O, QryGetInner AS I
WHERE Mid(O.Measure,3)=IIF(I.Measure <> 0,Mid(I.Measure,3), Mid(O.Measure,3)) OR Mid(O.Measure,3);

I'd like the query to return 0 in both Inner and W if the values isn't present

Hope you guys can help me )