Query without Join??

[grahamjlaws]

I really don't know how to describe this, so bear with me!

i have a table of sales data 'tSales' that holds 3 years of transactions.

Fields are

Acc
Cust
Market
Prod
Qty
Value
Cost
Material
Labour

I have another table 'tLabRate' that holds our labour rates

e.g. in 2004 £20ph, 2005 £25ph etc

in the Form:

RateID
LabRate 20.00 25.00
LabFactor 0.6666 0.8333
LrFrmDate 01/01/2004 01/01/2005
LrToDate 31/12/2004 31/12/2005 etc

What i am trying to do is display the correct factor for the point in time, e.g.
LabFactor where sDate > than LrFrmDate AND < LrToDate

How do i do this in a query?? i can't join i don't think becasue there isn't a common field....?

Thanks

Graham

 

[pbaldy]

Actually you can join, but it's not as well known. You want a "non-equi" join. I posted a sample in post 12 here:

http://www.access-programmers.co.uk/forums/showthread.php?t=116425

 

[grahamjlaws]

Hi Paul,

Thanks! From my learning point of view is your format:

SELECT DateCost, HoursWorked, Rate
FROM tblCost Left JOIN tblRate ON tblCost.DateCost >= tblRate.ValidFrom AND tblCost.DateCost <= tblRate.ValidTo;

The same in principle to:

select * from tSales inner join tLabRate on tSales.sDate between tLabRate.LrFrmDate and tLabRate.LrToDate

And:

SELECT tSales.*, t_LabRate.LabFactor
FROM tSales, t_LabRate
WHERE (((tSales.sDate) Between [t_labrate].[lrfrmdate] And [t_labrate].[lrtodate]));


What is the advantage / disadvantage to these 3 approaches?
i.e. Left Join / Inner Join / 'No' Join?

Regards

Graham

 

[pbaldy]

My understanding is that generally a join will be more efficient than a "no" join. The left join will protect you from the situation where a date is unaccounted for in the rate table. If that happened, the inner join wouldn't return the record from the sales table at all. With the left join, it will return the sales record with a null rate.

 

[grahamjlaws]

Thanks Paul

I appreciate the help.