trouble wiht query. check last record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI.
I have a table a with columns: date,symbol,action.

I want to return rows where the last action for every symbol = "Watch"
I am little confused how to go about this one.
 
Dear Seede:

I'm thinking you want one of these:

SELECT symbol, action, [date]
FROM YourTable
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable
WHERE T1.symbol = "Watch")

This produces those rows where symbol = "Watch" and which occur on the most
recent date of all those rows where symbol = "Watch".

Now, it might also be the case that you want the most recent date not only
of all values of symbol, but within the subset of those for each different
value of action. That would be:

SELECT symbol, action, [date]
FROM YourTable T
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable T1
WHERE symbol = "Watch"
AND T1.action = T.action)

You may find that these give considerably different results. Choose the one
that fits your requirements.

Please let me know if this helped, and if I can be of further help.

Tom Ellison
 
HI. i am almost there. Just need a bit of assitance. here is what i did
based on your input.

select * from tradediary tdd where tdd.symbol in (
select max(td.date_today) ,
td.symbol from tradediary td where td.action='Buy' group by td.symbol)
and tdd.symbol=td.symbol

the inner query works by itself for obvious reasons. how do i get the outer
query return results where the tdd.symbol =td.symbol

Thanks in advance for your help
Tom Ellison said:
Dear Seede:

I'm thinking you want one of these:

SELECT symbol, action, [date]
FROM YourTable
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable
WHERE T1.symbol = "Watch")

This produces those rows where symbol = "Watch" and which occur on the most
recent date of all those rows where symbol = "Watch".

Now, it might also be the case that you want the most recent date not only
of all values of symbol, but within the subset of those for each different
value of action. That would be:

SELECT symbol, action, [date]
FROM YourTable T
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable T1
WHERE symbol = "Watch"
AND T1.action = T.action)

You may find that these give considerably different results. Choose the one
that fits your requirements.

Please let me know if this helped, and if I can be of further help.

Tom Ellison


Seede said:
HI.
I have a table a with columns: date,symbol,action.

I want to return rows where the last action for every symbol = "Watch"
I am little confused how to go about this one.
Click to expand...
Click to expand...
 
Dear Seede:

Not making much sense. You said before that you wanted only symbol =
"Watch". Now your post indicates that you want the symbol to be correlated
between inner and outer queries. Which is it?

At the same time, you have added some restriction that action = "Buy" which
was not mentioned before.

Not knowing what you want, and getting contradictory information about it,
reduces my effectiveness. I recommend you show sample data and desired
output as well as explain things again.

You have switched to using an IN() clause, which I did not mention. The
IN() clause you represent makes no sense. First of all, while it is
perfectly acceptable to use a SELECT query to fill an IN() clause list, this
query can have only one column. You seem to want two columns. The first
column is a MAX(td.date_today) which is a single value result. If there is
a single value, IN() is not required. The simple = (SELECT MAX(DATE)
approach I proposed is sufficient.

Given your original requirements, and accomodating the column and table
names you have just revealed, the solution to your original post would be:

SELECT symbol, action, date_today
FROM tradediary T
WHERE symbol = "Watch"
AND date_today = (SELECT MAX(date_today)
FROM tradediary T1
WHERE symbol = "Watch"
AND T1.action = T.action)

Please run this and see if it matches your original requirements. Please
explain how it does not meet any new requirements.

Tom Ellison


Seede said:
HI. i am almost there. Just need a bit of assitance. here is what i did
based on your input.

select * from tradediary tdd where tdd.symbol in (
select max(td.date_today) ,
td.symbol from tradediary td where td.action='Buy' group by td.symbol)
and tdd.symbol=td.symbol

the inner query works by itself for obvious reasons. how do i get the
outer
query return results where the tdd.symbol =td.symbol

Thanks in advance for your help
Tom Ellison said:
Dear Seede:

I'm thinking you want one of these:

SELECT symbol, action, [date]
FROM YourTable
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable
WHERE T1.symbol = "Watch")

