inclusion of all dates in a criteria

[kingz2000]

Hi all,
if I execute the SQL
select stops, xdate from traffic where xdate>=#8/1/05#
and xdate<=8/7/05#
I get 4 data sets

1.8.05 10
2.8.05 7
4.8.05 12
6.8.05 9

However, I would like to see the whole week. WIth zeros where there wasn't anything..so, like this:

1.8.05 10
2.8.05 7
3.8.05 0
4.8.05 12
5.8.05 0
6.8.05 9
7.8.05 0


How do I do this?
Thanks in advance.
Kingsley

 

[PHV]

One way is a LEFT JOIN with a table of dates.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JerryKlmns]

Create the table AllDatesTable with a field TheDate of data type Date/Time. Add all dates of the year. Then copy the following to a new query SQL view,

PARAMETERS [From] Date, [To] Date;
SELECT stops, xdate
FROM traffic RIGHT JOIN AllDatesTable
ON traffic.xdate = AllDatesTable.TheDate
WHERE TheDate BETWEEN [From] And [To];

And you 'll get a parameter query showing what you need.

Hmmmm!It worked when I checked it!

 

[JerryKlmns]

PHV reads, computes, writes and submits faster than ANYONE!
ALWAYS!!!!!!

A litle correction for my post

PARAMETERS [From] DateTime, [To] DateTime;
SELECT stops, xdate
FROM traffic RIGHT JOIN AllDatesTable
ON traffic.xdate = AllDatesTable.TheDate
WHERE TheDate BETWEEN [From] And [To];

 

[PHV]

I was lazzy, forgot a tip:
use Excel to build the AllDates table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kingz2000]

So this is the only way to do it right?
It just seems a little undynamic, when I consider having to add a table with all the dates in manually..I mean, I want the program to work in 10 years, too..20 years

 

[JerryKlmns]

Kingsley,

For every 4 years you need 3*365+366=1459 dates (records) so I think that for 176 years you are covered. Unless you are an immortal god, programmer. You could create 64196 days forward in excel, copy them and paste append in the AllDatesTable.

Life is Short. Enjoy all of it (with children)

 

[PHV]

As you asked in a SQL forum you got a SQL solution.
With a programming language and Recordset you can play with loops ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886