Question regarding counting of Text values based on their status

[jhoelesmejarda]

Hello everyone,

I have a question regarding counting of text values base on their status and using that result to a calculation.

Say, I have a table of Demand of Positions, wherein, I have a specific Job Title for a certain Department that have number of workers needed (demand quantity) and a table of candidates for that job title and their status, say, Arrived, Visa Processing, Visa Applied, Visa Issued, and With Ticket.

What I would want is to make a summary out of the two tables, where the query will count how many candidates are there in that specific job title and have a field of status say, field of Count of Arrived, Count of Visa Processing and etc., and a field where I can add all of the count of candidates per status and deduct the result to the demand quantity where that field would be named Balance.
The problem is that the status varies on every candidate on that specific job title because the status field is used to track the progress of each candidate and this scenario will make the query blank because there would be no such record due to their status.
I tried making a summary following my requirement and you will see that in the attached file together with the SQL code of that query that the balance field value is blank, actually I don’t have any idea why this happened since I was just getting ideas from the Northwind database.

Please help me as I wanted to learn more, I tried searching my problem in the net many times but actually due to the lack of idea, i opted out in posting my problem here. Thanks in advance

 

[CJ_London]

looks like you need to investigate normalisation, you seem to have a different table for each status, when you just need one table with a status column in it

But to solve your immediate requirement, your balance formula needs to include the use of the nz function around each field - nz(flr1)+nz(fld2)....

 

[jhoelesmejarda]

Thanks for the suggestion it worked like a charm, didn't even tried to put nz() on the formula of the Balance field.

looks like you need to investigate normalisation, you seem to have a different table for each status, when you just need one table with a status column in it

Actually the status is already in one of the field of the table and what I did is I made a separate query for each of the status to show in the column because if I am going to use crosstab query if there is no such record with that status it will not show in the column and the formula will be messed up.

Anyhow, Thank you for quick and simple fix.