Date query

[Chrisopia]

Im getting quick confused and wound up. - after 3 days of trying several methods I can't seem to figure this out?

I have a table of numbers that apply to a date range, for example:
between 01/01/2001 and 01/01/2002 - The number (factor) is 20,
Between 02/01/2002 and 01/01/2003 - the factor is 30

I need to create a query that takes the date of a separate record, and finds which date range it lies between to multiply something by the relevant factor...

I was thinking loop queries, etc but I can't quite get them working and I cant think of an alternative?

 

[Chrisopia]

Price * tblFactor.Factor WHERE [Forms]![Order Details]![DatePaid] Is Between [tblFactor.DateFrom] AND [tblFactor.DateTo]

This is the closest I have, but it makes no sense to access.

 

[recyan]

just check out if below (pseudo-code) gives some guidelines :

SELECT 
	tblOrderDetails.OrderID, 
	tblOrderDetails.OrderDate, 
	tblOrderDetails.Price, 
	tblFactors.DateRangeStart, 
	tblFactors.DateRangeEnd, 
	tblFactors.Factor,
	tblOrderDetails.Price * tblFactors.Factor AS FactorAppliedPrice 
FROM 
	tblOrderDetails
	INNER JOIN 
	tblFactors
	ON 
	(tblOrderDetails.OrderDate BETWEEN tblFactors.DateRangeStart AND tblFactors.DateRangeEnd);

Thanks

 

[Chrisopia]

Well I'll be!

It worked a treat!

I didn't know you could use a query (i.e. Between # And #) to create a relationship in SQL!

How clever!!

tblOrderDetails.OrderDate BETWEEN tblFactors.DateRangeStart AND tblFactors.DateRangeEnd <- this was the bit I needed, I've customised it for what I needed, but thanks!!

 

[recyan]

Glad you found it helpful.

Thanks

 

[Galaxiom]

I didn't know you could use a query (i.e. Between # And #) to create a relationship in SQL!

There are many types of joins that can be written in the SQL but not in the design view because it only does equals. Use functions and other comparison operators too.

Note though that the use of functions can slow down the query dramatically on large tables.