Show date even if there is no criteria for that date

[netrusher]

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"));

 

[lameid]

Without knowing the query name of the underlying query it is not intuitive to follow...

Looking at the top query and your description, I have to wonder if all you need to do is change your query to an outer join... Does DATE-PCT-NoFaultTotals-6-9PTON contain all the days and the other query contain the out of stock days?

If so...

...
FROM ([DATE-PCT-NoFaultTotals-6-9PTON] [red]Left[/red] JOIN [DATE-PCT-Out of Stock Totals-6-9PTON] ...

 

[SkipVought]

Hi,

There can be a huge overhead in trying to represent the absence of data.

Does it really matter if on March 5, 2009, for instance, your inventory CHANGED...

...and this past MONDAY it was the same and TUESDAY it was the same and WEDNESDAY it was the same and TODAY it is the same?

And , oh yes, I should have reported that last FRIDAY it was the same and last THURSDAY it was the same and, and, and...

All that nonsense is convered by the latest reported change, is it not?

Sounds like some mid level manager's goofy requirement.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Here's what you could do.

Report your stock data in a Crosstab transform.

Year-Mon across, Items in rows.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lameid]

As a generic solution I have always been partial to using a table that lists all days or months where appropriate, a calendar table. Then you can make a query that generates a cartesian product. You have the calendar table and the a table that has the key values you want to test but no join. You can use this query as the basis for outer joining to the rest of the data.

The crosstab will only work if you specify the column heading of all the days you want to include or all the days are in the data to begin with (although an intriguing coding possibility). A problem with doing this for a crosstab query may be there is an upper limit to the number of columns and number of characters a query can have. I want to say 255 columns or ~65k characters. I doubt you'd hit either of these but something to be aware of. If you want a crosstab, you could crosstab off my solution as well but the column limit is still in play.

If a crosstab works for you and your data has all the days, it is the clear winner. However if you can't guarantee all days you will probably end up writing code to either put the days in the column vales of a crosstab query or populate a calendar table.

 

[netrusher]

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]

 

[lameid]

I still think you want an Outer join. But it sounds like you are interested in the NZ function or a combonation of IIF and Isnull functions depending on whether you are dealing with a value or an exprestion...

Pct No Faults: IIF(ISNULL([DATE-PCT-NoFaultTotals-6-9PTON].[No Fault Totals]),1,[DATE-PCT-NoFaultTotals-6-9PTON].[No Fault Totals]/[DATE-PCT-WorkUnitTotals-6-9PTON].[WU per day])