Ok, I have another question, this time involving working with dates and 
date ranges.

Scenario:

Table with Procedural Charges, some have two or more entries per charge 
depending on how often the costs have changed.

Transactions table with multiple entries per account (basically every 
transaction for an account).

Both of these tables have dates (ChargeProcedures has "EffectiveDate"; 
Transactions has "Episode").

Quandry:

What I would like to do is get my query to actually pull the most recent 
charge from the ChargeProcedures table based on the Episode date from my 
Transactions table. But, I would like it to pull the next latest charge 
if the Episode date in my Transactions table is older than the latest 
chagne in the EffectiveDate in ChargeProcedures.

Example:

Transactions.Episode  11/29/2003

ChargeProcedures table has an entry for 1/1/2002 and a newer entry for 
1/1/2004

I need to pull the cost and charge for the 2002 effectivedate, not for 
the 2004.

Problem I've been running into is that I can't seem to get just *one* 
result, but instead I'm getting one for *each effectivedate entry in the 
table*. Since this feeds another calculation, my final numbers are off 
by however many entries for a particular ChargeProcedure exists in the 
table.

Let me koe if this isn't clear. any help with this will be greatly 
appreciated.

Thanks,
Judith

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:15:533
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