Need help with query
i have a table for a custom range of the dates.
Range #, DateFrom, DateTo, Range
Range# WeekFrom WeekTo Range
1 1/3/2014 1/9/2014 1/3/2014-1/9/2014
2 1/10/2014 1/16/2014 1/10/2014-1/16/2014
3 1/17/2014 1/23/2014 1/17/2014-1/23/2014
4 1/24/2014 1/30/2014 1/24/2014-1/30/2014
5 1/31/2014 2/6/2014 1/31/2014-2/6/2014
6 2/7/2014 2/13/2014 2/7/2014-2/13/2014
7 2/14/2014 2/20/2014 2/14/2014-2/20/2014
8 2/21/2014 2/27/2014 2/21/2014-2/27/2014
as you can see that is a custom Date Table, where week starts at Friday and ends at Thursday
My second table shows the log of the event
Date
1/3/2014
how to write a query showing that that day belong to Range# 1, because this day happened between 1/3/2014 and 1/9/2014
Range #, DateFrom, DateTo, Range
Range# WeekFrom WeekTo Range
1 1/3/2014 1/9/2014 1/3/2014-1/9/2014
2 1/10/2014 1/16/2014 1/10/2014-1/16/2014
3 1/17/2014 1/23/2014 1/17/2014-1/23/2014
4 1/24/2014 1/30/2014 1/24/2014-1/30/2014
5 1/31/2014 2/6/2014 1/31/2014-2/6/2014
6 2/7/2014 2/13/2014 2/7/2014-2/13/2014
7 2/14/2014 2/20/2014 2/14/2014-2/20/2014
8 2/21/2014 2/27/2014 2/21/2014-2/27/2014
as you can see that is a custom Date Table, where week starts at Friday and ends at Thursday
My second table shows the log of the event
Date
1/3/2014
how to write a query showing that that day belong to Range# 1, because this day happened between 1/3/2014 and 1/9/2014
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There sure is a JOIN in access. It might be "INNER JOIN", though.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It is very simple and plain:
Select
tblLog.Date,
[Range#]
From
tblRange,
tblLog
Where
tblLog.Date Between tblRange.WeekFrom And tblRange.WeekTo
/gustav
Select
tblLog.Date,
[Range#]
From
tblRange,
tblLog
Where
tblLog.Date Between tblRange.WeekFrom And tblRange.WeekTo
/gustav
and it's also plain that 24 hours is missing from the defined ranges....
seriously
please don't ignore this
'2014-01-03 00:00:00', '2014-01-09 00:00:00' -> 24 hours missing, then the next start -> '2014-01-10 00:00:00',
2014-01-09 has 24 hours duration like every other day
The only way this does not matter is if the logged events only record the date, and not the time of day.
seriously
please don't ignore this
'2014-01-03 00:00:00', '2014-01-09 00:00:00' -> 24 hours missing, then the next start -> '2014-01-10 00:00:00',
2014-01-09 has 24 hours duration like every other day
The only way this does not matter is if the logged events only record the date, and not the time of day.
?
Between .. And is inclusive.
/gustav
Between .. And is inclusive.
/gustav
Yes, but only on the :00:00:00 point in time.
Between is the direct equivalent of this using one date range:
( field >= 2014-01-03 00:00:00 and field <= 2014-01-09 00:00:00 )
Between is not magic, it does NOT automatically include the missing 24 hours. As I said the only way this is not an issue is of the data has not time (other than 00:00:00).
I have to admit I'm not an Access expert (many years since I used it) but between is defined the same way in every product I have seen, for more on this please see: "Beware of Between"
Between is the direct equivalent of this using one date range:
( field >= 2014-01-03 00:00:00 and field <= 2014-01-09 00:00:00 )
Between is not magic, it does NOT automatically include the missing 24 hours. As I said the only way this is not an issue is of the data has not time (other than 00:00:00).
I have to admit I'm not an Access expert (many years since I used it) but between is defined the same way in every product I have seen, for more on this please see: "Beware of Between"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is there a Join in access? I know about Union??? does not work