Query to return intermediate values (1 Viewer)

kiwijules0505

Registered User.
Local time
Tomorrow, 07:11
Joined
Nov 27, 2007
Messages
10
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

GrumpyOldMan in Training
Local time
Today, 11:11
Joined
Dec 21, 2005
Messages
1,583
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

Registered User.
Local time
Tomorrow, 07:11
Joined
Nov 27, 2007
Messages
10
Many thanks for your time Craig, I got that to work for me.

Regards,
Jules
 

Users who are viewing this thread

Top Bottom