Count how many days since and on what date

[burrina]

I am in a quandary and can't think of how to do this. I have scheduled events that need to run and what if they are missed? On what days were they missed and how many times up until now have they been missed? So I need to Create a list of missed events and what dates they were on so I can check them off to be entered. I have managed to figure out how many days have transpired and how many times the event was missed. On what days, NOT!
Here are my field names and criteria as a screen shot. By my calculations EventID 21 has been missed 15 times. So I would ask the user if they wanted to enter those transactions now or not and if so which ones? I FORGOT TO MENTION THAT THESE EVENTS ARE SCHEDULE TO RUN EVERY DAY.

Here is what I have done since the post:
I added these 2 fields to my table, hope it works! I have nothing to go off so I did a update query and set the dates to yesterday and to true to test.

missdte Yes/No was this a missed event?
missdte Date/Time On what date was it missed?

I added this code to my OnCurrentEvent Procedure:



'Purpose: To check for missed event, if found mark missed as true and set date to today.
If [nextschddte] < Date And IsNull([ChkNo]) Then
Me.missev = True
Me.missdte = Date
End If


EventID is PK
EventStart Date Event Was First Entered (Date)
nextschddte is the next scheduled date to be entered (Date)
dteentered is the last date it was entered (Date)
enter if it was entered or not (yes/no)
ChkNo is Check Number (Number)
Days number of days since eventstart
Missed number of times event has been missed

[jzwp11]

Just using your EventID = 21 record, you say that you have 15 missed occurrences but truly you are saying that you have 15 missed occurrences since the last date entered. Wouldn't you need to count dates missed between the start date and the last date entered date? For example, let's say that the event was missed on 1/7/2013 and on 1/9/2013, so your number of missed events would actually be 17 not 15. By just recording the most recent dteentered date, you are not capturing what you need to determine the dates that have been missed since the start date. I think you need to capture each date entered as a record in a separate but related table.

Also, you do not need the field nextschddte. It would be better to have a number field to hold the frequency (1=daily) and you can calculate the next scheduled date by adding 1 to either the current date or whatever date you want to reference. I'm not sure the enter check box is doing anything of value either.

I would suggest the following table structure

Event
-pkEventID primary key, autonumber
-dteStart (start date)
-longFreq (frequency of reoccurence)

tblEventEntered
-pkEventEnterID primary key, autonumber
-fkEventID foreign key relating to tblEvents
-dteEntered

With this structure, you can determine what you described. Now as to the dates actually missed, that will require something more than just a simple query since you have to go through each date between the start date and the current date (I assume or some other reference date) and check to see if a date entered record exists (in tblEventEntered). There are a couple of different ways to handle this. One way requires Visual Basic for Application (VBA) code to loop through each date in the period (from the start date to today) and determine if it was a date entered or a missed date. The missed dates would then need to be accummulated somewhere. The second option would require an additional table with all possible dates and a query.

[burrina]

Are you familiar with this Scheduler? The scheduling is done via d, for daily, ww, for weekly and so on instead of using a number. It is intense. It is being used for a Check Register db. enter is used to show whether it has been entered or not into the check register.And nextschddte is used to show when the next scheduled event is to occur. User needs to see this. Yes, you are correct in days missed since the original date. Since I am still in beta with this I went back and entered dates for all missed occurrences so I could track from that point onward. As I said, I added 2 fields which i think will accomplish what I want. They are updated on every form open and will show if it was missed and the date it was missed.It also has a popup history form to display ALL missed occurrences. I don't think, NO, I know I am not good enough to code this any other way.If I can now query missed occurrences and ask user if they want to either dismiss them or enter them, that is what I am after. Pretty good thinking on your part from what I gave you to go on though. Here is a pic of the scheduler. Suggestions are welcomed.

Thanks,

[jzwp11]

No, I am not familiar with the scheduler db you showed.

The scheduling is done via d, for daily, ww, for weekly and so on instead of using a number

These text values might be tied to numbers somewhere in a table or in code. The d, ww, m are typical interval designations used in date related functions in Access. For example: dateadd("d",1,date()) will add 1 day to the current date.

It also has a popup history form to display ALL missed occurrences.

In what table are these missed occurrences being stored or are the actual dates when things happen being stored? Can you post the list of fields in the table where data is being stored (either the missed occurrences or the entered occurrences)? Or if you prefer, you can zip and post a copy of the database with any sensitive data removed or altered.

[burrina]

Take a look and tell me what you think!

Thanks,

Dave

[jzwp11]

