View Full Version : Query join table with date intervals
philfer
05-05-2017, 07:43 AM
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
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
05-06-2017, 12:20 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.