SQL Magic

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Any ideas regarding the SQL necessary to perform the following

I want to calculate the Number of Days Between the EFF DATE BONUS field, within each EMPLID and INCOME CODE group. Rank is a field that ranks the EFF DATE BONUS field from most current to oldest (within each EMPLID and INCOME CODE group). I need a calculation that subtracts Rank from Rank x +1 for each EFF DATE BONUS within each EMPLID AND INCOME CODE. The DaysBetween column displayed contains the actual calcs I'm looking for (did it in Excel). Any ideas

EMPLID INCOME CODE EFF DATE BONUS Rank TargetType DaysBetwee
74870 BON1 37968 1 NEW
74870 BON1 37772 2 NEW 19
74870 BON1 37689 3 OLD 8
74870 BON1 37177 4 OLD 51
74870 BON1 36829 5 n/a 34
74870 BON2 37772 1 NEW
74870 BON2 37716 2 OLD 5
74870 BON2 37689 3 OLD 2
74870 BON2 37177 4 OLD 51
74870 BON2 36829 5 n/a 34
74870 BON3 37772 1 NEW
74870 BON3 37689 2 OLD 8
74870 BON3 37514 3 NEW 17
74870 BON3 37177 4 OLD 33
74870 BON3 36829 5 n/a 34
74870 BON4 37912 1 NEW
74870 BON4 37884 2 NEW 2
74870 BON4 37772 3 NEW 11
74870 BON4 37689 4 OLD 8
74870 BON4 37514 5 NEW 17
74870 BON4 37177 6 OLD 33
74870 BON4 36829 7 n/a 34
 
Hi,




Bring the table twice, in the query, one get an _1 appended to its name.
Join the references through their EMPLID. Again through their INCOME.

Drag the rank field of reference_1 into the grid, add the criteria: = 1+
[reference].rank

Then

reference.[date] - reference_1.[date]

gives the difference in day between consecutives ranks ( for each group of
EMPLID, .INCOME , the fields you initially joined together)




Hoping it may help,
Vanderghast, Access MVP



Kirk P said:
Any ideas regarding the SQL necessary to perform the following?

I want to calculate the Number of Days Between the EFF DATE BONUS field,
Click to expand...
within each EMPLID and INCOME CODE group. Rank is a field that ranks the
EFF DATE BONUS field from most current to oldest (within each EMPLID and
INCOME CODE group). I need a calculation that subtracts Rank from Rank x +1
for each EFF DATE BONUS within each EMPLID AND INCOME CODE. The DaysBetween
column displayed contains the actual calcs I'm looking for (did it in
Excel). Any ideas?
 
Back
Top