In the frmReadMe, a second table TReg was mentioned but it was not included in the database. So is what you posted the entire database?

[burrina]

Definitely NOT. The db is a good size and then there is the thing about me not giving it away. I appreciate the help but I did think I gave you enough to go on. It is irrelevant about the other table and such. It would be like you trying to explain something complex you designed to me. I would have no clue. I apologize for giving you too much information.

[jzwp11]

Without capturing each related event or the missed events you will not be able to get to the information you described. I thought that perhaps one of the other tables had that information.

[burrina]

NO, everything is there that is needed to get to the missed events. The date (EventStart) when the event was first entered and also the last date when it was entered (dteentered) and the next scheduled date (nextschddte). I understand if you want to bow out of this one.

Thanks,

[jzwp11]

It also has a popup history form to display ALL missed occurrences

Since you said this, it suggests that the missed occurences are being stored somewhere. If you look at the record source for this form, it should tell you in which table that data is stored.

[burrina]

Missed events DO NOT get stored anywhere else. You are getting confused here. Data is exported to another table is all. Again though that does NOT matter. It is almost an exact duplicate of data since fields have to be compatible in order to do the append query. Forget about that. My query is a little off since I do not account for the times that the event WAS entered between the two dates is all. We have a start date, a last entered date and a date when it is supposed to be entered again. That's all we need. I don't think I am explaining myself well enough. Example; EventID 21 It was entered on 1/4/2013 and then entered again on the 10th but it was supposed to be entered every day after 1/4/2013 but it was not. If it was, it would be in that table. I created this monster, well almost all of it anyway. The schedule thing comes from Allen Browne. I have modified it for my needs. Take the number of times between the 2 dates in the query and subtract the times it was entered and there you have it. At least that is what my brain is telling me.

[jzwp11]

Example; EventID 21 It was entered on 1/4/2013 and then entered again on the 10th but it was supposed to be entered every day after 1/4/2013 but it was not

I did not see any eventID that repeated in the db you posted; in fact, EventID is an autonumber field so it cannot repeat. If multiple records in the table represent the different dates entered for the same event/person/account (whatever it is you are tracking), how do you know which ones are related to each other? If we have that then we can probably resolve your issue.

[burrina]

I just meant to look at that record. I know I am new and all but not that new. What is being tracked is the Bank Account, Payee is the crucial data.
1.Bank
2.Payee
3. ChkNo

In other words, enter a new record with the same info as EventID 21 ( I am still not that new, NO same ID) and it is supposed to be exported or entered into another table on the next day and there you have it. You have scheduled an event! Something happens and the event does NOT get entered and here lies the problem.
BUT, if it is missed and not exported then, this is what I am after. Does that make sense?

Cant have duplicate check numbers, no problem. So what gets entered is;
Bank, Type of Transaction, Payee, Frequency Amount, Debit or Credit, That's it.


I forgot about the table having lookups and such, my bad. Here is the table so you can enter a new record and match it with number 21 only a new ID.

[jzwp11]

I'm still confused about what constitutes related records. If I look at this data and since you said that bank and payee were related, and let's assume I want to focus on the 2 Wells Fargo records. So the first start date is 1/5/2013 so the next scheduled date is 1/6/2013 and that shows as entered, the next entered date for the bank/payee combination is 1/11/2013, so 1/7/2013, 1/8/2013, 1/9/2013 and 1/10/2013 would be the missing dates. Is that correct?

EventStart	Bank Used For Scheduled Transaction	Person Whom Event is Scheduled For.	nextschddte	dteentered
1/4/2013	Bank Of America	Dave, Elliott	1/5/2013	1/5/2013
1/5/2013	Citigroup	Dave, Elliott	1/12/2013	1/5/2013
1/5/2013	Bank Of America	Dave, Elliott	1/6/2013	1/6/2013
1/5/2013	Citigroup	Dave, Elliott	2/4/2013
1/5/2013	Wells Fargo	Dave, Elliott	1/6/2013	1/6/2013
1/8/2013	Wells Fargo	Dave, Elliott	1/9/2013	1/11/2013

[burrina]

YES, that is correct. Remember, this is a Check Register Database. SO: ALL record(s) for that Bank account go with that Bank account. ALL Wells Fargo records would go into the check register with that Bank Account. Everything else is just details. Everything works as is, just the missing scheduled events is all. How to best deal with those! 2 choices, either enter them or dismiss them. The event CAN be rescheduled later if the user wanted to, or if they deleted the entire record, i.e. event, they could re-create a new one. Hey, I think we are making progress, a couple more 20 or 30 posts and we will have it, Ha!