This produces those rows where symbol = "Watch" and which occur on the
most
recent date of all those rows where symbol = "Watch".

Now, it might also be the case that you want the most recent date not
only
of all values of symbol, but within the subset of those for each
different
value of action. That would be:

SELECT symbol, action, [date]
FROM YourTable T
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable T1
WHERE symbol = "Watch"
AND T1.action = T.action)

You may find that these give considerably different results. Choose the
one
that fits your requirements.

Please let me know if this helped, and if I can be of further help.

Tom Ellison


Seede said:
HI.
I have a table a with columns: date,symbol,action.

I want to return rows where the last action for every symbol = "Watch"
I am little confused how to go about this one.
Click to expand...
Click to expand...
Click to expand...
 
Apologies if i caused confusion. I am basically a Access beginner and here
is what i am trying to do. All i am trying to do is get all the symbols where
the action=Watch for the latest Date_Today.
Once i get all the symbols, i want to get all the rows related to that
symbol.
For eg. in the data sheet attached.
I want to return all rows for ADAT,AGT ... but not ANO.
The reason i want ADAT and AGT is because the max(date_today) for each
symbol is in the row where the action=Watch.
The reason i dont want ANO is because the max(date_today) for ANO is in the
row where the action=Buy.

Hope this clarifies. I would appreciate your assistance as i have been
scrating my head for the last 2 days.


for the last date_Today is for all dates where the max(date_today)
1. Table TradeDiary has following structure.
Date_Today Action Notes symbol
30/03/2006 Watch Watch Pullback ADAT
31/03/2006 Watch Watch pullback ADAT
28/03/2006 Watch Buy breakout .68 AGT
29/03/2006 Watch Buy pullback .67 AGT
30/03/2006 Watch Buy pullback .67 AGT
30/03/2006 Watch Buy pullback ANO
02/04/2006 Buy Buy pullback n ANO
29/03/2006 Watch Buy pullback APG.TO
30/03/2006 Watch Buy pullback APG.TO
02/04/2006 Buy buy close to 9 ema APG.TO
02/04/2006 Watch buy breakout 3.27 ARTG
30/03/2006 Watch Buy breakout 2.56 ATN.TO
29/03/2006 Watch Buy close to .38 as BAN.TO
31/03/2006 Hold Bought @ban @ .4 BAN.TO
29/03/2006 Watch BBD/SB.TO
30/03/2006 Watch BBD/SB.TO
30/03/2006 Watch BCG I
02/04/2006 Buy Buy bwr now at 9 em BWR.TO
28/03/2006 Watch gapped up CBJ.TO
29/03/2006 Watch Buy the breakout of. CBJ.TO

02/04/2006 Buy Buy pullback CBJ.TO
28/03/2006 Watch buy breakout 2.45 CCU.TO
29/03/2006 Watch Buy the breakout of CCU.TO
31/03/2006 Watch CCUR
31/03/2006 Watch buy pullback to 9 ema CDIC
29/03/2006 Watch CDV.TO
31/03/2006 CDV.TO
28/03/2006 Watch wait for a pullback to 33 ema CEK.TO
02/04/2006 Buy buy near pullback to trend line CEK.TO
29/03/2006 Ignore CYGX
30/03/2006 Watch CYGX
29/03/2006 Watch Buy now 1.75 CYTR
30/03/2006 Watch missed the alert by a cent CYTR
31/03/2006 CYTR
31/03/2006 Hold Bought cytr @ 1.78 CYTR
30/03/2006 Watch nice breakout on cup formation. Buy 9 ema pullback CZN.TO
02/04/2006 Buy buy pullback CZN.TO
31/03/2006 EDGR
29/03/2006 Ignore EGU.TO
30/03/2006 Watch buy pullback to 33 ema EZM.TO
30/03/2006 Watch FCC.TO



Tom Ellison said:
Dear Seede:

Not making much sense. You said before that you wanted only symbol =
"Watch". Now your post indicates that you want the symbol to be correlated
between inner and outer queries. Which is it?

