How to create a new record if i have a start date and an end date

[Guest]

I read the below question and the answer but I still do not understand how
you got to what you wanted. I'm new to the expressions and the select. can
you please explain more to me on how you set up your query and where you put
this information in. for the expression, can you tell give me an example of
what you did for your query, type, fields, expression. etc. any help would
be appreciated.
Shannon

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

Answer:
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;

Please help if u can

 

[Michel Walsh]

HI,


You can type the SQL statement in the SQL view. In fact, since this is a non
equi join (a join ON a comparison that is not an equality test), you have
to: the query designer can only produces join with a comparison of equality
test ( like: SELECT ... FROM ... ON table1.field1 = table2.field2 ... )
The SQL view is required to type some kind of statements, other are UNION
query, as example.


First, you have to understand the math behind.

Two intervals [starting, finishing] and [begin, end] DO NOT overlap if

starting> end or finishing < begin


so, there is some kind of overlap (in part or in full) in the negative. Use
De Morgan's Law (Boolean logic) to negate the previous statement, you get:


starting <= end AND finishing >= begin



Once you have the criteria, you can put it in the WHERE clause, or, since it
implies two table, you can put it in an INNER JOIN condition, as proposed.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks a bunch Michel, I did not realize i had to find the SQL under the
Query tab. Thanks again and it worked perfectly. However, i still have the
error of "microsoft access can't represent the join expression
[table1].[start]<=[alldates].[thedate] in design view. as i said it works,
but any idea

HI,


You can type the SQL statement in the SQL view. In fact, since this is a non
equi join (a join ON a comparison that is not an equality test), you have
to: the query designer can only produces join with a comparison of equality
test ( like: SELECT ... FROM ... ON table1.field1 = table2.field2 ... )
The SQL view is required to type some kind of statements, other are UNION
query, as example.


First, you have to understand the math behind.

Two intervals [starting, finishing] and [begin, end] DO NOT overlap if

starting> end or finishing < begin


so, there is some kind of overlap (in part or in full) in the negative. Use
De Morgan's Law (Boolean logic) to negate the previous statement, you get:


starting <= end AND finishing >= begin



Once you have the criteria, you can put it in the WHERE clause, or, since it
implies two table, you can put it in an INNER JOIN condition, as proposed.



Hoping it may help,
Vanderghast, Access MVP

I read the below question and the answer but I still do not understand how
you got to what you wanted. I'm new to the expressions and the select.
can
you please explain more to me on how you set up your query and where you
put
this information in. for the expression, can you tell give me an example
of
what you did for your query, type, fields, expression. etc. any help
would
be appreciated.
Shannon

Answer:
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;

Please help if u can

 

[Michel Walsh]

Hi,


That is not an error as a limitation of the 'graphical' capabilities of the
query designer. In other word, that is WHY you have to do this query in SQL
view.


Hoping it may help,
Vanderghast, Access MVP