Advanced problem using auto-number and datediff to find readmissio

[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

 

[MGFoster]

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?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You really should NOT store the AdmitNo and AdmitDays in a table. Since
these are calculated values it is better to use a View (query in
Access). That way every time you run the View (query) it will show you
the current values. Otherwise, you'll have to remember to run timely
UPDATEs on the table to maintain the calculated values.

Here's a View that will show you what you ask for. Since you indicated
the data should only be for disdates after 2/18/08 some of the data will
be blank 'cuz by including data on, or before, 2/18/08 would change the
AdmitNo.

SELECT unique_id, recordnum, admdate, disdate,

(SELECT COUNT(*) FROM Admissions WHERE recordnum = A.recordnum AND
disdate <= A.disdate AND disdate >= #2/18/08#) AS admitno,

DateDiff("d",(SELECT disdate
FROM Admissions
WHERE recordnum = A.recordnum
AND disdate < A.admdate
AND disdate >= #2/18/08#
AND disdate = (SELECT MAX(disdate) FROM Admissions WHERE
recordnum=A.recordnum AND disdate<A.admdate)), admdate) As admitdays

FROM Admissions As A
WHERE disdate >= #2/18/08#
ORDER BY recordnum, disdate

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSeTxDoechKqOuFEgEQI0tACfY3uBU7bqnrc0+AuSBM/qUp2RFzIAoO52
Xo1iaCURQUYlzfbhJ9nUtsEf
=AEBu
-----END PGP SIGNATURE-----

 

[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.