Query Count difference of two fields (1 Viewer)

[baijuep]

i have a access table (AUTH) with following field
Company Auth Held

Tata 12

Dell 11

HP 21

Opera 11

Bangour 10

i used the following query to calculate the Held from a table named pers.

SELECT AUTH.company, AUTH.Auth, Count(AUTH.company) AS Held
FROM pers INNER JOIN AUTH ON pers.company = AUTH.company
GROUP BY AUTH.company,AUTH.Auth

then i got the result as under

Company Auth Held

Tata 12 02

Dell 11 08

HP 21 20

Opera 11 12

Bangour 10 12

now i want the difference between Auth and Held as Sur/Defi

how can be it done

 

[rzw0wr]

Try making a field, Sur_Defi:Auth-Held.

Just a guess.

Dale

 

[plog]

First, Held shouldn't be a field in AUTH. You don't store calculated values: you calculate them when you need them.

Second, you're query may be producing incorrect results and is definitely ineffectient. Are [Company] values in AUTH unique? I assume they are not in pers, but if they are not unique in AUTH then you are multiplying the number of records you are counting. Additionally, if there are [Company] values in AUTH that are not in pers you will get no results (null) for that company's Held field which will prevent you from doing math later on.

Lastly, AUTH isn't needed in that query so using it is inefficient. I think this is a better query than the one you posted:

SELECT company, COUNT(company) AS Held FROM pers GROUP BY company

Save that query as sub_HeldTotal, then use it in another query to get the results you want:

SELECT AUTH.company, AUTH.Auth, sub_HeldTotal.Held, (Auth.Auth-Nz([sub_HeldTotal].[Held], 0)) AS SurDefi
FROM Auth
LEFT JOIN sub_HeldTotal ON Auth.company=sub_HeldTotal.company;

That query will provide you results for every company in Auth along with the calculated fields you want.

PS. I didn't validate that last query, so I'm sure these some syntax error.