need zeros!

[Gen]

Hi,

I can see a lot of people have posted about this topic, but I couldn't find
anything to help me. I have a query based on four tables:

1. Projects
ProjectID*
2. Purchase Orders
PurchaseOrderID*
ProjectID
OrderDate
3. Order Details
TransactionID*
PurchaseOrderID
ProductID
UnitsOrdered
4. Items
ProductID*
Price/Unit

*Primary key for that table

I'd like to see the total cost we are spending on each project per month and
the number of orders per project were are making. To do that, I have created
the following Query:

PARAMETERS Start DateTime, Finish DateTime;
SELECT Projects.ProjectID, Nz(Count([PurchaseOrderNumber])) AS POCount,
Sum(CCur(Nz([unitsordered]*[Price/unit]))) AS Sub
FROM (Projects LEFT JOIN [Purchase Orders] ON Projects.ProjectNo = [Purchase
Orders].ProjectID) LEFT JOIN (Items RIGHT JOIN [Order Details] ON
Items.ProductID = [Order Details].ProductID) ON [Purchase
Orders].PurchaseOrderID = [Order Details].PurchaseOrderID
WHERE ((([Purchase Orders].OrderDate) Between [start] And [finish]))
GROUP BY Projects.ProjectNo;

This query works great when I don't include the WHERE cause of the date
restriction, but when I do if there were no items ordered for, say, project
1, then nothing is returned, and I'd like it to return $0.00. I read
somewhere that the problem may be that the WHERE statement is preformed
first, so that will eliminate all projects with a zero amount right away.
Can anyone help?

Thanks very much

 

[KARL DEWEY]

Try this ---
WHERE ((([Purchase Orders].OrderDate) Between [start] And [finish])) OR
([Purchase Orders].OrderDate) IS NULL
GROUP BY Projects.ProjectNo;

 

[Gen]

Its almost working!!! But something strange is happening.... There are 11
projects total. Before, I was only getting the project totals back for 7 of
them that atually had numbers. With the new code you have suggested, I get
another 2 projects returned with zero for POCount and Total cost (great!).
But its still not returning the last 2 projects (number 7 and 10) ..... Can
you explain what might be happening here?

Try this ---
WHERE ((([Purchase Orders].OrderDate) Between [start] And [finish])) OR
([Purchase Orders].OrderDate) IS NULL
GROUP BY Projects.ProjectNo;

--
KARL DEWEY
Build a little - Test a little

Hi,

I can see a lot of people have posted about this topic, but I couldn't find
anything to help me. I have a query based on four tables:

1. Projects
ProjectID*
2. Purchase Orders
PurchaseOrderID*
ProjectID
OrderDate
3. Order Details
TransactionID*
PurchaseOrderID
ProductID
UnitsOrdered
4. Items
ProductID*
Price/Unit

*Primary key for that table

I'd like to see the total cost we are spending on each project per month and
the number of orders per project were are making. To do that, I have created
the following Query:

PARAMETERS Start DateTime, Finish DateTime;
SELECT Projects.ProjectID, Nz(Count([PurchaseOrderNumber])) AS POCount,
Sum(CCur(Nz([unitsordered]*[Price/unit]))) AS Sub
FROM (Projects LEFT JOIN [Purchase Orders] ON Projects.ProjectNo = [Purchase
Orders].ProjectID) LEFT JOIN (Items RIGHT JOIN [Order Details] ON
Items.ProductID = [Order Details].ProductID) ON [Purchase
Orders].PurchaseOrderID = [Order Details].PurchaseOrderID
WHERE ((([Purchase Orders].OrderDate) Between [start] And [finish]))
GROUP BY Projects.ProjectNo;

This query works great when I don't include the WHERE cause of the date
restriction, but when I do if there were no items ordered for, say, project
1, then nothing is returned, and I'd like it to return $0.00. I read
somewhere that the problem may be that the WHERE statement is preformed
first, so that will eliminate all projects with a zero amount right away.
Can anyone help?

Thanks very much

 

[Gen]

I think I know why this is happening - nothing has ever been ordered for the
two projects that are now coming up since the database was created. The
query is still not displaying the projects where things have been ordered
for, but just not in the time frame searched for. So technically I guess they
are not Null?

Its almost working!!! But something strange is happening.... There are 11
projects total. Before, I was only getting the project totals back for 7 of
them that atually had numbers. With the new code you have suggested, I get
another 2 projects returned with zero for POCount and Total cost (great!).
But its still not returning the last 2 projects (number 7 and 10) ..... Can
you explain what might be happening here?

Try this ---
WHERE ((([Purchase Orders].OrderDate) Between [start] And [finish])) OR
([Purchase Orders].OrderDate) IS NULL
GROUP BY Projects.ProjectNo;

--
KARL DEWEY
Build a little - Test a little

Hi,

I can see a lot of people have posted about this topic, but I couldn't find
anything to help me. I have a query based on four tables:

1. Projects
ProjectID*
2. Purchase Orders
PurchaseOrderID*
ProjectID
OrderDate
3. Order Details
TransactionID*
PurchaseOrderID
ProductID
UnitsOrdered
4. Items
ProductID*
Price/Unit

*Primary key for that table

I'd like to see the total cost we are spending on each project per month and
the number of orders per project were are making. To do that, I have created
the following Query:

PARAMETERS Start DateTime, Finish DateTime;
SELECT Projects.ProjectID, Nz(Count([PurchaseOrderNumber])) AS POCount,
Sum(CCur(Nz([unitsordered]*[Price/unit]))) AS Sub
FROM (Projects LEFT JOIN [Purchase Orders] ON Projects.ProjectNo = [Purchase
Orders].ProjectID) LEFT JOIN (Items RIGHT JOIN [Order Details] ON
Items.ProductID = [Order Details].ProductID) ON [Purchase
Orders].PurchaseOrderID = [Order Details].PurchaseOrderID
WHERE ((([Purchase Orders].OrderDate) Between [start] And [finish]))
GROUP BY Projects.ProjectNo;

This query works great when I don't include the WHERE cause of the date
restriction, but when I do if there were no items ordered for, say, project
1, then nothing is returned, and I'd like it to return $0.00. I read
somewhere that the problem may be that the WHERE statement is preformed
first, so that will eliminate all projects with a zero amount right away.
Can anyone help?

Thanks very much