Sequentially number records

[sheederj]

Hi, i have a query which contains two fields: PtId and visitdate. I'd like to create a field that sequentially numbers the visits.

If you're really smart, the second part of my problem involves computing the time from the last (or max) visit to the visit before it. I was thinking if I had the visits numbered it would make this task easier, but if there is another way, that would be even better!
Thanks so much for your help!

 

[FoFa]

Capture a date and time stamp, and basically gives you the proper sequence

 

[pbaldy]

A DMax or subquery gets you the max date less than the current date in a query, from there it's easy to calculate the difference.

 

[sheederj]

i'm sorry - i'm not super smart with this, but i'm not sure i understand. i can sort the records fine by the visitdate, but i need a column that would actually number them so that i can eventually subtract the max visit number to the max-1 visit number.

So, my query might look like this:

ptid visitdate visitnumber
1 1/1/05 1
1 1/2/05 2
1 1/3/05 3
2 1/1/05 1
2 1/2/05 2
2 1/3/05 3

Then i want to be able to subtract the difference between the date for the max visit number and the max visit date number - 1's date.
Thanks!

 

[pbaldy]

You don't need the sequence number to calculate the difference. Get the previous date in a query using DMax and subtract.

 

[sheederj]

again- i'm not super smart with this stuff - how exactly do i do get the previous date in a query and use dmax to subtract?
Thanks!

 

[pbaldy]

Have you tried?

DMax("visitdate","TableName","ptid = " & [ptid] & " AND visitdate < #" & [visitdate] & "#") AS PrevVisit

 

[sheederj]

ok, tried that but it keeps saying "invalid date value" and it doesn't like the "as PrevVisit" either - says invalid syntax.

Thanks!

 

[pbaldy]

"AS PrevVisit" is how it would look in SQL view. In design view:

PrevVisit: DMax("visitdate","TableName","ptid = " & [ptid] & " AND visitdate < #" & [visitdate] & "#")

Is that really a date field? That should work if it is.

 

[sheederj]

now it's running, but in the prevvisit column it just says "#error". the date field is really a date too.
Thanks!

 

[pbaldy]

Did you put in the correct table name, and are the other field names correct?

 

[sheederj]

db attached if that helps

i've attached a db that has the table and the query - maybe that will help?
So, just to clarify, what i need to figure out is the difference between the last visit date and the 2nd to last visit date.
Thanks very much for your help!

 

[sheederj]

oh yeah - the last visit date and 2nd to last for each patient. also, one more thing, the ptidgrv is the id i need to group on - it's a variable that combines the ptid and grv as that is what is unique for each patient.
Thanks!

 

[sheederj]

i think i put everything in right - i've attached it if you want to check it, but i think it's all right.
Thanks!

 

[pbaldy]

SELECT tblvisits!ptid & "_" & tblvisits!grv AS ptidgrv, Tblvisits.visitdate, First(DMax("visitdate","tblvisits","PtID = '" & [ptid] & "' AND Grv = " & [Grv] & " AND visitdate < #" & [visitdate] & "#")) AS PrevVisit, VisitDate - CDate(PrevVisit) AS Diff
FROM Tblvisits
GROUP BY tblvisits!ptid & "_" & tblvisits!grv, Tblvisits.visitdate;

 

[pbaldy]

BTW, you did not have the field names correct (you had date instead of visitdate). The big problem was that you had a different structure than described earlier in the thread.

 

[sheederj]

THANKS!!!! That worked great! I really appreciate your help!