File Tracking: Determining if a file should have been received.

[venivici]

Access Version: 2003

Problem Defined:
Create a file tracking database to track the submission of files for various clients based upon imported data. Each client has varying rules when a file is expected to be received, for example, weekly on Monday, biweekly on a Wednesday, monthly on a Friday, the 15th and last day of the month, etc. I need to be able to run queries/reports to identify the information when a file is received, which should be easy enough. But, I also need to identify if a file has not been received and it should have been.

Table Design
Right now, I have 3 tables. One table where the data is imported about the files, which is keyed by a 3 letter Client code. The Client code from the file instance table attaches to a Client Table which hold the client information - name, etc so forth. The client table is connected to a DateRules table, where the expected date, frequency will be listed.

Questions:
Is my overall table design help setup correctly How do I query to find that I should have received something and didn't? Generally, I understand the date functions in Access, but for some reason I am haveing a hard time getting my head around this one.

Thanks for your comments/help. If you have any additional questions or need clarification, just let me know.

 

[tehNellie]

No idea whether you've set your data base up correctly from your description.

If you're going to design an entire system around manipulating and interpreting Date/Time data I think it would be worth nailing completely the inbuilt Access Date manipulation Functions like DateAdd(), DatePart(), DateDiff() and so on. These give some pretty powerful options and capabilities yet frequently seem to be overlooked.

 

[The_Doc_Man]

But, I also need to identify if a file has not been received and it should have been.

If you are the sender of these letters, then you run into an "Old Programmer's Rule" - Access can never tell you anything that you didn't tell it first.

The issue will always be that Access and time-based events are not necessarily the most intimate of friends. That is because Access is designed to be "sparse" in the population of its tables. You won't necessarily have every possible slot for file arrival so you have to think on some method that isn't brute-force.

You need to have a "file sent" slot and a "file receipt acknowledged" slot. If you have a "sent" slot that is based on your schedule of expectations then it becomes a matter of finding mismatched sent/receipt slots. That is your starting point. Then you can check for a difference between date sent and expectation of receipt based on adding a couple of days for the postal carrier or courier or whatever you use for sending.

 

[venivici]

^^^^
Thanks for the input, I really appreciate it. That is the issue I am running into of course, is that Access only knows something if you tell it, which I understand. The other issue is that the expected sent dates are not completely static, so I am not able to populate a date table for a given client and just do a compare of expected vs received. They are based off of different rules.

Also, I would have to have a way to go back if an expected sent date was missed then was fulfilled after the expecation, and mark it as sent, but still track the fact that it was late. Hopefully I am making some sense and you can see where my problems lie.

 

[Simon_MT]

You can flag the availablility of a file but doing a Dir of the file name and using FSO glean information off the file, this is how I manage images. But there is a direct association between a file and a record rather than having to anticipate what files that have arrived. Having said that, it maybe possible to list the contents of a directory and update a table?

Simon