Select record even with no data (date query)

[Danilo]

Hi.
I have a table like this:
DATE | NAME
01/01/2008 Name 1
01/03/2008 Name 2
01/06/2008 Name 3

Ok. Now, I'd like to SELECT all records between '01/01/2008' AND '01/10/2008'.
But, the normal response from Access is to give me just the 3 records in the
table. But I need to get the following response:
DATE | NAME
01/01/2008 Name 1
01/02/2008 NULL
01/03/2008 Name 2
01/04/2008 NULL
01/05/2008 NULL
01/06/2008 Name 3
01/07/2008 NULL
01/08/2008 NULL
01/09/2008 NULL
01/10/2008 NULL

Anyone could help me?
Thanks!!!

 

[Allen Browne]

The dates have to come from somewhere, so create a table with just one field
named (say) TheDate, type Date/Time. Mark the field as primary key, and save
the table as tblDate.

Now enter all the dates in the range you need. You can use the function
below to populate it with 20 years worth of dates if you wish.

Now create a query, using both tables. Drag tblDate.TheDate, and drop it
onto the date field in your original table. Access displays a line joining
the 2 tables in the upper pane of query design. Double-click this line.
Access opens a dialog, with 3 options. Choose the one that says:
All records from tblDate, and any matches from [your other table]

That's an outer join, and gives you the results you want.

(Hopefully you don't really have fields named Date and Name: both these
field names will cause you grief in Access.)

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

 

[John Spencer]

The Dates have to come from somewhere. One solution is to build a calendar
table in your database that has one record for every date in the range you
might be interested in.

Then you could join your table to the calendar table with an outer join to
get the results you want.

SELECT C.TheDate, Y.[Name]
FROM CalendarTable as C LEFT JOIN [YourTable] as Y
ON C.TheDate = Y.[Date]
WHERE C.TheDate Between #1/1/2008# and #1/10/2008#

In the design view (query grid)
-- Add the two tables
-- Set up a relationship by dragging from TheDate to the Date field
-- Double-click on the line and select ALL from the CalendarTable and only
from your table
-- Select TheDate field from the calendar table and enter your criteria
under it
-- Select the Name field from your table

By the way, if your fields are really named Date and Name, you should
consider changing them as both those words have specific meanings in Access
and VBA. You could get some strange results.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

I have a table like this:
DATE | NAME
01/01/2008 Name 1
01/03/2008 Name 2
01/06/2008 Name 3

Ok. Now, I'd like to SELECT all records between '01/01/2008' AND '01/10/2008'.
But, the normal response from Access is to give me just the 3 records in the
table. But I need to get the following response:
DATE | NAME
01/01/2008 Name 1
01/02/2008 NULL
01/03/2008 Name 2
01/04/2008 NULL
01/05/2008 NULL
01/06/2008 Name 3
01/07/2008 NULL
01/08/2008 NULL
01/09/2008 NULL
01/10/2008 NULL

To make up records that don't exist, you need a table with
records. You could use a table with all possible dates, but
that might be a pain to maintain over a long period of time.

Instead, I like to use a utility table that has many
potential uses. I name this table Numbers and it has one
field named Num. The records have values 1, 2, 4, ... up to
more than you will ever need.

With all that in place, you could use a query like:

SELECT [Start Date] + Num - 1 As SaleDate, [Name]
FROM Numbers LEFT JOIN yourtable
ON [Start Date] + Num - 1 = yourtable.Date
WHERE yourtable.Date Between [Start Date] And [End Date]

The query design grid can not represent that kind of join so
you will have to stay in SQL view.

I hope you are aware that using reserved words like Date and
Name as field names is just begging for trouble.

 

[Danilo]

Marshall,

I tried to run the code, but I got an error: "Join expression is not
supported"

Here's the query:
SELECT #01/01/2008# + Num - 1 As oDate, testName
FROM Numbers LEFT JOIN test
On #01/01/2008# + Num - 1 = test.testDate
WHERE test.testDate Between #01/01/2008# And #01/10/2008#

Can you help me with that?
Tks
Danilo

I have a table like this:
DATE | NAME
01/01/2008 Name 1
01/03/2008 Name 2
01/06/2008 Name 3

Ok. Now, I'd like to SELECT all records between '01/01/2008' AND '01/10/2008'.
But, the normal response from Access is to give me just the 3 records in the
table. But I need to get the following response:
DATE | NAME
01/01/2008 Name 1
01/02/2008 NULL
01/03/2008 Name 2
01/04/2008 NULL
01/05/2008 NULL
01/06/2008 Name 3
01/07/2008 NULL
01/08/2008 NULL
01/09/2008 NULL
01/10/2008 NULL

To make up records that don't exist, you need a table with
records. You could use a table with all possible dates, but
that might be a pain to maintain over a long period of time.

Instead, I like to use a utility table that has many
potential uses. I name this table Numbers and it has one
field named Num. The records have values 1, 2, 4, ... up to
more than you will ever need.

With all that in place, you could use a query like:

SELECT [Start Date] + Num - 1 As SaleDate, [Name]
FROM Numbers LEFT JOIN yourtable
ON [Start Date] + Num - 1 = yourtable.Date
WHERE yourtable.Date Between [Start Date] And [End Date]

The query design grid can not represent that kind of join so
you will have to stay in SQL view.

I hope you are aware that using reserved words like Date and
Name as field names is just begging for trouble.

 

[Danilo]

Thanks Marsh,

The query works now, but there's no results.
I'd like to get all dates from 01/01 to 01/10.
What numbers should I have in the numbers table?

Now, for test, I have from 1 to 20. The query returns dates from 01/10 to
01/20.

Tks

 

[Marshall Barton]

Marshall,

I tried to run the code, but I got an error: "Join expression is not
supported"

Here's the query:
SELECT #01/01/2008# + Num - 1 As oDate, testName
FROM Numbers LEFT JOIN test
On #01/01/2008# + Num - 1 = test.testDate
WHERE test.testDate Between #01/01/2008# And #01/10/2008#

So much for posting air code. Here's a tested version with
a complete Where clause:

SELECT #01/01/2008# + Num - 1 As oDate, testName
FROM Numbers LEFT JOIN test
On #01/01/2008# + Numbers.Num - 1 = test.testDate
WHERE (testDate Between #01/01/2008# And #01/10/2008#
OR testDate Is Null)
AND Num >= #01/10/2008# - #01/01/2008# +1

I am still being kind of sloppy doing date arithmetic
instead of using DateAdd and DateDiff, but that could be a
severe performance drag.

 

[Marshall Barton]

1 through 20 is adequate for now. Eventually, I would
expect it to be 99 or 999 or ??

The AND Num >= #01/10/2008# - #01/01/2008# +1 part of the
Where clause was supposed to take care of limiting the dates
in the result. Unfortunately, somehow I managed to mangle
that too. The > was supposed to be <
AND Num <= #01/10/2008# - #01/01/2008# + 1

You should explain the results instead of saying "there's no
results" and post a Copy/Paste of the query as you tested
it.