Listing Dates in Range

[Guest]

I have two fields concerning dates. One is the start date, the other is the
end date. I am running a query to gather all people who have requested an
appointment but have not been assigned. They request any time between the
dates specified in those two fields.

How (if possible) can I create a result in the query for each date within
the specified range for each individual.

For example.
John Doe 7-19-2005 through 7-21-2005
will return results:
John Doe 7-19-2005
John Doe 7-20-2005
John Doe 7-21-2005

Thanks,
Kevin

 

[Guest]

Create a table with consecutive numbers zero through the maximum number of
days to fill. Create a query with your table and the count table not joined.

SELECT YourTable.Start, YourTable.End, [start]+[Count] AS [Interval Date]
FROM YourTable, [Count]
WHERE (((Count.Count)<=DateDiff("d",[start],[end])));

 

[John Vinson]

I have two fields concerning dates. One is the start date, the other is the
end date. I am running a query to gather all people who have requested an
appointment but have not been assigned. They request any time between the
dates specified in those two fields.

How (if possible) can I create a result in the query for each date within
the specified range for each individual.

For example.
John Doe 7-19-2005 through 7-21-2005
will return results:
John Doe 7-19-2005
John Doe 7-20-2005
John Doe 7-21-2005

Thanks,
Kevin

You'll need an auxiliary table with one record for each date which
might potentially be within the ranges you'll use. I'd suggest opening
Excel and using its spiffy "fill down" feature to create a spreadsheet
with dates from today through, say, the end of 2015. Import this
spreadsheet into a new table (AllDates let's say) in Access. Don't
name the field Date, that's a reserved word - I'll use TheDate as an
example.

You can then use a "non equi join" query:

SELECT PersonName, Alldates.TheDate
FROM yourtable INNER JOIN Alldates
ON yourtable.StartDate <= AllDates.TheDate
AND yourtable.EndDate >= AllDates.TheDate;

John W. Vinson[MVP]

 

[Guest]

This might be a stupid question, but what do you mean by "non equi join
query." Is that just two tables in a query with no join properties between
them?

Also, Where do I enter this? Is it in one of the rows on the Design Query
View?

SELECT PersonName, Alldates.TheDate
FROM yourtable INNER JOIN Alldates
ON yourtable.StartDate <= AllDates.TheDate
AND yourtable.EndDate >= AllDates.TheDate

 

[Guest]

Nevermind. I figured it out. Thanks very much.

One more for you...
Between the Start field and End field I have a Yes/No field whose title is
"or."
The idea is when this is not checked it would be 7-19-2005 through 7-21-2005.
When is is checked, it would be 7-19-2005 or 7-21-2005.

Is there a way i can factor that into the query; So if it is checked, it
will only return the start and end date and not those in between?

 

[John Vinson]

One more for you...
Between the Start field and End field I have a Yes/No field whose title is
"or."
The idea is when this is not checked it would be 7-19-2005 through 7-21-2005.
When is is checked, it would be 7-19-2005 or 7-21-2005.

Is there a way i can factor that into the query; So if it is checked, it
will only return the start and end date and not those in between?

ooh... tricky... <g>

Ok, try this:

SELECT PersonName, Alldates.TheDate
FROM yourtable INNER JOIN Alldates
ON yourtable.StartDate <= AllDates.TheDate
AND yourtable.EndDate >= AllDates.TheDate
WHERE [Or] = False
UNION
SELECT PersonName, Alldates.TheDate
FROM yourtable INNER JOIN Alldates
ON yourtable.StartDate = AllDates.TheDate
OR yourtable.EndDate = AllDates.TheDate
WHERE [Or] = True;

or perhaps use a Cartesian join:

SELECT PersonName, Alldates.TheDate
FROM yourtable, Alldates
WHERE ([OR] = False AND yourtable.StartDate <= AllDates.TheDate
AND yourtable.EndDate >= AllDates.TheDate)
OR (([OR] = True AND (yourtable.StartDate = AllDates.TheDate
OR yourtable.EndDate = AllDates.TheDate))


John W. Vinson[MVP]

 

[Guest]

Thank you very much. I really appreciate all your help.

-Kevin

One more for you...
Between the Start field and End field I have a Yes/No field whose title is
"or."
The idea is when this is not checked it would be 7-19-2005 through 7-21-2005.
When is is checked, it would be 7-19-2005 or 7-21-2005.

Is there a way i can factor that into the query; So if it is checked, it
will only return the start and end date and not those in between?

ooh... tricky... <g>

Ok, try this:

SELECT PersonName, Alldates.TheDate
FROM yourtable INNER JOIN Alldates
ON yourtable.StartDate <= AllDates.TheDate
AND yourtable.EndDate >= AllDates.TheDate
WHERE [Or] = False
UNION
SELECT PersonName, Alldates.TheDate
FROM yourtable INNER JOIN Alldates
ON yourtable.StartDate = AllDates.TheDate
OR yourtable.EndDate = AllDates.TheDate
WHERE [Or] = True;

or perhaps use a Cartesian join:

SELECT PersonName, Alldates.TheDate
FROM yourtable, Alldates
WHERE ([OR] = False AND yourtable.StartDate <= AllDates.TheDate
AND yourtable.EndDate >= AllDates.TheDate)
OR (([OR] = True AND (yourtable.StartDate = AllDates.TheDate
OR yourtable.EndDate = AllDates.TheDate))


John W. Vinson[MVP]