Query join table with date intervals

[philfer]

Hi,

I have a table which calculates the number of days in a transaction as [EndDate]-[StartDate] as numdays

I have another table with time intervals eg:-


1 Overnight
7 1 Week
30 1 month
90 3 months


How do I join these two table so that I can return the time interval based on numdays


Thanks
Phil

[OBP]

Phil, I am not sure how you do what you want as the number of days will only meet some of your intervals some of the time.
Can you explain a bit more about what you are trying to do.

[HiTechCoach]

I would use a subquery to lookup the value from the other table. I would also change your interval table to have a range.


See: Subquery Basics (http://hitechcoach.com/microsoft-office/access/access-links/52-access-queries-tips-and-tricks/426-subquery-basics)


Another option would be to create a User Defined Function (UDF). You can pass it the "days" and it would return the interval. The advantage to the UDF is that is can be sharted. You can use the same UDF in forms, reports, and queries.

I prefer to do this type of calculation at the form or report level because it generally has better performance.