Solved Query price sum is not equal to zero when no record found (Query Null should be zero) (1 Viewer)

[Ihk]

Query sum is not getting zero, if there is not specific record. I want to have zero value (Price).
if there is record then meaning record is not empty, then query works perfectly fine.
I have attached
1)query picture

2) Sql code

SELECT Sum(StatusQuery.[Date(User)]) AS [SumOfDate(User)], MainOrdersEntries.Status, Sum(MainOrdersEntries.TotalPrice) AS SumOfTotalPrice
FROM StatusQuery INNER JOIN MainOrdersEntries ON StatusQuery.ID = MainOrdersEntries.ID
GROUP BY MainOrdersEntries.Status
HAVING (((Sum(StatusQuery.[Date(User)]))>=Date()-60) AND ((MainOrdersEntries.Status)="Pending"));

as shown in picture, query is trying to fetch record with "Pending" field. when there are such records with "Pending" then it works pretty well. when there not such record (row) in table, query sum of "Pending" as well as Sum of Price should be zero.

Can someone help.
Thank you

 

[Ranman256]

Use NZ(field,0)

 

[plog]

when there not such record (row) in table, query sum of "Pending" as well as Sum of Price should be zero.

You can't create records that don't exist.

If you want a record for a Status that doesn't exist you must use a datasource that does have it. Do you have a table of your Status values? If so, what's its name?

NZ() will be needed, but not in the query you posted. The one you posted will become a subquery.

 

[Ihk]

In my table Field "status" values can be either of
Pending
Approved
Cancelled
etc
Each row of will have either of these at the same time. when we change the status from "Pending" to " approved". or to "Cancelled".... that means there is no record in the table with "Pending".
Then how to turn "sum for price of Pending" in to zero rather than Null.
Please with one example, how to use NZ() in this case.
thank you

 

[Ihk]

Use NZ(field,0)

One example please, I will be thankful

 

[arnelgp]

you need to add a Third table (dummy) for the missing dates.
join this table and you can use Nz() for missing values for those dates.

 

[isladogs]

Agree with previous comments. A few extra points:
1. You cannot sensibly sum date fields. You could however use Count instead.
2. It is a bad idea to use special characters such as () in a field name. DateUser or UserDate would be better
3. Change the GroupBy for the Status field to Where. It will be more efficient

This can also be done using two queries (and without using a missing dates table) as follows

Query1:

SELECT Count(StatusQuery.[Date(User)]) AS [CountOfDate(User)], Sum(MainOrdersEntries.TotalPrice) AS SumOfTotalPrice
FROM StatusQuery INNER JOIN MainOrdersEntries ON StatusQuery.ID = MainOrdersEntries.ID
GROUP BY MainOrdersEntries.Status
WHERE (((StatusQuery.[Date(User)]))<Date()-60) AND ((MainOrdersEntries.Status)="Pending"));

Query2:

SELECT Query1.CountOfUserDate, Nz([SumOfPrice],0) AS TotalPrice
FROM Query1;

 

[plog]

First you need to create a data source of the unique Status values:

SELECT Status
FROM MainOrdersEntries
GROUP BY Status;

Paste that into a new query and name it 'sub1'. Next take your existing query, remove the 'Pending' criteria and name that query 'sub2'.

Then to get the data you want combine them in a new query:

SELECT sub1.Status, Nz([Date(User)], 0) AS Field1, NZ(TotalPrice, 0) AS TotalPriceField
FROM sub1
LEFT JOIN sub2 ON sub1.Status = sub2.Status

 

[Ihk]

sorry for my late reply,
I am thankful for every body for helping me out.
It worked for me with NZ function. NZ(normal code for sum up),0)
For example Like this
NZ(Sum(MainOrdersEntries.TotalPrice),0) AS SumOfTotalPrice

 

[Ihk]

Agree with previous comments. A few extra points:
1. You cannot sensibly sum date fields. You could however use Count instead.
2. It is a bad idea to use special characters such as () in a field name. DateUser or UserDate would be better
3. Change the GroupBy for the Status field to Where. It will be more efficient

This can also be done using two queries (and without using a missing dates table) as follows

Query1:

SELECT Count(StatusQuery.[Date(User)]) AS [CountOfDate(User)], Sum(MainOrdersEntries.TotalPrice) AS SumOfTotalPrice
FROM StatusQuery INNER JOIN MainOrdersEntries ON StatusQuery.ID = MainOrdersEntries.ID
GROUP BY MainOrdersEntries.Status
WHERE (((StatusQuery.[Date(User)]))<Date()-60) AND ((MainOrdersEntries.Status)="Pending"));

Query2:

SELECT Query1.CountOfUserDate, Nz([SumOfPrice],0) AS TotalPrice
FROM Query1;

I am very very thankful, These points really made my understanding very clear. I appreciate your time for guiding me.

 

[isladogs]

You're welcome