The following code works on SQL server. not sure about
access. I think you are looking for something similar.

SELECT Transactions.datepurchased,
charge_procedure.effective_date,
charge_procedure.product_cost 
        FROM transactions INNER JOIN charge_procedure
        ON Transactions.product_id =
charge_procedure.product_id WHERE
charge_procedure.effective_date IN
        (SELECT TOP 1 charge_procedure.effective_date FROM
charge_procedure WHERE 
         charge_procedure.product_id =#pid# and
charge_procedure.effective_date <=
Transactions.datepurchased 
        order by charge_procedure.effective_date DESC) AND
Transactions.product_id=#pid#  



--- Greg Morphis <[EMAIL PROTECTED]> wrote:

> Does access support subqueries?
> 
> ex.
> select * from table
> where datefield = (
> select max(datefield) from table)
> 
> this would return a row with the latest date.
> 
> 
> On Tue, 21 Dec 2004 08:27:52 -0500, Judith Taylor
> <[EMAIL PROTECTED]> wrote:
> > Ben Sauer wrote:
> > > What kind of Database?
> > 
> > Oops, sorry. it's an MS Access database running on
> Wink2K Server.
> > 
> > Judith
> > 
> > 
> 
> 


                
__________________________________ 
Do you Yahoo!? 
Dress up your holiday email, Hollywood style. Learn more. 
http://celebrity.mail.yahoo.com

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Silver Sponsor - New Atlanta
http://www.newatlanta.com

Message: http://www.houseoffusion.com/lists.cfm/link=i:15:537
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/15
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:15
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Reply via email to