At the same time, you have added some restriction that action = "Buy" which
was not mentioned before.

Not knowing what you want, and getting contradictory information about it,
reduces my effectiveness. I recommend you show sample data and desired
output as well as explain things again.

You have switched to using an IN() clause, which I did not mention. The
IN() clause you represent makes no sense. First of all, while it is
perfectly acceptable to use a SELECT query to fill an IN() clause list, this
query can have only one column. You seem to want two columns. The first
column is a MAX(td.date_today) which is a single value result. If there is
a single value, IN() is not required. The simple = (SELECT MAX(DATE)
approach I proposed is sufficient.

Given your original requirements, and accomodating the column and table
names you have just revealed, the solution to your original post would be:

SELECT symbol, action, date_today
FROM tradediary T
WHERE symbol = "Watch"
AND date_today = (SELECT MAX(date_today)
FROM tradediary T1
WHERE symbol = "Watch"
AND T1.action = T.action)

Please run this and see if it matches your original requirements. Please
explain how it does not meet any new requirements.

Tom Ellison


Seede said:
HI. i am almost there. Just need a bit of assitance. here is what i did
based on your input.

select * from tradediary tdd where tdd.symbol in (
select max(td.date_today) ,
td.symbol from tradediary td where td.action='Buy' group by td.symbol)
and tdd.symbol=td.symbol

the inner query works by itself for obvious reasons. how do i get the
outer
query return results where the tdd.symbol =td.symbol

Thanks in advance for your help
Tom Ellison said:
Dear Seede:

I'm thinking you want one of these:

SELECT symbol, action, [date]
FROM YourTable
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable
WHERE T1.symbol = "Watch")

This produces those rows where symbol = "Watch" and which occur on the
most
recent date of all those rows where symbol = "Watch".

Now, it might also be the case that you want the most recent date not
only
of all values of symbol, but within the subset of those for each
different
value of action. That would be:

SELECT symbol, action, [date]
FROM YourTable T
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable T1
WHERE symbol = "Watch"
AND T1.action = T.action)

You may find that these give considerably different results. Choose the
one
that fits your requirements.

Please let me know if this helped, and if I can be of further help.

Tom Ellison


HI.
I have a table a with columns: date,symbol,action.

I want to return rows where the last action for every symbol = "Watch"
I am little confused how to go about this one.
Click to expand...
Click to expand...
Click to expand...
 
Dear Seede:

You have changed from symbol = "Watch" in your original post to action =
"Watch" in this post. Just change my original suggestions accordingly,
along with the changes to the table name and column name changes. Also, in
the second query, change action to symbol.

There are still differences between the two queries I provided based on a
question you have not answered. The question has changed somewhat because
the columns action and symbols have exchanged significance. The question is
this: If you have this data:

Date_today Action Notes symbol
01/10/2006 Watch Some Notes XXXX
01/09/2006 Watch Some Notes AAAA

Now, with this, do you want to see only the 01/10/2006 result, or both. If
you filter to only the most recent date, you'll see only the 01/10/2006
result, but if you filter to the most recent date for eash symbol then
you'll get both. Which do you want?

The queries are now:

SELECT Date_today, action, Notes, symbol
FROM tradediary
WHERE action = "Watch"
AND Date_today = (SELECT MAX(Date_today)
FROM tradediary T
WHERE T.action = "Watch")

and

SELECT Date_today, action, Notes, symbol
FROM tradediary T
WHERE action = "Watch"
AND Date_today = (SELECT MAX(Date_today)
FROM tradediary T1
WHERE action = "Watch"
AND T1.symbol = T.symbol)

These will give quite different results. Perhaps you can see the difference
and why it occurs. Which do you want?

Tom Ellison


Seede said:
Apologies if i caused confusion. I am basically a Access beginner and
here
is what i am trying to do. All i am trying to do is get all the symbols
where
the action=Watch for the latest Date_Today.
Once i get all the symbols, i want to get all the rows related to that
symbol.
For eg. in the data sheet attached.
I want to return all rows for ADAT,AGT ... but not ANO.
The reason i want ADAT and AGT is because the max(date_today) for each
symbol is in the row where the action=Watch.
The reason i dont want ANO is because the max(date_today) for ANO is in
the
row where the action=Buy.

