CODEWITHSUNDEEP
sqlms-access
Tristan Forward
Tristan Forward

Reputation: 3514

Ms Access SQL calculation from multiple rows

I have the following query:

SELECT 
    InspectionID, Distance, Continuous, structural_grade  
FROM 
    Conditions
WHERE 
    Continuous LIKE ("S%") OR Continuous LIKE ("F%")
ORDER BY 
    InspectionId ASC, Distance ASC

Which outputs:

262 60.80   S01 3
262 73.10   F01 3
262 82.60   S02 2
262 140.30  F02 2
263 30.80   S01 2
263 46.60   F01 2
380 4.60    S01 3
380 8.50    F01 3
380 9.80    S02 4
380 28.20   F02 4
380 77.70   S03 4
380 97.70   F03 4

The ordering is correct. However I'm stuck at what to do next.

Each "S" has a matching "F" with the same numbers Ex S01, F01. I need to access the Distance field of each "F" and minus "S" distance from it.

For examples F01 has a distance of 73.10 and S01 60.80 so the distance between is 12.3. I need to do this for each records and them numbers after the "S" and "F" can go really high yet will always match. ex. S999 - F999

Upvotes: 1

Views: 59

Answers (2)

HansUp
HansUp

Reputation: 97101

Use the Mid() function to extract the digits from your Continuous field: Mid("S01", 2) yields "01". Then use those digits when you join the F and S rows.

With your sample data in Access 2010, the following query gave me this result set:

enter image description here

SELECT
    f.InspectionID,
    f.Continuous,
    s.Continuous,
    f.Distance AS F_distance,
    s.Distance AS S_distance,
    f.Distance - s.Distance AS F_minus_S
FROM
    Conditions AS f
    INNER JOIN
        (
            SELECT
                InspectionID,
                Continuous,
                Distance,
                Mid([Continuous],2) AS digits_only
            FROM Conditions
            WHERE Continuous ALike 'S%'
        ) AS s
    ON
            f.InspectionID = s.InspectionID
        AND Mid(f.[Continuous],2) = s.digits_only
WHERE f.Continuous ALike 'F%'
ORDER BY f.InspectionId ASC, f.Distance ASC;

I included several columns which I doubt you want in your final query. But I think they can be useful during development and testing.

Beware the Access query designer will complain it can't represent that join condition in Design View. If you set up the join in SQL View, you can then run the query without Access complaining.

Upvotes: 1

user1265498735189
user1265498735189

Reputation: 1

SELECT a.continuous AS 'S Continuous',
a.distance AS 'S Distance',
b.continuous AS 'F Continuous',
b.distance AS 'F Distance',
abs(a.distance - b.distance) AS 'Difference'

FROM Conditions a

INNER JOIN Conditions b
ON a.InspectionID = b.InspectionID
AND substring(a.continuous,2,len(a.continuous)-1) = substring(b.continuous,2,len(b.continuous)-1)

WHERE a.Continuous LIKE 'S%'
AND b.Continuous LIKE 'F%'

ORDER BY a.InspectionId ASC, a.Distance ASC

Upvotes: 0

Related Questions