need to show blank values

[instereo911 via AccessMonster.com]

So i have a table with the following data on it:
Date | CustomerID | Method | Aged Days | Manager

and have a query based off of the table with the following:
SELECT TotalInventory.date, TotalInventory.method, TotalInventory.status,
TotalInventory.ageddays
FROM [Manager Table] INNER JOIN TotalInventory ON [Manager Table].Unit =
TotalInventory.unit
WHERE ((([Manager Table].Manager)="becky julian"))
GROUP BY TotalInventory.date, TotalInventory.method, TotalInventory.status,
TotalInventory.ageddays;

then another query off of that:
SELECT beckyjulian_TI.date, beckyjulian_TI.method, beckyjulian_TI.ageddays
FROM beckyjulian_TI
WHERE (((beckyjulian_TI.method)="call"));

then another query off of that
SELECT beckyjulian_TI_calls.date, Count(beckyjulian_TI_calls.ageddays) AS
CountOfageddays
FROM beckyjulian_TI_calls
WHERE (((beckyjulian_TI_calls.ageddays)>30))
GROUP BY beckyjulian_TI_calls.date;

The outcome I hoped to get on my last query was to show the data on a new
query as the following:
Date Manager Total Calls Inv Total Calls >30
01/01/07 Becky Julian 100 20
01/02/07 Becky Julian 0 0
01/03/07 Becky Julian 50 0
etc.

But for the zeros will not show up and the date will be skipped if there is a
blank amount for the count... Is there any way of helping with this problem..
I am probably doing it all wrong

Thank you again everyone

 

[mscertified]

You should run each query independently and make sure each is giving you what
you want. Remember that whenever you have a 'WHERE column = value', you will
get NO matches if the column is NULL.
Also when you join 2 tables or queries together the result will include only
those rows in BOTH tables.
I don't understand which query is supposed to be giving the table you posted.

-Dorian

 

[KARL DEWEY]

Create a table name CountNumber with field named CountNUM containing 0 (zero)
through your maximum void spread. Use these queries that I edited.
beckyjulian_TI_Dates1 ---
SELECT DateAdd("d",[CountNum],[Date]) AS Dates
FROM beckyjulian_TI_calls, CountNumber, beckyjulian_TI_Dates2
WHERE (((DateAdd("d",[CountNum],[Date]))<=[MaxOfDate]))
GROUP BY DateAdd("d",[CountNum],[Date]);

beckyjulian_TI_Dates2 ---
SELECT Max(beckyjulian_TI_calls.Date) AS MaxOfDate
FROM beckyjulian_TI_calls;

beckyjulian_TI ---
SELECT TotalInventory.Date, TotalInventory.Method, TotalInventory.status,
TotalInventory.AgedDays
FROM [Manager Table] INNER JOIN TotalInventory ON [Manager Table].Unit =
TotalInventory.Unit
WHERE ((([Manager Table].Manager)="becky julian"));

beckyjulian_TI_calls ---
SELECT beckyjulian_TI.Date, beckyjulian_TI.Method, beckyjulian_TI.AgedDays
FROM beckyjulian_TI
WHERE (((beckyjulian_TI.Method)="call"));

SELECT beckyjulian_TI_Dates1.Dates, Sum(IIf([AgedDays]<=30,1,0)) AS [Total
Calls Inv], Sum(IIf([AgedDays]>30,1,0)) AS [Total Calls >30]
FROM beckyjulian_TI_Dates1 LEFT JOIN beckyjulian_TI_calls ON
beckyjulian_TI_Dates1.Dates = beckyjulian_TI_calls.Date
GROUP BY beckyjulian_TI_Dates1.Dates;

--
KARL DEWEY
Build a little - Test a little

So i have a table with the following data on it:
Date | CustomerID | Method | Aged Days | Manager

and have a query based off of the table with the following:
SELECT TotalInventory.date, TotalInventory.method, TotalInventory.status,
TotalInventory.ageddays
FROM [Manager Table] INNER JOIN TotalInventory ON [Manager Table].Unit =
TotalInventory.unit
WHERE ((([Manager Table].Manager)="becky julian"))
GROUP BY TotalInventory.date, TotalInventory.method, TotalInventory.status,
TotalInventory.ageddays;

then another query off of that:
SELECT beckyjulian_TI.date, beckyjulian_TI.method, beckyjulian_TI.ageddays
FROM beckyjulian_TI
WHERE (((beckyjulian_TI.method)="call"));

then another query off of that
SELECT beckyjulian_TI_calls.date, Count(beckyjulian_TI_calls.ageddays) AS
CountOfageddays
FROM beckyjulian_TI_calls
WHERE (((beckyjulian_TI_calls.ageddays)>30))
GROUP BY beckyjulian_TI_calls.date;

The outcome I hoped to get on my last query was to show the data on a new
query as the following:
Date Manager Total Calls Inv Total Calls >30
01/01/07 Becky Julian 100 20
01/02/07 Becky Julian 0 0
01/03/07 Becky Julian 50 0
etc.

But for the zeros will not show up and the date will be skipped if there is a
blank amount for the count... Is there any way of helping with this problem..
I am probably doing it all wrong

Thank you again everyone

 

[instereo911 via AccessMonster.com]

Thanks for the feedback Dorian,

If have a 'where column=value' and I want to substitute a blank with a "0",
how do i accomplish this... ?


I guess what i need to do is put some sort of formula before the where in all
of my queries to make sure to substitute a blank with a 0 ...

Hopefully this is possible,

You should run each query independently and make sure each is giving you what
you want. Remember that whenever you have a 'WHERE column = value', you will
get NO matches if the column is NULL.
Also when you join 2 tables or queries together the result will include only
those rows in BOTH tables.
I don't understand which query is supposed to be giving the table you posted.

-Dorian

So i have a table with the following data on it:
Date | CustomerID | Method | Aged Days | Manager

[quoted text clipped - 33 lines]

Thank you again everyone

 

[mscertified]

Blank is different to null !

"WHERE column = value OR column IS NULL"

will get you all the records with the value plus the records that have null
in the column. you can convert the NULL to a zero in your report or wherever
the data is going.

-Dorian

Thanks for the feedback Dorian,

If have a 'where column=value' and I want to substitute a blank with a "0",
how do i accomplish this... ?


I guess what i need to do is put some sort of formula before the where in all
of my queries to make sure to substitute a blank with a 0 ...

Hopefully this is possible,

You should run each query independently and make sure each is giving you what
you want. Remember that whenever you have a 'WHERE column = value', you will
get NO matches if the column is NULL.
Also when you join 2 tables or queries together the result will include only
those rows in BOTH tables.
I don't understand which query is supposed to be giving the table you posted.

-Dorian

So i have a table with the following data on it:
Date | CustomerID | Method | Aged Days | Manager

[quoted text clipped - 33 lines]

Thank you again everyone