insert variables into Query

[Guest]

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 )

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 )

 

[Jeff Boyce]

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


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

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

 

[Guest]

"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

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


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

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

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 )

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 )

 

[Jeff Boyce]

I guess I don't understand, then. Are you saying that you are not able to
select the field [EndDate] from TableY into the query?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

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

"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

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


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

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

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 )

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 )

 

[Guest]

Correct.

I have tried to substitute the variables with In(select [EndDate] from
[tableY])

but I cant get the query to iterate.

so for each value in tableY produce a result set?
--
Learning SQL and Access

I guess I don't understand, then. Are you saying that you are not able to
select the field [EndDate] from TableY into the query?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

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

"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

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


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

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


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 )

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 )

 

[Jeff Boyce]

See my response to your other post...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

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

Correct.

I have tried to substitute the variables with In(select [EndDate] from
[tableY])

but I cant get the query to iterate.

so for each value in tableY produce a result set?
--
Learning SQL and Access

I guess I don't understand, then. Are you saying that you are not able to
select the field [EndDate] from TableY into the query?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

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

"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


:

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


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

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


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 )

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 )

 

[Jeff Boyce]

(i.e., your post on the subject of "In()" ... that is yours, right?)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

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

Correct.

I have tried to substitute the variables with In(select [EndDate] from
[tableY])

but I cant get the query to iterate.

so for each value in tableY produce a result set?
--
Learning SQL and Access

I guess I don't understand, then. Are you saying that you are not able to
select the field [EndDate] from TableY into the query?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

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

"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


:

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


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

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


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 )

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 )

 

[Guest]

Yup, thats mine as well.

but I have solved the problem.

You cant use the <In().

So I dragged the table into the Query and filter using the reference to the
filed with no joins to the other table.

I t works PERFECTLY!
--
Learning SQL and Access

(i.e., your post on the subject of "In()" ... that is yours, right?)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

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

Correct.

I have tried to substitute the variables with In(select [EndDate] from
[tableY])

but I cant get the query to iterate.

so for each value in tableY produce a result set?
--
Learning SQL and Access

I guess I don't understand, then. Are you saying that you are not able to
select the field [EndDate] from TableY into the query?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

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

"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


:

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


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

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


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 )

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 )