Incrementally Number

  • Thread starter Thread starter Jeff C
  • Start date Start date
J

Jeff C

Working with the following:

SELECT JeffC_A_Dates.[Phys #], JeffC_A_Dates.[MR #], JeffC_A_Dates_1.Admit
AS InitialAdmit, JeffC_A_Dates_1.Discharge AS InitialDisch,
JeffC_A_Dates.Admit AS Readmit, JeffC_A_Dates.Discharge AS ReadmitDisch,
DateDiff("d",JeffC_A_Dates_1.Discharge,JeffC_A_Dates.Admit) AS Readmission
FROM JeffC_A_Dates INNER JOIN JeffC_A_Dates AS JeffC_A_Dates_1 ON
JeffC_A_Dates.[MR #] = JeffC_A_Dates_1.[MR #]
WHERE (((JeffC_A_Dates_1.Rank)=[JeffC_A_Dates].[Rank]-1))
ORDER BY JeffC_A_Dates.[MR #], JeffC_A_Dates.Admit;


I need to create another field in the query that incrementally numbers each
record beginning with 1 - Can anyone help with this?

Thanks in advance
 
--
Jeff C
Live Well .. Be Happy In All You Do


Allen Browne said:
See:
Ranking or numbering records
at:
http://allenbrowne.com/ranking.html#query

The article explains 2 approaches: a subquery or temporary table.
Click to expand...

Thanks Allen - this is an interesting project for me, the resulting integer
is going to be used as a "label" for each record. There may be a better
approach but this "label" will be loaded into a field in an OLAP cube to
identify pairs of readmissions. I'll work with the ideas in your article.
Do you have a better idea?
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Jeff C said:
Working with the following:

SELECT JeffC_A_Dates.[Phys #], JeffC_A_Dates.[MR #], JeffC_A_Dates_1.Admit
AS InitialAdmit, JeffC_A_Dates_1.Discharge AS InitialDisch,
JeffC_A_Dates.Admit AS Readmit, JeffC_A_Dates.Discharge AS ReadmitDisch,
DateDiff("d",JeffC_A_Dates_1.Discharge,JeffC_A_Dates.Admit) AS Readmission
FROM JeffC_A_Dates INNER JOIN JeffC_A_Dates AS JeffC_A_Dates_1 ON
JeffC_A_Dates.[MR #] = JeffC_A_Dates_1.[MR #]
WHERE (((JeffC_A_Dates_1.Rank)=[JeffC_A_Dates].[Rank]-1))
ORDER BY JeffC_A_Dates.[MR #], JeffC_A_Dates.Admit;


I need to create another field in the query that incrementally numbers
each
record beginning with 1 - Can anyone help with this?

Thanks in advance
Click to expand...
Click to expand...
 
Back
Top