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.
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.
Code:
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;
Code:
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"));