How to return zero in a field if it is null (1 Viewer)

[tcneo]

Hi,

Test planner file is attached.

So there are these 3 tables of interest in the database.

Availability which lists the dates, analyst ID and the number of mins they have available.
e.g.
Date / Analyst_ID / availability
1 Feb 2022 / 1 / 400
1 Feb 2022 / 2 / 400
2 Feb 2022 / 1/ 400
2 Feb 2022 / 2 / 400
and so on

There is also a table with information about the analysts: Analyst_ID / name / email

And there is a third table with task_details: Task_ID / date_of_task / Analyst_ID / method / duration_per_qty / qty

I need a query that returns:
1) every date where is a task assigned.
2) every analyst that is available for each date.
3) if there are 1 or more task on that date which the analyst is assigned to, it will return the sum of (duration_per_qty * qty)

In the only query in my uploaded file, I did what i think is a LEFT JOIN of the Availability table with the task_details table, so it returns:
1) every date (in the availability table)
2) every analyst available on every date
3) if there are 1 or more task on that date which the analyst is assigned to, it will return the sum of (duration_per_qty * qty)

how do i modify the query such that:
1) dates in which there are no task assign do not appear (nice to have but not very critical since i can filter the range of dates later)
2) currently for dates in which there are no task, the calculation (sum(duration_per_qty * qty)) is blank. how do i get it to return a zero instead?

thanks!

 

[arnelgp]

for pt.2 you create another query that will alter the column (with calculation sum(duration_per_qty*qty)):

select field1, field2, Nz(theSumField, 0) As summation from your query;

use this query for your form/report.

//if you can show the link where you posted the sample db, i will take a look.

 

[tcneo]

for pt.2 you create another query that will alter the column (with calculation sum(duration_per_qty*qty)):

select field1, field2, Nz(theSumField, 0) As summation from your query;

use this query for your form/report.

//if you can show the link where you posted the sample db, i will take a look.

hi, the sample file is attached to my original post.

 

[tcneo]

for pt.2 you create another query that will alter the column (with calculation sum(duration_per_qty*qty)):

select field1, field2, Nz(theSumField, 0) As summation from your query;

use this query for your form/report.

//if you can show the link where you posted the sample db, i will take a look.

that's a nifty of acheiving pt 2. i learn a new function today. Thanks!

 

[arnelgp]

see your query now.