Results 1 to 2 of 2
Thread: DMax (A2K)
2004-12-01, 16:24 #1
- Join Date
- Feb 2001
- Louisville, Kentucky, USA
- Thanked 0 Times in 0 Posts
Good Morning Everyone,
This may be too complicated (hope not!).
I have DATE, CallNum, and CallerName fields in my table. A CALLER will call in with CASES. For EACH CASE, the CallName is entered as well as the DATE. The number of CASES varies, Fred may have 4 CASES but Mary who is the next CALLER may have only 1 CASE. Each CALL is tracked (via CallerName). I now need to track the number of CASES per CALL (CallNum).
CallNum is a number field. Easily done on a form in an AfterUpdate Event to place a count for each call. Problem is ....
Each DAY, CallNum should start back at 1. If Fred Called in w/4 CASES, his CALL is counted as 1 but 3 entries where is name resides should NOT be counted as a CALL but rather each should be counted as a CASE (therefore the other 3 entries CallNum should be 0). His report should be CALL #1, 4 CASES. Mary calls in next, she would be CALL #2, 1 CASE.
To add to the problem, each day the Count should start over.
I've tried several things working with the DMAX function, but that can't be applied (or I don't think so) because DMAX will return the MAX # in the entire field. It would work fine if I could get it parse off the DATE and Caller Name. Meaning, if DMAX could look at the DATE and compare to Today and return the MAX for the DATE period (Today), I could use this, but adding to the problem, it needs to compare CallerName's to the previous entry and determine if they Match, if so, DON'T count, if they DON't match, then count.
I know in my head how it should work, but the coding for it is over my head.
I'm sure this is a bit much and I would certainly understand it if it goes beyond the realm of "HELP", but I WOULD appreciate ANY IDEAS!
Thanks!Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
2004-12-01, 16:37 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: DMax (A2K)
You should have two tables: Calls and Cases, with a one-to-many relationship: each Case belongs to a single Call, but there can be several Cases for a single Call.
To assign a Call number, you only need to look at the date field. Note: please don't use Date as name for a field. It can easily lead to confusion, since Date is also a built-in VBA function. I would use CallDate or something similar. The Call number for a new record in the Calls table would be
<code>Nz(DMax("CallNum", "tblCalls", "CallDate=Date()"), 0) + 1</code>
where tblCalls is the name of the Calls table.
The DMax function retrieves the highest assigned call number for today. The Nz function catches the case that there are no calls yet for today, and returns 0 then. Finally, this number is incremented by 1.