Need Help With Code (Averages & Stats)

[blue1914mab]

I, with the assistance of PHV and Rubbernilly, have created a Transaction Log table, that logs every time a user checks out, and returns a patient file. I need to create stats based on that log. On the users personal stats pages, I have 4 unbound text boxes that are designed to hold their individual stats. The first box "Total Patient Records Checked Out, works great using =DCount("Status","PatientInventory","Status='CHECKED-OUT' And EmployeeID = '" & DLookUp("LastName","tblEmployees","UserID='" & [CurrentUser] & "'") & "'"). This is based on table PatientInventory.

the TransactionLog table has 5 fields related to the stats needed.

1)TansactionID=Primary Key
2)PatientID= References Each Patient Recored in the table PatientInventory
3)AssignDate= Date record checked out
4)ReturnDate= Date record returned
5)EmployeeID= Lookup ComboBox based on "LastName" of person who checked the record out.


The formula I need is:
1) Total Records Checked Out (Month)
2) Totla Records Checked Out (Year)
3) Average Checkout Time (Days)

Im not good with code. Thanks why I need much help!!!

Blue

 

[rubbernilly]

Hey, blue... glad things are working out so far.

Let's try this for the Total Records Checked Out (Month):

=DCount("TransactionID","TransactionLog","EmployeeID = '" & DLookUp("LastName","tblEmployees","UserID='" & [CurrentUser] & "'") & "' AND Month(AssignDate) = Month(Date)")

If that works right, then just modify it a little for the Per Year calculation:

=DCount("TransactionID","TransactionLog","EmployeeID = '" & DLookUp("LastName","tblEmployees","UserID='" & [CurrentUser] & "'") & "' AND Year(AssignDate) = Year(Date)")

 

[rubbernilly]

Ok, now let's tackle that Length of Checkout field you want.

The easiest way is to create a query that has SQL similar to:

SELECT DateDiff("d",[AssignDate],[ReturnDate]) AS LengthOfCheckOut, EmployeeID, AssignDate, ReturnDate
FROM TransactionLog
WHERE (((TransactionLog.ReturnDate) Is Not Null));

My suggestion below assumes that you have saved that query as "TransactionLogQuery." This will get you the complete average for the user in question:

=DAvg("LengthOfCheckOut","TransactionLogQuery","EmployeeID = '" & DLookUp("LastName","tblEmployees","UserID='" & [CurrentUser] & "'") & "'")

Some things for you to consider...

...Does it matter to this average calculation when the record was checked out? (do you only want to average records checked out this month? this year?)

...Does it matter to this average calculation when the record was returned?

In either case, you will need to modify the <criteria> component of the DAvg statement similarly to the previous examples of DCount (using the proper field - or both fields - in the statement).

HTH

 

[blue1914mab]

The code for month total did not work.

The code below however does works. I tinkered for a few hours to no avail trying to add the month piece to this:

=DCount("StatusID","TransactionLog","StatusID='CHECKED-OUT' And EmployeeID = '" & DLookUp("LastName","tblEmployees","UserID='" & [CurrentUser] & "'") & "'")

Answer to our question: It does NOT matter when it was checked out or returned.

 

[blue1914mab]

Sorry, extra explanation required. The code I posted works in regards to calculating by user the # of transactions. However the "Month/date" piece needs to be added to what I posted in the previous message.

Blue

 

[rubbernilly]

Hey, blue...

I recreated what you have there and these worked for me:

=DCount("TransactionID","TransactionLog","EmployeeID = '" & DLookUp("LastName","tblEmployees","UserID='" & [CurrentUser] & "'") & "' AND Month(AssignDate) = Month(Date())")

=DCount("TransactionID","TransactionLog","EmployeeID = '" & DLookUp("LastName","tblEmployees","UserID='" & [CurrentUser] & "'") & "' AND Year(AssignDate) = Year(Date())")

I was missing the () after the "Date" function.

On that same note, I don't know how your statement worked... since you don't have a "StatusID" in your TransactionLog table...

??

 

[blue1914mab]

Sorry,if I did not mention that. It is the field that houses "CHECKED-OUT" and "AVAILABLE" in the TransactinLog table

Oh, The Average Length code worked GREAT!!! Thank You.

Question: I would like to create a field called "DueDate" in the PatientInventory table (houses all the individual patient records) or in the TransactionLog that calculates a due date based on the "StatusID"= 'CHECKED-OUT', and the "AssignDate", 7 days after, not including weekends. Is there a way to do that?

 

[PHV]

DueDate: IIf([StatusID]='CHECKED-OUT', [AssignDate]+9-(Weekday([AssignDate])>=5), Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[blue1914mab]

Thanks PHV!!

Should that be an unbound field on a form? I would like to have it as field in the Transaction table, so I can report on it. If so. where would I put that code, in the "Validate Rule" spot?

Blue

 

[rubbernilly]

Add the field to your table, and then add it to the form as a bound control.

Assign the value of PHV's calculation to the control in the code for the assign button... so that when the button is clicked, a Due Date is generated and saved with the record.

As for adding the Month check to the working code you posted already, this should be what you're looking for:

=DCount("StatusID","TransactionLog","StatusID='CHECKED-OUT' And EmployeeID = '" & DLookUp("LastName","tblEmployees","UserID='" & [CurrentUser] & "'") & "' AND Month(AssignDate) = Month(Date())")

 

[rubbernilly]

One more thing...

If you do add the field to the table and add a bound control to the form, make sure that you clear the value in your "Return" button.

 

[blue1914mab]

I added the DueDate field to the PatientInventory table instead of the TransactionLog table. That way the users can see it as part of their stats section on the "Welcome Page". I referenced it (via. the checkout button) to post it in the TransactionLog table using the same field title.

I have one last question, and this Database will be DONE! HALLELUJAH!!!!!!!!! At least to get the group started.

Based on what we have, I need to show how many times they went over the due date before returning the recored, and how many days (total) they have gone over the due date. That would be based on all their check-outs and returns, not individual check-outs and returns. I hope that made sence.

Blue

 

[rubbernilly]

The PatientInventory table was the right place to put the field, since that is where it is associated.

If I understand you correctly, you also added it to the TransactionLog table? That's good, because that will let you get the history you are looking for.

Are you beginning to understand the DCount, DAvg, and DLookup functions? Because we can probably use one here to retrieve the stats you are looking for.

=DCount("TransactionID","TransactionLog","ReturnDate > DueDate")

That should get you the entire group. To limit it to one employee:

=DCount("TransactionID","TransactionLog","ReturnDate > DueDate And EmployeeID = '" & DLookUp("LastName","tblEmployees","UserID='" & [CurrentUser] & "'") & "'")

HTH