Sub-Query Syntax

[TallMan]

Hello,

I have a tbl with the following fields:

Acct_Num, Mkt_Dt,Cash_Value

The table holds every daily ending balance since it was opened. I am trying to run a query to capture the "MAX" mkt_dt so that I have the most recent value as of yesterdays close.

When I run a straight Max(mkt_dt) in the design view of the query it returns every row in the table. If the market value changes, which it does, then the query is pulling the "max" date for every value change.

I think I am able to get the true max date if I use a sub-query within the where criteria of the mkt_dt field.


This is what I have so far.....I have tried all types of combinations. Do you see anything wrong with my syntax??

Select * From tbl1 where PGM = "TEST" and Mkt_Val = (select Acct_Num,max(mkt_dt) From tbl1);

 

[Guus2005]

Didn't you get a syntax error message?

You are comparing a string "mkt_dt" to the result of a select statement which returns two fields acct_num and max(mkt_dt)

I think this is what you meant:

SELECT TOP 1000 dbo_ams_acct_sum.acct_num, dbo_AMS_Assets_by_Account_vw.mkt_dt, CCur(IIf([cash_bal_amt]=0,[dbo_AMS_Assets_by_Account_vw]![cr_int_amt]+[dbo_AMS_Assets_by_Account_vw]![cash_bal_amt],[dbo_AMS_Assets_by_Account_vw]![cash_bal_amt])) AS Cash_Val
 
FROM dbo_ams_acct_sum INNER JOIN dbo_AMS_Assets_by_Account_vw ON dbo_ams_acct_sum.acct_num = dbo_AMS_Assets_by_Account_vw.acct_num
WHERE ((mkt_dt In (select max(mkt_dt) from dbo.ams_assets_by_account_vw group by acct_num)) AND ((dbo_ams_acct_sum.acct_pgm)="rjcs" Or (dbo_ams_acct_sum.acct_pgm)="frdm" Or (dbo_ams_acct_sum.acct_pgm)="osmm" Or (dbo_ams_acct_sum.acct_pgm)="eama") AND ((dbo_ams_acct_sum.acct_pgm_assn_end_dt) Is Null));

 

[TallMan]

I just tried your update....no luck. I am getting a "Could not find file "share path name here""

Not sure why this is not working.

 

[Guus2005]

Strange error message. I don't believe that is a SQL syntax error message.

Can you post a sample database?

 

[Guus2005]

You have changed the original SQL. But still there is the same error

Select * From tbl1 where PGM = "TEST" and Mkt_Val = (select Acct_Num,max(mkt_dt) From tbl1);

Try this:

Select * From tbl1 where PGM = "TEST" and Mkt_Val in (select max(mkt_dt) From tbl1);

 

[vbaInet]

Select * From tbl1 where PGM = "TEST" and Mkt_Val[COLOR=Red][B] in[/B][/COLOR] (select max(mkt_dt) From tbl1);

I'm sure you know this, but IN should be = since max() is returning just one value.

 

[Guus2005]

Select * From tbl1 where PGM = "TEST" and Mkt_Val[COLOR=Red][B] in[/B][/COLOR] (select max(mkt_dt) From tbl1);

I'm sure you know this, but IN should be = since max() is returning just one value.

Yes, max() returns just one value, but that doesn't mean that IN is wrong.
The statement works just as well.

Enjoy!

 

[vbaInet]

Yes, max() returns just one value, but that doesn't mean that IN is wrong.
The statement works just as well.

Enjoy!

I didn't mention that IN is wrong I pointed out that the most effecient way to compare in such a situation would be = and not IN.

 

[Guus2005]

You are completely right. I wonder if it is faster too.

 

[vbaInet]

I wonder too Guus. My only guess is that since the IN() operator is an array (of some sort) it would perform a Split() and return the values in a relational view (i.e. as rows), whereas the equal to would perform a straight comparison.