Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10065

Re: SQL query - apply ranges within 1 data column

Show all headers | View raw

remco.doorewaard@gmail.com wrote in
news:98ad8821-bb7a-4b9c-ac6f-b9f4edf7c0e4@googlegroups.com: 

> Op vrijdag 31 oktober 2014 18:51:31 UTC+1 schreef Patrick
> Finucane: 
>> On Friday, October 31, 2014 12:49:01 PM UTC-4, Dalsium wrote:
>> > Hi,
>> > 
>> > I have the following situation:
>> > 
>> > TABLE 1                         TABLE 2          
>> > ID     POOL     Date         Value     ID     POOL     Date
>> > 1     P907     1-1-2010     a     1     P907     1-8-2010
>> > 2     P907     1-1-2011     b     2     P907     1-11-2010
>> > 3     P907     1-1-2012     c     3     P907     1-7-2011
>> > 4     P911     1-1-2010     d     4     P911     1-1-2014
>> > 5     P911     1-1-2011     e                    
>> >                                    
>> > Return                                   
>> > POOL     Date         Value                         
>> > P907     1-8-2010     a                         
>> > P907     1-11-2010     a                         
>> > P907     1-7-2011     b                         
>> > P911     1-1-2014     e     
>> > 
>> > 
>> > Is it possible to get a return on a query as stated above?
>> > 
>> > For instance: date 1-8-2010 in table 2 lies within the range of
>> > 1-1-201 
> 0 till 1-1-2011 and the value of this period is a.
>> > 
>> > Thanks in advance,
>> > 
>> > D.
>> 
>> Open up the querybuilder.  Add Table1 and Table2. Draw a
>> relationship lin 
> e between the two tables on Pool.  Select the fields Table1.POOL,
> Table1.Value, Table2.Date.  Set the criteria to "A" in the Value
> column and enter Between #1/1/2010# And #1/1/2011# under the Date
> column.  Sort on Date.  Run. 
> 
> Thanks for your reply. But that means (if I understand your
> suggestion correctly) that for every combination (a and between
> #1/1/2010# And #1/1/2011#, b and between #1/1/2011# And #1/1/2012#
> and so on) I have to make criteria. I have a lot of records in
> table 1, so I'm going to need a criteria combination for each
> record this way. 
> 
> Is there another way to let the query determine what range within
> table 1 should be selected based on the date in table 2 and thus
> produce the appropriate value return? 
> 
If the interval in Table 1 is always one year, make the criteria for 
the table 2 date 
Between #[table 1].[date]# and #dateadd("yyyy",1,[table 1].[date])#

If the interval may vary, it'll be a lot more complicated.


-- 
Bob Q.
PA is y I've altered my address.

---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

SQL query - apply ranges within 1 data column Dalsium <remco.doorewaard@gmail.com> - 2014-10-31 09:48 -0700
  Re: SQL query - apply ranges within 1 data column Patrick Finucane <patrickfinucanetx@gmail.com> - 2014-10-31 10:51 -0700
    Re: SQL query - apply ranges within 1 data column remco.doorewaard@gmail.com - 2014-10-31 15:27 -0700
      Re: SQL query - apply ranges within 1 data column Bob Quintal <rquintal@sPAmpatico.ca> - 2014-10-31 23:17 +0000

csiph-web