Advanced problem using auto-number and datediff to find readmissio

M

mdkryptoking

I have a doozy of a process i am trying to complete. First, I am trying to
auto number the field admitno in in the sample data below:

unique id recordnum ADMDATE DISDATE AdmitNo readmit
206 571 02/15/2008 02/18/2008 1
990 571 10/02/2008 10/05/2008
306 220 03/19/2004 03/24/2004
730 220 05/28/2004 06/01/2004
926 220 06/17/2004 06/24/2004
083 220 09/30/2004 10/04/2004
982 220 10/18/2004 10/22/2004
798 220 12/06/2005 12/08/2005
305 220 06/24/2006 06/26/2006
489 220 11/23/2007 11/29/2007
822 220 01/10/2008 01/14/2008
234 220 02/09/2008 02/13/2008
837 220 03/17/2008 03/22/2008 1
824 220 07/25/2008 07/28/2008
646 220 09/08/2008 09/12/2008
632 220 12/02/2008 12/05/2008
403 220 12/13/2008 12/21/2008

The 1 represents the record i want to start counting at for each recordnum.
Each uniqueid is a difference occurence of recordnum. I do not care about
what happened before admitno = 1 for each recordnum. So basically i want it
to look like this:

unique id recordnum ADMDATE DISDATE AdmitNo admitdays
206 571 02/15/2008 02/18/2008 1
990 571 10/02/2008 10/05/2008 2
306 220 03/19/2004 03/24/2004
730 220 05/28/2004 06/01/2004
926 220 06/17/2004 06/24/2004
083 220 09/30/2004 10/04/2004
982 220 10/18/2004 10/22/2004
798 220 12/06/2005 12/08/2005
305 220 06/24/2006 06/26/2006
489 220 11/23/2007 11/29/2007
822 220 01/10/2008 01/14/2008
234 220 02/09/2008 02/13/2008
837 220 03/17/2008 03/22/2008 1
824 220 07/25/2008 07/28/2008 2
646 220 09/08/2008 09/12/2008 3
632 220 12/02/2008 12/05/2008 4
403 220 12/13/2008 12/21/2008 5

then i need to find the date difference between the disdate of 1 and the
admdate of 2 (2 and 3, 3 and 4, etc). For example: the number of days between
12/05/2008 and 12/13/2008 is 8 days. I need 8 to show up in the admitdays
field for uniqueid 403 recordnum 220. So my final result would look like this:

unique id recordnum ADMDATE DISDATE AdmitNo admitdays
403 220 12/13/2008 12/21/2008 5 8

I realize that this will take several steps but I dont know how to tell
access to do this. Can anyone help?

Thank you
 
M

mdkryptoking

This worked however the results ended up like this:

824 220 05/08/2008 05/13/2008 47
546 220 07/25/2008 07/28/2008 125
646 220 09/08/2008 09/12/2008 170
632 220 12/02/2008 12/05/2008 255
403 220 12/13/2008 12/21/2008 266

so it counted from the beginning admdate each time. Thank you very much.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ranking on SQL 1
RANK 1
select entries keyed in after 5:30 PM 1
Rolling Average Calculation 3
Find then highlight in yellow 6
Difference in Date/Time 1
show top values 5
Excel Jululian 5

Top