Query that return a fixed set of results and format (1 Viewer)

[acesQuer]

Hi,

I would like to make my query such that it will always return all result, with the result that did not match the criteria to be set to zero. How do I do that??

Below is an example:

I have the following table:

ProdtID|ProdtType|Cost|MauType|DatePurc

P001|Stationary|2000|15/1/2010
P002|Music|3000|18/1/2010
P003|Book|4000|11/7/2010
P004|Electronics|5000|5/10/2010

Whenever I run my query to retrieve the result based on a DatePurc(Date of Purchase), the results that returned does not conform to a fixed format.

Eg: If I select all ProdtType and Cost that is between 1/1/2010 to 18/4/2010, results returned only Stationary and Music with 2000, 3000 as cost.

That is correct. However, I would like to fix the query such that it also returned Book and Electronics with their price set to 0, since they did not match the criteria.

Desired Output when I run my query:

Stationary|2000
Music|3000
Book|0
Electronics|0

Reason for doing that: I would like to display the results in a table format for analysis. Therefore, I would still need the results even though it didnt meet the criteria.

Any ideas how to achieve the desired effect??

Many thanks ahead!

 

[jzwp22]

Assuming that your query looks something like this:

query name: qryProdListingForThePeriod
SELECT maintable.ProdtID, maintable.ProdtType, maintable.Cost, maintable.MauType, maintable.DatePurc
FROM maintable
WHERE DatePurc between #1/1/10# and #4/18/2010#;

Now, create another query that extracts the unique products from your main table:


query name: qryProducts
SELECT DISTINCT maintable.ProdtID, maintable.ProdtType
FROM maintable;

Now left join the qryProducts to qryProdListingForthePeriod. Include an IIF() function that sets the value to zero when the cost field is null

query name: qryFinal
SELECT qryProducts.ProdtType, IIf(IsNull([Cost]),0,[Cost]) AS PeriodCost
FROM qryProducts LEFT JOIN qryProdListingForThePeriod ON (qryProducts.ProdtType=qryProdListingForThePeriod.ProdtType) AND (qryProducts.ProdtID=qryProdListingForThePeriod.ProdtID);

I've attached an example database.

 

[acesQuer]

Hi jzwp22,

Thank you for the reply. However, I still encountered the same problem when I add additional clauses such as a WHERE statement. The results returned does not contain all the ProdtType.

E.g:
SELECT qryProducts.ProdtType, IIf(IsNull([Cost]),0,[Cost]) AS PeriodCost
FROM qryProducts LEFT JOIN qryProdListingForThePeriod ON (qryProducts.ProdtType=qryProdListingForThePeriod.ProdtType) AND (qryProducts.ProdtID=qryProdListingForThePeriod.ProdtID)
WHERE cost > 2000;

The result returned:
qryFinal ProdtType PeriodCost Music $3,000.00
What I would like is to have all other ProdtType but with their PeriodCost set to 0 since they didnt meet the criteria.

Any idea how to obtain the desired result? Many thanks.

 

[jzwp22]

You will have to put the additional criteria in the correct query just adding it to the last query will probably not give you what you need. In your original post, you were only concerned with the date range, you did not specify a minimum amount. Are you really after those products in the date range that had a cost of > 2000? If so then you need to add the criteria to this query:

query name: qryProdListingForThePeriod
SELECT maintable.ProdtID, maintable.ProdtType, maintable.Cost, maintable.MauType, maintable.DatePurc
FROM maintable
WHERE DatePurc between #1/1/10# and #4/18/2010# AND cost>2000

Could you please explain in detail what you are after?