I have 2 tables. One has employee info and the second has time data based on thresholds reached in the employee info. Obviously, a basic join will bring in all of the threshold records. How do I get it to only pull in the one that satisfies the threshold condition?
Join Query to pull in 1 record based on threshold reached
- Thread starter here4real
- Start date
I just got it... I needed to do multiple steps..
The first step was just to do the join and merge the employee and threshold tables such that for each employee I could have 1 or more records... Note - the thresholds were in a way where if they worked 0 hours they received one thing, 6 hours something else, etc...
Next, I did a Group By on that intermediate query with a MAX on the threshold...
Last, I merged that last query with the threshold table pulling out the data associated with that threshold.
The first step was just to do the join and merge the employee and threshold tables such that for each employee I could have 1 or more records... Note - the thresholds were in a way where if they worked 0 hours they received one thing, 6 hours something else, etc...
Next, I did a Group By on that intermediate query with a MAX on the threshold...
Last, I merged that last query with the threshold table pulling out the data associated with that threshold.
Can you not simply do something along the lines of...
Change the query design into SQL view then something along the lines of...
Using a max to get thresholds is a bad idea because if there are many records you run VERY big recordsets, using this join construct makes it much more managable.
Change the query design into SQL view then something along the lines of...
Code:
Select ...
From yourtable
Join Thresholds on hours > BottomLimit and Hours <=TopLimitThe thresholds only have one limit, i.e. the point at which it goes into effect so you have one starting at 0, one starting at 6, etc... I could put in an ending threshold and then do a < on one side and a >= on the other... I know a priori that there will not be many records for the max - at most 3...
well if you have a 100k records on the otherside, its stil 300k records...
Not quite as bad as my situation where I have 15-20 categories, which goes crazy fast...
Though the < >= join is more "future proof" as long as you have a working solution I guess thats most important
Not quite as bad as my situation where I have 15-20 categories, which goes crazy fast...
Though the < >= join is more "future proof" as long as you have a working solution I guess thats most important
