Show date even if there is no criteria for date

[aldeb]

The SQL Code below is a combination of 3 queries.
The 2nd SQL Code below is one of the 3 queries that make up
the 1st SQL Code. The PossibleCause Field of the 2nd query
criteria is "out of stock". The combined query below is
based on a date range. I have been asked if the dates in
the selected date range do not have any "out of stock" for
a date that the query show that date PCT Parts Available at
100%. Now, if there is no "out of stock" for a particular
date it of course shows nothing. Any advice out there?
Bottom line, If I select date range Aug 1- Aug 18 and the
only date with an "out of stock" is Aug 13 then that is all
that shows up. I would like for everyday to show even if
there is not "out of stock" and those dates would show 100%.

Thanks for all help.

SELECT [DATE-PCT-NoFaultTotals-6-9PTON].[No Fault Totals], [DATE-PCT-Out of Stock Totals-6-9PTON].[Out of Stock], [DATE-PCT-WorkUnitTotals-6-9PTON].[WU per day], [DATE-PCT-NoFaultTotals-6-9PTON].[No Fault Totals]/[DATE-PCT-WorkUnitTotals-6-9PTON].[WU per day] AS [Pct No Faults], 1-[DATE-PCT-Out of Stock Totals-6-9PTON].[Out of Stock]/[DATE-PCT-WorkUnitTotals-6-9PTON.WU per day] AS [FTT Parts Available], [DATE-PCT-NoFaultTotals-6-9PTON].TodaysDate
FROM ([DATE-PCT-NoFaultTotals-6-9PTON] INNER JOIN [DATE-PCT-Out of Stock Totals-6-9PTON] ON [DATE-PCT-NoFaultTotals-6-9PTON].TodaysDate = [DATE-PCT-Out of Stock Totals-6-9PTON].TodaysDate) INNER JOIN [DATE-PCT-WorkUnitTotals-6-9PTON] ON [DATE-PCT-Out of Stock Totals-6-9PTON].TodaysDate = [DATE-PCT-WorkUnitTotals-6-9PTON].TodaysDate
ORDER BY [DATE-PCT-NoFaultTotals-6-9PTON].TodaysDate;

SELECT WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS [Out of Stock], WorkUnitsFaultsMainTBL.TodaysDate
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("H006","C878","J006","D878","M202","M229","A299","A909")) And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt))
GROUP BY WorkUnitsFaultsMainTBL.FaultCategory, WorkUnitsFaultsMainTBL.TodaysDate, WorkUnitsFaultsMainTBL.PossibleCause
HAVING (((WorkUnitsFaultsMainTBL.PossibleCause)="out of stock"));

 

[pbaldy]

Try

...
FROM ([DATE-PCT-NoFaultTotals-6-9PTON] LEFT JOIN...

All those spaces and symbols in your names are not recommended.

 

[aldeb]

Thanks pbaldy,

That will give me each date but will not put 100% in the field if "out of stock" is null. I am looking to show a zero if out-of-stock is null and add 100% to the "Pct No Faults".

 

[aldeb]

The code below is in one of my Query fields. I would like to
do the following or something like it:

If "out of stock" is null then "Pct No Faults" 100% or if
"out of stock" is not null then the code below. Anyone know
how to write that?

Also, if "out of stock" is null I would like a zero to appear
by each date in the "out of stock" field.

Pct No Faults: [DATE-PCT-NoFaultTotals-6-9PTON].[No Fault Totals]/[DATE-PCT-WorkUnitTotals-6-9PTON].[WU per day]

 

[pbaldy]

You should be able to use either the Nz() function to replace a Null, or the IIf() function to test for Null and respond accordingly.