# Thread: calculating dates in a group (Access2000)

1. ## calculating dates in a group (Access2000)

Many of you have helped me with the databases I've been creating to use in the ** Possible SPAM post - please alert a Moderator (2)** where I work. I've learned more and more about Access from you and now my supervisor has asked me to do stretch my skills to the limit and inclue reporting in the latest database. This one is used to track patients who receive a drugs called Pegasys which is used to treat Hepatitis C. I need to calculate the number of days since the last time the prescription was filled. For instance, if it was filled 4/1 and 4/15, it would be 14 days. And another glitch, if 4/1 was the first time the prescription was filled, I need the value 0 to return. I've attached a spreadsheet of how the final report should look but it is one I just typed up in Excel. I really don't even know where to begin with calculating these dates. I had thought they meant number of days from the reporting date which would be easy (Date-DateofService).
I would be happy to read and learn but I don't even know where to begin or if RecordSets is even what I'm really asking about.
My problem is that I need to do this per patient and I really have not played around with Record Sets since I took a class in Access 4 years ago. I have the Access 2000 Developer's Handbook but there is so much info I don't know where to start.

2. ## Re: calculating dates in a group (Access2000)

I have imported your Excel sheet into an Access table called sheet1.
Try running the query (called Query1):
SELECT Sheet1.RNA_ID, Sheet1.NDC, Sheet1.DOS, DMax("DOS","Sheet1","RNA_ID=" & [Sheet1].[RNA_ID] & " and NDC= '" & [Sheet1].[NDC] & "' and DOS <#" & Format([Sheet1].[DOS],"mm/dd/yy") & "#") AS LatestDOS, iif(isnull(LatestDOS),0, DateDiff("d",LatestDOS,Sheet1.DOS)) as Days
FROM Sheet1
ORDER BY Sheet1.RNA_ID, Sheet1.NDC, Sheet1.DOS;
This should give you the number of days since the last prescription was filled for all records in the table.

To give you the latest only you will need a query (based upon Query1) giving the Max DOS date. This query is called Query2.
SELECT Query1.RNA_ID, Query1.NDC, Max(Query1.DOS) AS LatestDte
FROM Query1
GROUP BY Query1.RNA_ID, Query1.NDC;

To then get the Number of days since last prescription for the latest you need another query (called Query3):
SELECT Query2.RNA_ID, Query2.NDC, Query2.LatestDte, Query1.Days
FROM Query2 INNER JOIN Query1 ON (Query2.LatestDte = Query1.DOS) AND (Query2.NDC = Query1.NDC) AND (Query2.RNA_ID = Query1.RNA_ID);

3. ## Re: calculating dates in a group (Access2000)

Excellent query, Pat!

An alternative way of calculating the Days field is DateDiff("d",Nz([LatestDOS],[DOS]),[DOS]) AS Days. This avoids the use of IIf; instead, the Nz function substitutes DOS for LatestDOS if the latter is Null, making DateDiff zero.

Niggling note: if the query is going to be used as record source for a report, it is not necessary to specify a sort order in the query; it'll have to be done in the Sorting and Grouping window for the report.

4. ## Re: calculating dates in a group (Access2000)

<img src=/S/cool.gif border=0 alt=cool width=15 height=15> You guys are so awesome! I thought this would take all sorts of VB and things I don't know about. Thanks a milion! Can't wait to get to work in the morning and try this out. My boss will think I'm super woman. Our IS guy has been working on this for months in Crystal Reports but couldn't get it to work.

5. ## Re: calculating dates in a group (Access2000)

Why thank you Hans, thanks also for the note re the Nz function.

Niggling note or not, it is good advice, one I hadn't picked up on.

6. ## Re: calculating dates in a group (Access2000)

I thought I would post the database to you. Note that after you import the Excel sheet that the DOS field is text, I made it a date type field.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•