Hope this clarifies. I would appreciate your assistance as i have been
scrating my head for the last 2 days.


for the last date_Today is for all dates where the max(date_today)
1. Table TradeDiary has following structure.
Date_Today Action Notes symbol
30/03/2006 Watch Watch Pullback ADAT
31/03/2006 Watch Watch pullback ADAT
28/03/2006 Watch Buy breakout .68 AGT
29/03/2006 Watch Buy pullback .67 AGT
30/03/2006 Watch Buy pullback .67 AGT
30/03/2006 Watch Buy pullback ANO
02/04/2006 Buy Buy pullback n ANO
29/03/2006 Watch Buy pullback APG.TO
30/03/2006 Watch Buy pullback APG.TO
02/04/2006 Buy buy close to 9 ema APG.TO
02/04/2006 Watch buy breakout 3.27 ARTG
30/03/2006 Watch Buy breakout 2.56 ATN.TO
29/03/2006 Watch Buy close to .38 as BAN.TO
31/03/2006 Hold Bought @ban @ .4 BAN.TO
29/03/2006 Watch BBD/SB.TO
30/03/2006 Watch BBD/SB.TO
30/03/2006 Watch BCG I
02/04/2006 Buy Buy bwr now at 9 em BWR.TO
28/03/2006 Watch gapped up CBJ.TO
29/03/2006 Watch Buy the breakout of. CBJ.TO

02/04/2006 Buy Buy pullback CBJ.TO
28/03/2006 Watch buy breakout 2.45 CCU.TO
29/03/2006 Watch Buy the breakout of CCU.TO
31/03/2006 Watch CCUR
31/03/2006 Watch buy pullback to 9 ema CDIC
29/03/2006 Watch CDV.TO
31/03/2006 CDV.TO
28/03/2006 Watch wait for a pullback to 33 ema CEK.TO
02/04/2006 Buy buy near pullback to trend line CEK.TO
29/03/2006 Ignore CYGX
30/03/2006 Watch CYGX
29/03/2006 Watch Buy now 1.75 CYTR
30/03/2006 Watch missed the alert by a cent CYTR
31/03/2006 CYTR
31/03/2006 Hold Bought cytr @ 1.78 CYTR
30/03/2006 Watch nice breakout on cup formation. Buy 9 ema pullback CZN.TO
02/04/2006 Buy buy pullback CZN.TO
31/03/2006 EDGR
29/03/2006 Ignore EGU.TO
30/03/2006 Watch buy pullback to 33 ema EZM.TO
30/03/2006 Watch FCC.TO



Tom Ellison said:
Dear Seede:

Not making much sense. You said before that you wanted only symbol =
"Watch". Now your post indicates that you want the symbol to be
correlated
between inner and outer queries. Which is it?

At the same time, you have added some restriction that action = "Buy"
which
was not mentioned before.

Not knowing what you want, and getting contradictory information about
it,
reduces my effectiveness. I recommend you show sample data and desired
output as well as explain things again.

You have switched to using an IN() clause, which I did not mention. The
IN() clause you represent makes no sense. First of all, while it is
perfectly acceptable to use a SELECT query to fill an IN() clause list,
this
query can have only one column. You seem to want two columns. The first
column is a MAX(td.date_today) which is a single value result. If there
is
a single value, IN() is not required. The simple = (SELECT MAX(DATE)
approach I proposed is sufficient.

Given your original requirements, and accomodating the column and table
names you have just revealed, the solution to your original post would
be:

SELECT symbol, action, date_today
FROM tradediary T
WHERE symbol = "Watch"
AND date_today = (SELECT MAX(date_today)
FROM tradediary T1
WHERE symbol = "Watch"
AND T1.action = T.action)

Please run this and see if it matches your original requirements. Please
explain how it does not meet any new requirements.

Tom Ellison


Seede said:
HI. i am almost there. Just need a bit of assitance. here is what i
did
based on your input.

select * from tradediary tdd where tdd.symbol in (
select max(td.date_today) ,
td.symbol from tradediary td where td.action='Buy' group by
td.symbol)
and tdd.symbol=td.symbol

the inner query works by itself for obvious reasons. how do i get the
outer
query return results where the tdd.symbol =td.symbol

Thanks in advance for your help
:

Dear Seede:

I'm thinking you want one of these:

SELECT symbol, action, [date]
FROM YourTable
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable
WHERE T1.symbol = "Watch")

