[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need help with query

Posted on 2014-08-06
10
Medium Priority
?
169 Views
Last Modified: 2014-09-04
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
0
Comment
Question by:rfedorov
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 500 total points
ID: 40244246
Something like this?

SELECT ranges.[range#]
    , log.*
FROM ranges
    JOIN log
        ON ranges.WeekFrom <= log.date
            AND log.date <= ranges.WeekTo

Open in new window

0
 

Author Comment

by:rfedorov
ID: 40244290
thanks for the respond,
Is there a Join in access? I know about Union??? does not work
0
 
LVL 23

Expert Comment

by:nemws1
ID: 40244301
There sure is a JOIN in access.  It might be "INNER JOIN", though.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 500 total points
ID: 40244325
select range# from ranges
where exists
(select 1 from log
 where date between ranges.week_from and ranges.weekto)
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 40245083
Access does support joins, e.g.
http://msdn.microsoft.com/en-us/library/bb208854(v=office.12).aspx
http://msdn.microsoft.com/en-us/library/bb208894(v=office.12).aspx

I think you want an inner join based on the logged date falling between the range boundaries

HOWEVER, if your log data has both date and TIME you may be in for a shock because your ranges exclude an almost 24 hour period e.g.  look for the bold below

INSERT INTO Ranges
      ([RangeID], [WeekFrom], [WeekTo], [Range])
VALUES
      (1, '2014-01-03 00:00:00', '2014-01-09 00:00:00', '1/3/2014-1/9/2014'),
      (2, '2014-01-10 00:00:00', '2014-01-16 00:00:00', '1/10/2014-1/16/2014'),

IF your logged dates are just that date without time, then the following should work:

|                          EVENT | RANGEID |                       WEEKFROM |                         WEEKTO |             RANGE |
|--------------------------------|---------|--------------------------------|--------------------------------|-------------------|
| January, 03 2014 00:00:00+0000 |       1 | January, 03 2014 00:00:00+0000 | January, 09 2014 00:00:00+0000 | 1/3/2014-1/9/2014 |

select
*
from log
inner join ranges on log.event between ranges.WeekFrom and ranges.WeekTo



CREATE TABLE Ranges
	([RangeID] int, [WeekFrom] datetime, [WeekTo] datetime, [Range] varchar(19))
;
	
INSERT INTO Ranges
	([RangeID], [WeekFrom], [WeekTo], [Range])
VALUES
	(1, '2014-01-03 00:00:00', '2014-01-09 00:00:00', '1/3/2014-1/9/2014'),
	(2, '2014-01-10 00:00:00', '2014-01-16 00:00:00', '1/10/2014-1/16/2014'),
	(3, '2014-01-17 00:00:00', '2014-01-23 00:00:00', '1/17/2014-1/23/2014'),
	(4, '2014-01-24 00:00:00', '2014-01-30 00:00:00', '1/24/2014-1/30/2014'),
	(5, '2014-01-31 00:00:00', '2014-02-06 00:00:00', '1/31/2014-2/6/2014'),
	(6, '2014-02-07 00:00:00', '2014-02-13 00:00:00', '2/7/2014-2/13/2014'),
	(7, '2014-02-14 00:00:00', '2014-02-20 00:00:00', '2/14/2014-2/20/2014'),
	(8, '2014-02-21 00:00:00', '2014-02-27 00:00:00', '2/21/2014-2/27/2014')
;

CREATE TABLE Log
	([Event] datetime)
;
	
INSERT INTO Log
	([Event])
VALUES
	('2014-01-03 00:00:00')
;
http://sqlfiddle.com/#!3/4c859/1

Open in new window

0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40245713
It is very simple and plain:

Select
    tblLog.Date,
    [Range#]
From
    tblRange,
    tblLog
Where
    tblLog.Date Between tblRange.WeekFrom And tblRange.WeekTo

/gustav
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40245938
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.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40245946
?
Between .. And is inclusive.

/gustav
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40246170
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"
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40246255
I know, but the sample data doesn't expose time values.

/gustav
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question