By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 2,091 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

How to fill/link a query criteria based on another query result???

P: 4
Hi there,

basically what I am trying to do (without using code as I donít know how to do that with a code) is to retrieve the previous 5 days & the next 5 days (date wise)against a query1 that come up with a date against a user input value?

Query1
user input = 202
result one or more dates where "202" is associated with

query2
use each of query1 dates and retrieve values for the past 5 days & next 5 days (date wise)

Data is structured using one to many relationship

TblDLA
DLAValue - Primary key field

TblAllData
Date - Primary Key
DLAValue Foreign Key

hope make sense

thanks for you help
Jan 4 '07 #1
Share this Question
Share on Google+
7 Replies


100+
P: 1,646
Hi there,

basically what I am trying to do (without using code as I donít know how to do that with a code) is to retrieve the previous 5 days & the next 5 days (date wise)against a query1 that come up with a date against a user input value?

Query1
user input = 202
result one or more dates where "202" is associated with

query2
use each of query1 dates and retrieve values for the past 5 days & next 5 days (date wise)

Data is structured using one to many relationship

TblDLA
DLAValue - Primary key field

TblAllData
Date - Primary Key
DLAValue Foreign Key

hope make sense

thanks for you help
Hi if you are looking for a sql statement try this:
Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM TblAllData WHERE DLAValue = 202 AND " _
  2. & "TheDate BETWEEN DATEADD(day, 5, DATE()) AND DATEADD(day, -5, DATE())"
I have changed the name of the date field because Date is a reserved word.
Jan 4 '07 #2

P: 4
Thanks for your reply,

your two lines of code make a lot sense and even a newbie like me can fully understand the logic behind it, however as I stated I really donít know where to go to write that SQL statement!! (I guess I have a lot to learn)

anyway does that mean that the standard Query in access does not support such link between two queries. foe example query2 can never trigger query1 and retrieve the required data to process in query2. am I asking tooooooooo much from a standard access query!

if yes maybe I should start learning SQL as the above was my first obstacle and to solve I need to use code (I have a lot to ask about!!!). do you guys recommend any sites, books, tutorials that I should start reading to educate my self with SQL, noting I am very new in database (Excel oriented)

long way to go :-(
Many thanks
Jan 4 '07 #3

100+
P: 1,646
Thanks for your reply,

your two lines of code make a lot sense and even a newbie like me can fully understand the logic behind it, however as I stated I really donít know where to go to write that SQL statement!! (I guess I have a lot to learn)

anyway does that mean that the standard Query in access does not support such link between two queries. foe example query2 can never trigger query1 and retrieve the required data to process in query2. am I asking tooooooooo much from a standard access query!

if yes maybe I should start learning SQL as the above was my first obstacle and to solve I need to use code (I have a lot to ask about!!!). do you guys recommend any sites, books, tutorials that I should start reading to educate my self with SQL, noting I am very new in database (Excel oriented)

long way to go :-(
Many thanks
In Access when you are looking at the query builder you can view it in sql and write the code directly as above. Your question about using one query result to call another query makes little sense to me given the information you have supplied thus far. Perhaps you could let us know what you want to retrieve finally from your tables and why you need to query the first table to get it
Jan 4 '07 #4

P: 4
Thanks again for your help

The purpose of the first query is to retrieve the dates of the user input value
and the 2nd query is to get these dates and retrieve the past & next 5days against each day, for example

Query1
user input 202

Query1 Output
Date DLA
30/11/2005 202
01/04/2006 202
28/06/2006 202

Query2
is to take each of the there dates above and show the past & next 5days like this (taking 30/11/2005 to illustrate):

Date DLA
25/11/2005 198
26/11/2005 188
27/11/2005 194
28/11/2005 196
29/11/2005 200
30/11/2005 202
01/12/2005 206
02/12/2005 204
03/12/2005 206
04/12/2005 200
05/12/2005 190

and the same shall also be applied on the other two days(01/04/2006 & 28/06/2006). hope the picture is much clear now.

Many Thanks
Jan 4 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
[quote=Triple7]Thanks for your reply,

your two lines of code make a lot sense and even a newbie like me can fully understand the logic behind it, however as I stated I really donít know where to go to write that SQL statement!! (I guess I have a lot to learn)
[/quore]

Open Access Query Design window and change the view to SQL then just copy and paste in the following which Will gave you.

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM TblAllData 
  2. WHERE DLAValue = 202 AND [Date] 
  3. BETWEEN DATEADD(day, 5, DATE()) AND DATEADD(day, -5, DATE());
anyway does that mean that the standard Query in access does not support such link between two queries. foe example query2 can never trigger query1 and retrieve the required data to process in query2. am I asking tooooooooo much from a standard access query!
You can do it in two queries if you want.

Query1 ...

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM TblAllData 
  2. WHERE DLAValue = 202;
Query2 ...

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM Query1 
  2. WHERE [Date] BETWEEN DATEADD(day, 5, DATE()) 
  3. AND DATEADD(day, -5, DATE());
if yes maybe I should start learning SQL as the above was my first obstacle and to solve I need to use code (I have a lot to ask about!!!). do you guys recommend any sites, books, tutorials that I should start reading to educate my self with SQL, noting I am very new in database (Excel oriented)
You can switch the view from the Access Designer to SQL view and back again. We normally request and post sql as it is too difficult to describe a query from access view.

Mary
Jan 5 '07 #6

P: 4
Thanks for your help

however thatís does not work as I donít know the date (which is 30/11/2005).
you SQL code will need user input for the date as I understand it
and the user does not know the dates!! (30/11/2005) is base on query1 output and query2 should automatically get that date(s) and do the required processing to show the past & next 5 days.

any suggestions!!!

Many thanks
Jan 5 '07 #7

NeoPa
Expert Mod 15k+
P: 31,276
As WillAKAWill says, it's not a good idea to use the name Date for a field. It is possible but certainly not recommended.
This is not remotely straightforward, as you are asking for ranges 'around' multiple dates. This needs to be done without table joins, as some records should appear more than once (If they are close to more than one of the returned dates from 'Query1').
Expand|Select|Wrap|Line Numbers
  1. SELECT T.Date,T.DLAValue
  2. FROM TblAllData AS T, (
  3. SELECT [Date]
  4. FROM TblAllData
  5. WHERE DLAValue=[Enter DLA to Show:]
  6. ORDER BY [Date]) AS Q
  7. WHERE T.Date Between Q.Date-5 And Q.Date+5
  8. ORDER BY T.Date
Jan 5 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.