This produces those rows where symbol = "Watch" and which occur on the
most
recent date of all those rows where symbol = "Watch".

Now, it might also be the case that you want the most recent date not
only
of all values of symbol, but within the subset of those for each
different
value of action. That would be:

SELECT symbol, action, [date]
FROM YourTable T
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable T1
WHERE symbol = "Watch"
AND T1.action = T.action)

You may find that these give considerably different results. Choose
the
one
that fits your requirements.

Please let me know if this helped, and if I can be of further help.

Tom Ellison


HI.
I have a table a with columns: date,symbol,action.

I want to return rows where the last action for every symbol =
"Watch"
I am little confused how to go about this one.
Click to expand...
Click to expand...
Click to expand...
 
thanks a million. u saved my job
Tom Ellison said:
Dear Seede:

You have changed from symbol = "Watch" in your original post to action =
"Watch" in this post. Just change my original suggestions accordingly,
along with the changes to the table name and column name changes. Also, in
the second query, change action to symbol.

There are still differences between the two queries I provided based on a
question you have not answered. The question has changed somewhat because
the columns action and symbols have exchanged significance. The question is
this: If you have this data:

Date_today Action Notes symbol
01/10/2006 Watch Some Notes XXXX
01/09/2006 Watch Some Notes AAAA

Now, with this, do you want to see only the 01/10/2006 result, or both. If
you filter to only the most recent date, you'll see only the 01/10/2006
result, but if you filter to the most recent date for eash symbol then
you'll get both. Which do you want?

The queries are now:

SELECT Date_today, action, Notes, symbol
FROM tradediary
WHERE action = "Watch"
AND Date_today = (SELECT MAX(Date_today)
FROM tradediary T
WHERE T.action = "Watch")

and

SELECT Date_today, action, Notes, symbol
FROM tradediary T
WHERE action = "Watch"
AND Date_today = (SELECT MAX(Date_today)
FROM tradediary T1
WHERE action = "Watch"
AND T1.symbol = T.symbol)

These will give quite different results. Perhaps you can see the difference
and why it occurs. Which do you want?

Tom Ellison


Seede said:
Apologies if i caused confusion. I am basically a Access beginner and
here
is what i am trying to do. All i am trying to do is get all the symbols
where
the action=Watch for the latest Date_Today.
Once i get all the symbols, i want to get all the rows related to that
symbol.
For eg. in the data sheet attached.
I want to return all rows for ADAT,AGT ... but not ANO.
The reason i want ADAT and AGT is because the max(date_today) for each
symbol is in the row where the action=Watch.
The reason i dont want ANO is because the max(date_today) for ANO is in
the
row where the action=Buy.

Hope this clarifies. I would appreciate your assistance as i have been
scrating my head for the last 2 days.


