Re: insert variables into Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"Qtr_Value" is calcualated and I have found a way to do this.

All I need to know now is how do I substitue the tableY.EndDate into the
Query for each row in the tableY.

I want to get out of inserting subqueries simply to change one common value.

--
Learning SQL and Access


"Jeff Boyce" wrote:

A couple observations/questions...

Where are you getting your [Qtr_Value]? Is that a calculated value, based
on the date in your [EndDate] field? If so, a scan through this (queries)
and the tablesdbdesign newsgroups will show a strong consensus against
storing calculated values. If you can calculate it, do that, in a query
(this isn't an "ALWAYS" condition, but "USUALLY").

If you are storing text like "06-07 Qtr2", how are you proposing to compare
that to a date. You use
fieldB >= tableY.EndDate
as a WHERE condition, and I assume your "fieldB" is your [Qtr_Value].

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


"stephenson22" <stephenson22@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B7395BF6-DD86-4FAC-9AE6-1C11495D81F9@xxxxxxxxxxxxxxxx
I have a query: simple extract below.
---------------------------------------------------------------
select

fieldA
fieldB
tableY.Qtr_Value

from tableX

where

fieldA <= tableY.EndDate
And
fieldB >= tableY.EndDate
-----------------------------------------------------------
..........follow me so far :o)

I have a table (tableY) with the follwing values:

EndDate Qtr_Value
31/09/06 06-07 Qtr2
30/06/06 06-07 Qtr1
31/03/06 05-06 Qtr4


For every occurrance of a row in tableY I would like to insert the value
of
EndDate into the SQL query above and for each iteration insert the
corresponding value of Qtr_Value in the SQL query the desired result after
two iterations would look like this:

SQL iteration 1:
-------------------------
select

fieldA
fieldB
tableY.Qtr_Value

from tableX

where

fieldA <= 31/09/06
And
fieldB >= 31/09/06

Result 1:

fieldA, fieldB, Qtr_Value
value, value 2, 06-07 Qtr2

SQL interation 2
-------------------------
select

fieldA
fieldB
tableY.Qtr_Value

from tableX

where

fieldA <= 30/06/06
And
fieldB >= 30/06/06
Result 2:
fieldA, fieldB, Qtr_Value
value, value 2, 06-07 Qtr1

I hope I have made this clear enough for a genius to help me.

Please, please, please :o)
--
Learning SQL and Access


.

Relevant Pages

  • Re: insert variables into Query
    ... on the date in your [EndDate] field? ... fieldA <= tableY.EndDate ... fieldB>= tableY.EndDate ... SQL iteration 1: ...
    (microsoft.public.access.queries)
  • insert variables into Query
    ... from tableX ... fieldA <= tableY.EndDate ... fieldB>= tableY.EndDate ... EndDate into the SQL query above and for each iteration insert the ...
    (microsoft.public.access.queries)
  • Re: Incrementing a Counter
    ... field which is reset ... FieldA Counter ... I know I can do it with code, but can I with SQL? ... FieldB INTEGER NOT NULL, ...
    (microsoft.public.access.queries)
  • Re: constraint in Trigger
    ... CREATE TRIGGER tiu_name ON tblName ... IF (@FieldB IS NOT NULL) AND (@FieldA IS NULL) ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Forms - Mandatory field
    ... me to use a real e-mail address in public newsgroups. ... GlobalSign digital certificate is a forgery and should be deleted without ... FieldA and FieldB. ...
    (microsoft.public.access.forms)