Issue with Between Fuction in Query

[Haynesey]

Hi,

I am trying to produce a query that shows how many appointments are available each day for a particular contractor.

I have used the Between [Start Date:] and [End Date:] function in my query to select the dat range that I want to query.

However, this only brings back the dates where appointments have already been made. I want all dates to be displayed, even if no appointments have been made.

For example (If I choose the date range 01/10/10 to 31/10/10), at the moment it displays:

Contractor1 Date Apps MaxApps AppsRemaining
P H Jones 01/10/2010 25 40 15
P H Jones 04/10/2010 48 40 -8
P H Jones 05/10/2010 26 40 14
P H Jones 06/10/2010 10 40 30
P H Jones 07/10/2010 9 40 31
P H Jones 08/10/2010 6 40 34
P H Jones 11/10/2010 16 40 24
P H Jones 12/10/2010 1 40 39
P H Jones 13/10/2010 2 40 38
P H Jones 14/10/2010 22 40 18
P H Jones 15/10/2010 40 40 0
P H Jones 18/10/2010 31 40 9
P H Jones 19/10/2010 32 40 8
P H Jones 20/10/2010 40 40 0
P H Jones 21/10/2010 29 40 11

What I want to display is:

Contractor1 Date Apps MaxApps AppsRemaining
P H Jones 01/10/2010 25 40 15
P H Jones 02/10/2010 0 40 40
P H Jones 03/10/2010 0 40 40
P H Jones 04/10/2010 48 40 -8
P H Jones 05/10/2010 26 40 14
P H Jones 06/10/2010 10 40 30
P H Jones 07/10/2010 9 40 31
P H Jones 08/10/2010 6 40 34
P H Jones 09/10/2010 0 40 40
P H Jones 10/10/2010 0 40 40
P H Jones 11/10/2010 16 40 24
P H Jones 12/10/2010 1 40 39
P H Jones 13/10/2010 2 40 38
P H Jones 14/10/2010 22 40 18
P H Jones 15/10/2010 40 40 0
P H Jones 16/10/2010 0 40 40
P H Jones 17/10/2010 0 40 40
P H Jones 18/10/2010 31 40 9
P H Jones 19/10/2010 32 40 8
P H Jones 20/10/2010 40 40 0
P H Jones 21/10/2010 29 40 11
P H Jones 22/10/2010 0 40 40
P H Jones 23/10/2010 0 40 40
P H Jones 24/10/2010 0 40 40
P H Jones 25/10/2010 0 40 40
P H Jones 26/10/2010 0 40 40
P H Jones 27/10/2010 0 40 40
P H Jones 28/10/2010 0 40 40
P H Jones 29/10/2010 0 40 40
P H Jones 30/10/2010 0 40 40
P H Jones 31/10/2010 0 40 40

Hope this makes sense

Thanks in advance

Lee

 

[DCrake]

If there is no data in a table for a particular date then how do you expect it to return a value?

You would need to have a table that has all dates in it linked to each person