for the last date_Today is for all dates where the max(date_today)
1. Table TradeDiary has following structure.
Date_Today Action Notes symbol
30/03/2006 Watch Watch Pullback ADAT
31/03/2006 Watch Watch pullback ADAT
28/03/2006 Watch Buy breakout .68 AGT
29/03/2006 Watch Buy pullback .67 AGT
30/03/2006 Watch Buy pullback .67 AGT
30/03/2006 Watch Buy pullback ANO
02/04/2006 Buy Buy pullback n ANO
29/03/2006 Watch Buy pullback APG.TO
30/03/2006 Watch Buy pullback APG.TO
02/04/2006 Buy buy close to 9 ema APG.TO
02/04/2006 Watch buy breakout 3.27 ARTG
30/03/2006 Watch Buy breakout 2.56 ATN.TO
29/03/2006 Watch Buy close to .38 as BAN.TO
31/03/2006 Hold Bought @ban @ .4 BAN.TO
29/03/2006 Watch BBD/SB.TO
30/03/2006 Watch BBD/SB.TO
30/03/2006 Watch BCG I
02/04/2006 Buy Buy bwr now at 9 em BWR.TO
28/03/2006 Watch gapped up CBJ.TO
29/03/2006 Watch Buy the breakout of. CBJ.TO

02/04/2006 Buy Buy pullback CBJ.TO
28/03/2006 Watch buy breakout 2.45 CCU.TO
29/03/2006 Watch Buy the breakout of CCU.TO
31/03/2006 Watch CCUR
31/03/2006 Watch buy pullback to 9 ema CDIC
29/03/2006 Watch CDV.TO
31/03/2006 CDV.TO
28/03/2006 Watch wait for a pullback to 33 ema CEK.TO
02/04/2006 Buy buy near pullback to trend line CEK.TO
29/03/2006 Ignore CYGX
30/03/2006 Watch CYGX
29/03/2006 Watch Buy now 1.75 CYTR
30/03/2006 Watch missed the alert by a cent CYTR
31/03/2006 CYTR
31/03/2006 Hold Bought cytr @ 1.78 CYTR
30/03/2006 Watch nice breakout on cup formation. Buy 9 ema pullback CZN.TO
02/04/2006 Buy buy pullback CZN.TO
31/03/2006 EDGR
29/03/2006 Ignore EGU.TO
30/03/2006 Watch buy pullback to 33 ema EZM.TO
30/03/2006 Watch FCC.TO



Tom Ellison said:
Dear Seede:

Not making much sense. You said before that you wanted only symbol =
"Watch". Now your post indicates that you want the symbol to be
correlated
between inner and outer queries. Which is it?

At the same time, you have added some restriction that action = "Buy"
which
was not mentioned before.

Not knowing what you want, and getting contradictory information about
it,
reduces my effectiveness. I recommend you show sample data and desired
output as well as explain things again.

You have switched to using an IN() clause, which I did not mention. The
IN() clause you represent makes no sense. First of all, while it is
perfectly acceptable to use a SELECT query to fill an IN() clause list,
this
query can have only one column. You seem to want two columns. The first
column is a MAX(td.date_today) which is a single value result. If there
is
a single value, IN() is not required. The simple = (SELECT MAX(DATE)
approach I proposed is sufficient.

Given your original requirements, and accomodating the column and table
names you have just revealed, the solution to your original post would
be:

SELECT symbol, action, date_today
FROM tradediary T
WHERE symbol = "Watch"
AND date_today = (SELECT MAX(date_today)
FROM tradediary T1
WHERE symbol = "Watch"
AND T1.action = T.action)

Please run this and see if it matches your original requirements. Please
explain how it does not meet any new requirements.

Tom Ellison


HI. i am almost there. Just need a bit of assitance. here is what i
did
based on your input.

select * from tradediary tdd where tdd.symbol in (
select max(td.date_today) ,
td.symbol from tradediary td where td.action='Buy' group by
td.symbol)
and tdd.symbol=td.symbol

the inner query works by itself for obvious reasons. how do i get the
outer
query return results where the tdd.symbol =td.symbol

Thanks in advance for your help
:

Dear Seede:

I'm thinking you want one of these:

SELECT symbol, action, [date]
FROM YourTable
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable
WHERE T1.symbol = "Watch")

This produces those rows where symbol = "Watch" and which occur on the
most
recent date of all those rows where symbol = "Watch".

Now, it might also be the case that you want the most recent date not
only
of all values of symbol, but within the subset of those for each
different
value of action. That would be:

