how to show an entire date span

[Jenny]

Hi everybody, on a table containing a Date and a Product field, I execute
the following simple query:

SELECT MyTable.Date, Count(MyTable.Product) AS ProductCount
FROM MyTable
GROUP BY MyTable.Date
ORDER BY MyTable.Date;

The result looks like this:

Date ProductCount
2004-03-23 89
2004-03-24 205
2004-03-27 24
2004-03-28 167
2004-04-01 122

However, I'd prefer the entire date span from the lowest date
Min(MyTable.Date) up to the highest date Max(MyTable.Date) to be shown, i.e.
including dates with a zero product count, so the query result would look
like this:

Date ProductCount
2004-03-23 89
2004-03-24 205
2004-03-25 0
2004-03-26 0
2004-03-27 24
2004-03-28 167
2004-03-29 0
2004-03-30 0
2004-03-31 0
2004-04-01 122

Any advice?

 

[Michel Walsh]

Hi,

You would need a driver table. Say Iotas, one field, iota, with values from
0 to 999.

Save your first query, query1. Now, make a query like:



SELECT DMin("[Date]", "query1") + Iotas.iota As TheDate,
Nz(ProductCount, 0)
FROM Iotas LEFT JOIN query1
ON DMin("[Date]", "query1") + Iotas.iota = query1.Date



Hoping it may help,
Vanderghast, Access MVP

 

[Gary Walter]

Hi,

You would need a driver table. Say Iotas, one field, iota, with values from
0 to 999.

Save your first query, query1. Now, make a query like:



SELECT DMin("[Date]", "query1") + Iotas.iota As TheDate,
Nz(ProductCount, 0)
FROM Iotas LEFT JOIN query1
ON DMin("[Date]", "query1") + Iotas.iota = query1.Date

Hi Michel,

Excellent (as always), but I think it may be missing
WHERE clause?

SELECT DMin("[Date]", "query1") + Iotas.iota As TheDate,
Nz(ProductCount, 0)
FROM Iotas LEFT JOIN query1
ON DMin("[Date]", "query1") + Iotas.iota = query1.Date
WHERE
(DMin("[Date]", "query1") + Iotas.iota) <= DMax("[Date]","query1");

Apologies for butting in (especially if I am wrong).

Gary Walter

 

[Jenny]

SELECT DMin("[Date]", "query1") + Iotas.iota As TheDate,

Nz(ProductCount, 0)
FROM Iotas LEFT JOIN query1
ON DMin("[Date]", "query1") + Iotas.iota = query1.Date
WHERE
(DMin("[Date]", "query1") + Iotas.iota) <= DMax("[Date]","query1");

Terrific, this works like a charm! Thanks, guys.

J.

 

[Michel Walsh]

Hi,


Indeed, indeed, your are right with the part WHERE the WHERE is
missing



To make a possible use of index on Iotas.Iota, I would change it to:


WHERE DMax("[Date]","query1")-DMin("[Date]", "query1") >= Iotas.iota


since SQL is very poor on algebra, even if the statements are equivalent,
this one implies a field alone compared with a "constant", and the index can
then be used, while with

WHERE fieldName + 0 = constant


the index (on fieldName) won't, probably, be used at all.


Vanderghast, Access MPV



(...)
but I think it may be missing

WHERE clause?

SELECT DMin("[Date]", "query1") + Iotas.iota As TheDate,
Nz(ProductCount, 0)
FROM Iotas LEFT JOIN query1
ON DMin("[Date]", "query1") + Iotas.iota = query1.Date
WHERE
(DMin("[Date]", "query1") + Iotas.iota) <= DMax("[Date]","query1");

Apologies for butting in (especially if I am wrong).

Gary Walter