Query to return intermediate values

[kiwijules0505]

Hi All,

I need an Access query to return the rate at which a customer should be charged. A customer's rate can be set for a period of 1 to 6 months and the customer's rate table could look like this:

ACCOUNT_ID........START_DATE........END_DATE........COST_PER_UNIT
31308.................01-Apr-07................30-Jun-07.................6.195
31308.................01-Jul-07.................31-Aug-07.................6.304
31308.................01-Sep-07................31-Mar-08.................5.422
31308.................01-Apr-08.................31-Aug-08.................6.304

I need a query (preferrably SQL) that will return the rate a customer should be charged each month, e.g.

31308 / Aug 07 / $6.304
31308 / Sep 07 / $5.422
31308 / Oct 07 / $5.422
etc, etc

I'm using Access 2003 and the rates table contains details of about 2500 customers.

Any ideas would be welcomed.

PS. Can someone tell me how to paste a table straight from Excel into the forum post body???

 

[CraigDolphin]

Jules,

have look at the sql below. You'll need to change the table name/field names etc to match yours.

SELECT Table3.Cost_Per_Unit
FROM Table3
WHERE (((Table3.AccountID)=[Input AccountID]) AND ((Table3.Start_Date)<=CDate([Input Month] & '/15/' & [Input Year])) AND ((Table3.End_Date)>=CDate([Input Month] & '/15/' & [Input Year])));

It should give you an idea as how you might proceed. You could replace the criterion paramater inputs with references to controls on forms, or variables that you've set in vba etc. You might also want to look into domain aggregate functions like Dlookup if you want to just lookup the value in the control source of a textbox on a form.

HTH

 

[kiwijules0505]

Many thanks for your time Craig, I got that to work for me.

Regards,
Jules

 

[CraigDolphin]

Happy to help...esp a fellow Kiwi