SELECT symbol, action, [date]
FROM YourTable T
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable T1
WHERE symbol = "Watch"
AND T1.action = T.action)

You may find that these give considerably different results. Choose
the
one
that fits your requirements.

Please let me know if this helped, and if I can be of further help.

Tom Ellison


HI.
I have a table a with columns: date,symbol,action.

I want to return rows where the last action for every symbol =
"Watch"
I am little confused how to go about this one.
Click to expand...
Click to expand...
Click to expand...
 
Dear Seede:

That's somewhat beyond what I expected. I'm glad I didn't have the pressure
ahead of time of thinking your job was at stake.

When you're finished with it, can I have it?

Tom Ellison


Seede said:
thanks a million. u saved my job
Tom Ellison said:
Dear Seede:

You have changed from symbol = "Watch" in your original post to action =
"Watch" in this post. Just change my original suggestions accordingly,
along with the changes to the table name and column name changes. Also,
in
the second query, change action to symbol.

There are still differences between the two queries I provided based on a
question you have not answered. The question has changed somewhat
because
the columns action and symbols have exchanged significance. The question
is
this: If you have this data:

Date_today Action Notes symbol
01/10/2006 Watch Some Notes XXXX
01/09/2006 Watch Some Notes AAAA

Now, with this, do you want to see only the 01/10/2006 result, or both.
If
you filter to only the most recent date, you'll see only the 01/10/2006
result, but if you filter to the most recent date for eash symbol then
you'll get both. Which do you want?

The queries are now:

SELECT Date_today, action, Notes, symbol
FROM tradediary
WHERE action = "Watch"
AND Date_today = (SELECT MAX(Date_today)
FROM tradediary T
WHERE T.action = "Watch")

and

SELECT Date_today, action, Notes, symbol
FROM tradediary T
WHERE action = "Watch"
AND Date_today = (SELECT MAX(Date_today)
FROM tradediary T1
WHERE action = "Watch"
AND T1.symbol = T.symbol)

These will give quite different results. Perhaps you can see the
difference
and why it occurs. Which do you want?

Tom Ellison


Seede said:
Apologies if i caused confusion. I am basically a Access beginner and
here
is what i am trying to do. All i am trying to do is get all the symbols
where
the action=Watch for the latest Date_Today.
Once i get all the symbols, i want to get all the rows related to that
symbol.
For eg. in the data sheet attached.
I want to return all rows for ADAT,AGT ... but not ANO.
The reason i want ADAT and AGT is because the max(date_today) for each
symbol is in the row where the action=Watch.
The reason i dont want ANO is because the max(date_today) for ANO is in
the
row where the action=Buy.

Hope this clarifies. I would appreciate your assistance as i have been
scrating my head for the last 2 days.


for the last date_Today is for all dates where the max(date_today)
1. Table TradeDiary has following structure.
Date_Today Action Notes symbol
30/03/2006 Watch Watch Pullback ADAT
31/03/2006 Watch Watch pullback ADAT
28/03/2006 Watch Buy breakout .68 AGT
29/03/2006 Watch Buy pullback .67 AGT
30/03/2006 Watch Buy pullback .67 AGT
30/03/2006 Watch Buy pullback ANO
02/04/2006 Buy Buy pullback n ANO
29/03/2006 Watch Buy pullback APG.TO
30/03/2006 Watch Buy pullback APG.TO
02/04/2006 Buy buy close to 9 ema APG.TO
02/04/2006 Watch buy breakout 3.27 ARTG
30/03/2006 Watch Buy breakout 2.56 ATN.TO
29/03/2006 Watch Buy close to .38 as BAN.TO
31/03/2006 Hold Bought @ban @ .4 BAN.TO
29/03/2006 Watch BBD/SB.TO
30/03/2006 Watch BBD/SB.TO
30/03/2006 Watch BCG I
02/04/2006 Buy Buy bwr now at 9 em BWR.TO
28/03/2006 Watch gapped up CBJ.TO
29/03/2006 Watch Buy the breakout of. CBJ.TO

