Performing lookup on a query

[tanhus]

Right now I have a query "query1" that contains 3 fields
[Deal No] [amount] [trans_dt]
10000 10 01/01/2007
10000 20 15/01/2007
10000 30 15/02/2007
10001 15 10/01/2007
10001 25 15/03/2007
.... ... ...

What i want to do with is have a form that has a position date say
31/01/2007 and i want to build a query so that for every [Deal No] it lists
the previous [trans_dt] before the position date and lists the next
[trans_dt] after the position date, so that the query would look like this

[Deal No] [prev_trans_dt] [next_trans_dt]
10000 15/01/2007 15/02/2007
10000 10/01/2007 15/03/2007

Thanks to those who can help

 

[KARL DEWEY]

Your example does not follow what you said.

But try this anyway ---
Have your date in a unbound textbox on a form and open. Use the text box as
criteria for two TOP 1 queries - one descending the other ascending.
Criteria will be greater than for ascending and less than for the descending
one. Union query them together.

 

[tanhus]

I didnt used what you told me to do but I did figure how to do it using Max
and Min

SELECT dbo_gltnrpt.deal_no AS [Deal No],
Max(IIf([trans_dt]<#1/31/2007#,[trans_dt],[settle_dt])) AS [prev_trans_dt],
Min(IIf([trans_dt]>#1/31/2007#,[trans_dt],Null)) AS [next_trans_dt],
dbo_gltnrpt.trans_dt, IIf([trans_dt]>#1/31/2007#,[amount],Null) AS asdf
GROUP BY dbo_gltnrpt.deal_no;

But now i need to figure out how to add in the [amount] field. This amount
is the amount on the [next_trans_dt] for each deal.

Your example does not follow what you said.

But try this anyway ---
Have your date in a unbound textbox on a form and open. Use the text box as
criteria for two TOP 1 queries - one descending the other ascending.
Criteria will be greater than for ascending and less than for the descending
one. Union query them together.
--
KARL DEWEY
Build a little - Test a little

Right now I have a query "query1" that contains 3 fields
[Deal No] [amount] [trans_dt]
10000 10 01/01/2007
10000 20 15/01/2007
10000 30 15/02/2007
10001 15 10/01/2007
10001 25 15/03/2007
... ... ...

What i want to do with is have a form that has a position date say
31/01/2007 and i want to build a query so that for every [Deal No] it lists
the previous [trans_dt] before the position date and lists the next
[trans_dt] after the position date, so that the query would look like this

[Deal No] [prev_trans_dt] [next_trans_dt]
10000 15/01/2007 15/02/2007
10000 10/01/2007 15/03/2007

Thanks to those who can help

 

[KARL DEWEY]

Is this what you are looking for ----
SELECT dbo_gltnrpt.deal_no AS [Deal No], dbo_gltnrpt.trans_dt,
Max(IIf([trans_dt]<#1/31/2007#,[trans_dt],[settle_dt])) AS prev_trans_dt,
Min(IIf([trans_dt]>#1/31/2007#,[trans_dt],Null)) AS next_trans_dt,
IIf([trans_dt]>#1/31/2007#,[amount],Null) AS asdf
FROM dbo_gltnrpt
GROUP BY dbo_gltnrpt.deal_no, dbo_gltnrpt.trans_dt,
IIf([trans_dt]>#1/31/2007#,[amount],Null)
HAVING (((IIf([trans_dt]>#1/31/2007#,[amount],Null)) Is Not Null));

--
KARL DEWEY
Build a little - Test a little

I didnt used what you told me to do but I did figure how to do it using Max
and Min

SELECT dbo_gltnrpt.deal_no AS [Deal No],
Max(IIf([trans_dt]<#1/31/2007#,[trans_dt],[settle_dt])) AS [prev_trans_dt],
Min(IIf([trans_dt]>#1/31/2007#,[trans_dt],Null)) AS [next_trans_dt],
dbo_gltnrpt.trans_dt, IIf([trans_dt]>#1/31/2007#,[amount],Null) AS asdf
GROUP BY dbo_gltnrpt.deal_no;

But now i need to figure out how to add in the [amount] field. This amount
is the amount on the [next_trans_dt] for each deal.

Your example does not follow what you said.

But try this anyway ---
Have your date in a unbound textbox on a form and open. Use the text box as
criteria for two TOP 1 queries - one descending the other ascending.
Criteria will be greater than for ascending and less than for the descending
one. Union query them together.
--
KARL DEWEY
Build a little - Test a little

Right now I have a query "query1" that contains 3 fields
[Deal No] [amount] [trans_dt]
10000 10 01/01/2007
10000 20 15/01/2007
10000 30 15/02/2007
10001 15 10/01/2007
10001 25 15/03/2007
... ... ...

What i want to do with is have a form that has a position date say
31/01/2007 and i want to build a query so that for every [Deal No] it lists
the previous [trans_dt] before the position date and lists the next
[trans_dt] after the position date, so that the query would look like this

[Deal No] [prev_trans_dt] [next_trans_dt]
10000 15/01/2007 15/02/2007
10000 10/01/2007 15/03/2007

Thanks to those who can help

 

[tanhus]

Unfortunately what im trying to do wont work or i just dont know how to
figure it out. What i need to do is figure out how i can use VBA code to look
through this "query1" and pull out the [prev_trans_dt], [next_trans_dt] and
[amount] for each deal then insert it into a serperate table. Do you know how
I can loop through a query?

Is this what you are looking for ----
SELECT dbo_gltnrpt.deal_no AS [Deal No], dbo_gltnrpt.trans_dt,
Max(IIf([trans_dt]<#1/31/2007#,[trans_dt],[settle_dt])) AS prev_trans_dt,
Min(IIf([trans_dt]>#1/31/2007#,[trans_dt],Null)) AS next_trans_dt,
IIf([trans_dt]>#1/31/2007#,[amount],Null) AS asdf
FROM dbo_gltnrpt
GROUP BY dbo_gltnrpt.deal_no, dbo_gltnrpt.trans_dt,
IIf([trans_dt]>#1/31/2007#,[amount],Null)
HAVING (((IIf([trans_dt]>#1/31/2007#,[amount],Null)) Is Not Null));

--
KARL DEWEY
Build a little - Test a little

I didnt used what you told me to do but I did figure how to do it using Max
and Min

SELECT dbo_gltnrpt.deal_no AS [Deal No],
Max(IIf([trans_dt]<#1/31/2007#,[trans_dt],[settle_dt])) AS [prev_trans_dt],
Min(IIf([trans_dt]>#1/31/2007#,[trans_dt],Null)) AS [next_trans_dt],
dbo_gltnrpt.trans_dt, IIf([trans_dt]>#1/31/2007#,[amount],Null) AS asdf
GROUP BY dbo_gltnrpt.deal_no;

But now i need to figure out how to add in the [amount] field. This amount
is the amount on the [next_trans_dt] for each deal.

Your example does not follow what you said.

But try this anyway ---
Have your date in a unbound textbox on a form and open. Use the text box as
criteria for two TOP 1 queries - one descending the other ascending.
Criteria will be greater than for ascending and less than for the descending
one. Union query them together.
--
KARL DEWEY
Build a little - Test a little


:

Right now I have a query "query1" that contains 3 fields
[Deal No] [amount] [trans_dt]
10000 10 01/01/2007
10000 20 15/01/2007
10000 30 15/02/2007
10001 15 10/01/2007
10001 25 15/03/2007
... ... ...

What i want to do with is have a form that has a position date say
31/01/2007 and i want to build a query so that for every [Deal No] it lists
the previous [trans_dt] before the position date and lists the next
[trans_dt] after the position date, so that the query would look like this

[Deal No] [prev_trans_dt] [next_trans_dt]
10000 15/01/2007 15/02/2007
10000 10/01/2007 15/03/2007

Thanks to those who can help

 

[KARL DEWEY]

Did the SQL statement that I posted pull the data as you wanted?
What do you mean by this?

--
KARL DEWEY
Build a little - Test a little

Unfortunately what im trying to do wont work or i just dont know how to
figure it out. What i need to do is figure out how i can use VBA code to look
through this "query1" and pull out the [prev_trans_dt], [next_trans_dt] and
[amount] for each deal then insert it into a serperate table. Do you know how
I can loop through a query?

Is this what you are looking for ----
SELECT dbo_gltnrpt.deal_no AS [Deal No], dbo_gltnrpt.trans_dt,
Max(IIf([trans_dt]<#1/31/2007#,[trans_dt],[settle_dt])) AS prev_trans_dt,
Min(IIf([trans_dt]>#1/31/2007#,[trans_dt],Null)) AS next_trans_dt,
IIf([trans_dt]>#1/31/2007#,[amount],Null) AS asdf
FROM dbo_gltnrpt
GROUP BY dbo_gltnrpt.deal_no, dbo_gltnrpt.trans_dt,
IIf([trans_dt]>#1/31/2007#,[amount],Null)
HAVING (((IIf([trans_dt]>#1/31/2007#,[amount],Null)) Is Not Null));

--
KARL DEWEY
Build a little - Test a little

I didnt used what you told me to do but I did figure how to do it using Max
and Min

SELECT dbo_gltnrpt.deal_no AS [Deal No],
Max(IIf([trans_dt]<#1/31/2007#,[trans_dt],[settle_dt])) AS [prev_trans_dt],
Min(IIf([trans_dt]>#1/31/2007#,[trans_dt],Null)) AS [next_trans_dt],
dbo_gltnrpt.trans_dt, IIf([trans_dt]>#1/31/2007#,[amount],Null) AS asdf
GROUP BY dbo_gltnrpt.deal_no;

But now i need to figure out how to add in the [amount] field. This amount
is the amount on the [next_trans_dt] for each deal.

:

Your example does not follow what you said.

But try this anyway ---
Have your date in a unbound textbox on a form and open. Use the text box as
criteria for two TOP 1 queries - one descending the other ascending.
Criteria will be greater than for ascending and less than for the descending
one. Union query them together.
--
KARL DEWEY
Build a little - Test a little


:

Right now I have a query "query1" that contains 3 fields
[Deal No] [amount] [trans_dt]
10000 10 01/01/2007
10000 20 15/01/2007
10000 30 15/02/2007
10001 15 10/01/2007
10001 25 15/03/2007
... ... ...

What i want to do with is have a form that has a position date say
31/01/2007 and i want to build a query so that for every [Deal No] it lists
the previous [trans_dt] before the position date and lists the next
[trans_dt] after the position date, so that the query would look like this

[Deal No] [prev_trans_dt] [next_trans_dt]
10000 15/01/2007 15/02/2007
10000 10/01/2007 15/03/2007

Thanks to those who can help

 

[tanhus]

Unfortunately it didnt.

Did the SQL statement that I posted pull the data as you wanted?
What do you mean by this?

--
KARL DEWEY
Build a little - Test a little

Unfortunately what im trying to do wont work or i just dont know how to
figure it out. What i need to do is figure out how i can use VBA code to look
through this "query1" and pull out the [prev_trans_dt], [next_trans_dt] and
[amount] for each deal then insert it into a serperate table. Do you know how
I can loop through a query?

Is this what you are looking for ----
SELECT dbo_gltnrpt.deal_no AS [Deal No], dbo_gltnrpt.trans_dt,
Max(IIf([trans_dt]<#1/31/2007#,[trans_dt],[settle_dt])) AS prev_trans_dt,
Min(IIf([trans_dt]>#1/31/2007#,[trans_dt],Null)) AS next_trans_dt,
IIf([trans_dt]>#1/31/2007#,[amount],Null) AS asdf
FROM dbo_gltnrpt
GROUP BY dbo_gltnrpt.deal_no, dbo_gltnrpt.trans_dt,
IIf([trans_dt]>#1/31/2007#,[amount],Null)
HAVING (((IIf([trans_dt]>#1/31/2007#,[amount],Null)) Is Not Null));

--
KARL DEWEY
Build a little - Test a little


:

I didnt used what you told me to do but I did figure how to do it using Max
and Min

SELECT dbo_gltnrpt.deal_no AS [Deal No],
Max(IIf([trans_dt]<#1/31/2007#,[trans_dt],[settle_dt])) AS [prev_trans_dt],
Min(IIf([trans_dt]>#1/31/2007#,[trans_dt],Null)) AS [next_trans_dt],
dbo_gltnrpt.trans_dt, IIf([trans_dt]>#1/31/2007#,[amount],Null) AS asdf
GROUP BY dbo_gltnrpt.deal_no;

But now i need to figure out how to add in the [amount] field. This amount
is the amount on the [next_trans_dt] for each deal.

:

Your example does not follow what you said.

But try this anyway ---
Have your date in a unbound textbox on a form and open. Use the text box as
criteria for two TOP 1 queries - one descending the other ascending.
Criteria will be greater than for ascending and less than for the descending
one. Union query them together.
--
KARL DEWEY
Build a little - Test a little


:

Right now I have a query "query1" that contains 3 fields
[Deal No] [amount] [trans_dt]
10000 10 01/01/2007
10000 20 15/01/2007
10000 30 15/02/2007
10001 15 10/01/2007
10001 25 15/03/2007
... ... ...

What i want to do with is have a form that has a position date say
31/01/2007 and i want to build a query so that for every [Deal No] it lists
the previous [trans_dt] before the position date and lists the next
[trans_dt] after the position date, so that the query would look like this

[Deal No] [prev_trans_dt] [next_trans_dt]
10000 15/01/2007 15/02/2007
10000 10/01/2007 15/03/2007

Thanks to those who can help

 

[tanhus]

I want to lookup the [Deal No] field in "query1" and loop through each Deal
No and figure out the trans_dt prior to the unbound text box that is going to
be in my form. This VBA code will be processed when I hit a button.

Did the SQL statement that I posted pull the data as you wanted?
What do you mean by this?

--
KARL DEWEY
Build a little - Test a little

Unfortunately what im trying to do wont work or i just dont know how to
figure it out. What i need to do is figure out how i can use VBA code to look
through this "query1" and pull out the [prev_trans_dt], [next_trans_dt] and
[amount] for each deal then insert it into a serperate table. Do you know how
I can loop through a query?

Is this what you are looking for ----
SELECT dbo_gltnrpt.deal_no AS [Deal No], dbo_gltnrpt.trans_dt,
Max(IIf([trans_dt]<#1/31/2007#,[trans_dt],[settle_dt])) AS prev_trans_dt,
Min(IIf([trans_dt]>#1/31/2007#,[trans_dt],Null)) AS next_trans_dt,
IIf([trans_dt]>#1/31/2007#,[amount],Null) AS asdf
FROM dbo_gltnrpt
GROUP BY dbo_gltnrpt.deal_no, dbo_gltnrpt.trans_dt,
IIf([trans_dt]>#1/31/2007#,[amount],Null)
HAVING (((IIf([trans_dt]>#1/31/2007#,[amount],Null)) Is Not Null));

--
KARL DEWEY
Build a little - Test a little


:

I didnt used what you told me to do but I did figure how to do it using Max
and Min

SELECT dbo_gltnrpt.deal_no AS [Deal No],
Max(IIf([trans_dt]<#1/31/2007#,[trans_dt],[settle_dt])) AS [prev_trans_dt],
Min(IIf([trans_dt]>#1/31/2007#,[trans_dt],Null)) AS [next_trans_dt],
dbo_gltnrpt.trans_dt, IIf([trans_dt]>#1/31/2007#,[amount],Null) AS asdf
GROUP BY dbo_gltnrpt.deal_no;

But now i need to figure out how to add in the [amount] field. This amount
is the amount on the [next_trans_dt] for each deal.

:

Your example does not follow what you said.

But try this anyway ---
Have your date in a unbound textbox on a form and open. Use the text box as
criteria for two TOP 1 queries - one descending the other ascending.
Criteria will be greater than for ascending and less than for the descending
one. Union query them together.
--
KARL DEWEY
Build a little - Test a little


:

Right now I have a query "query1" that contains 3 fields
[Deal No] [amount] [trans_dt]
10000 10 01/01/2007
10000 20 15/01/2007
10000 30 15/02/2007
10001 15 10/01/2007
10001 25 15/03/2007
... ... ...

What i want to do with is have a form that has a position date say
31/01/2007 and i want to build a query so that for every [Deal No] it lists
the previous [trans_dt] before the position date and lists the next
[trans_dt] after the position date, so that the query would look like this

[Deal No] [prev_trans_dt] [next_trans_dt]
10000 15/01/2007 15/02/2007
10000 10/01/2007 15/03/2007

Thanks to those who can help

 

[KARL DEWEY]

Unfortunately it didnt.
What did it not do? What did it do wrong? I can not help without some clues.

I want to lookup the [Deal No] field in "query1" and loop through each Deal

No and figure out the trans_dt prior to the unbound text box that is going to
be in my form. This VBA code will be processed when I hit a button.

Use this as criteria in your query changing the form and object names ---
[Forms]![YourFormName]![YourTextBox]