Access SQL grouped count (1 Viewer)

cool · Dec 14, 2016

I have a string variable in vba containing the following SQL : -

"SELECT Count(*) AS employ_number, table1.job AS jobs " & _
"FROM (SELECT DISTINCT names, job FROM table1) AS countx " & _
"GROUP BY table1.job "

that's worked fine
from the above , I have a grouped counts based on job in table1, for ex :
engineer = 15
captain = 20
lawyer =13

I need to do a subtraction from each job according to a number specified by a control ,
so if these controls (eng = 2 , capt = 3 , law = 7)
then the result should be :
engineer = 13
captain = 17
lawyer = 6

any ideas ?

Ranman256 · Dec 14, 2016

In your conversion table,you need 3 fields,
Job,value,subtract.
Engineer,15,2
Capt,20,3

Do the math in the query.

cool · Dec 14, 2016

Ranman , thanks for the reply
there's maybe 20 different job I have to subtract a different values from them
I just put these 3 for example , may I ask you for more explanation ?

Grumm · Dec 14, 2016

So just to get the correct picture :
You select and count all the different jobs.
Then you have 1 control for each job in a form where a user can enter a number ?
If that is the case, put the result of the select in an array and change the correct index with the number in the control.
You will need to use VBA to do this. It will be hard to do it in the sql itself.
Or store the substracted value in a different table with a link to the job. And use one single query.

cool · Dec 14, 2016

Grumm said:
So just to get the correct picture :
You select and count all the different jobs.
Then you have 1 control for each job in a form where a user can enter a number ?

that's exactly what I need ,and I wish to do it with vba
but I don't know how to start

Ranman256 · Dec 14, 2016

You DONT need to do it in vb.
Queries can work.
A conversion table with the values to use and subtract will work.

cool · Dec 14, 2016

Ranman256 said:
You DONT need to do it in vb.
Queries can work.
A conversion table with the values to use and subtract will work.

please ,, an example may be help
and forgive me because I don't know what's the meaning of conversion table

Grumm · Dec 15, 2016

Can you post extra information about your database ?
I guess Ranman256 didn't see that the values of a job is done by a count. That value can't be stored in a field in that table.
That is why I proposed 2 solutions.
Since you don't know vba, you can create a new table where you store the values you need to substract each job. (linked to that 'table1') Then in the query you showed in the first post, you just need an extra join to that new table and do "Count(*) - table2.Substract".

I can make an example if you give me the structure of that 'table1'.

cool · Dec 15, 2016

Grumm said:
Can you post extra information about your database ?
I guess Ranman256 didn't see that the values of a job is done by a count. That value can't be stored in a field in that table.
That is why I proposed 2 solutions.
Since you don't know vba, you can create a new table where you store the values you need to substract each job. (linked to that 'table1') Then in the query you showed in the first post, you just need an extra join to that new table and do "Count(*) - table2.Substract".

I can make an example if you give me the structure of that 'table1'.

thank you Grumm
I have solved it , I am good with vba but not in SQL .

Access SQL grouped count (1 Viewer)

cool

New member

Ranman256

Well-known member

cool

New member

Grumm

Registered User.

cool

New member

Ranman256

Well-known member

cool

New member

Grumm

Registered User.

cool

New member

Similar threads

Users who are viewing this thread