02/04/2006 Buy Buy pullback CBJ.TO
28/03/2006 Watch buy breakout 2.45 CCU.TO
29/03/2006 Watch Buy the breakout of CCU.TO
31/03/2006 Watch CCUR
31/03/2006 Watch buy pullback to 9 ema CDIC
29/03/2006 Watch CDV.TO
31/03/2006 CDV.TO
28/03/2006 Watch wait for a pullback to 33 ema CEK.TO
02/04/2006 Buy buy near pullback to trend line CEK.TO
29/03/2006 Ignore CYGX
30/03/2006 Watch CYGX
29/03/2006 Watch Buy now 1.75 CYTR
30/03/2006 Watch missed the alert by a cent CYTR
31/03/2006 CYTR
31/03/2006 Hold Bought cytr @ 1.78 CYTR
30/03/2006 Watch nice breakout on cup formation. Buy 9 ema pullback
CZN.TO
02/04/2006 Buy buy pullback CZN.TO
31/03/2006 EDGR
29/03/2006 Ignore EGU.TO
30/03/2006 Watch buy pullback to 33 ema EZM.TO
30/03/2006 Watch FCC.TO



:

Dear Seede:

Not making much sense. You said before that you wanted only symbol =
"Watch". Now your post indicates that you want the symbol to be
correlated
between inner and outer queries. Which is it?

At the same time, you have added some restriction that action = "Buy"
which
was not mentioned before.

Not knowing what you want, and getting contradictory information about
it,
reduces my effectiveness. I recommend you show sample data and
desired
output as well as explain things again.

You have switched to using an IN() clause, which I did not mention.
The
IN() clause you represent makes no sense. First of all, while it is
perfectly acceptable to use a SELECT query to fill an IN() clause
list,
this
query can have only one column. You seem to want two columns. The
first
column is a MAX(td.date_today) which is a single value result. If
there
is
a single value, IN() is not required. The simple = (SELECT MAX(DATE)
approach I proposed is sufficient.

Given your original requirements, and accomodating the column and
table
names you have just revealed, the solution to your original post would
be:

SELECT symbol, action, date_today
FROM tradediary T
WHERE symbol = "Watch"
AND date_today = (SELECT MAX(date_today)
FROM tradediary T1
WHERE symbol = "Watch"
AND T1.action = T.action)

Please run this and see if it matches your original requirements.
Please
explain how it does not meet any new requirements.

Tom Ellison


HI. i am almost there. Just need a bit of assitance. here is what i
did
based on your input.

select * from tradediary tdd where tdd.symbol in (
select max(td.date_today) ,
td.symbol from tradediary td where td.action='Buy' group by
td.symbol)
and tdd.symbol=td.symbol

the inner query works by itself for obvious reasons. how do i get
the
outer
query return results where the tdd.symbol =td.symbol

Thanks in advance for your help
:

Dear Seede:

I'm thinking you want one of these:

SELECT symbol, action, [date]
FROM YourTable
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable
WHERE T1.symbol = "Watch")

This produces those rows where symbol = "Watch" and which occur on
the
most
recent date of all those rows where symbol = "Watch".

Now, it might also be the case that you want the most recent date
not
only
of all values of symbol, but within the subset of those for each
different
value of action. That would be:

SELECT symbol, action, [date]
FROM YourTable T
WHERE symbol = "Watch"
AND [date] = (SELECT MAX(DATE)
FROM YourTable T1
WHERE symbol = "Watch"
AND T1.action = T.action)

You may find that these give considerably different results.
Choose
the
one
that fits your requirements.

Please let me know if this helped, and if I can be of further help.

Tom Ellison


HI.
I have a table a with columns: date,symbol,action.

I want to return rows where the last action for every symbol =
"Watch"
I am little confused how to go about this one.
Click to expand...
Click to expand...
Click to expand...
 
Tom, I'm still trying to make sense of this thread,
but your final response is absolutely priceless.

ROFLOL
 
Back
Top