MS Access help

usslindstrom · August 24th, 2005, 01:40 AM

I have a question about throwing together an MS Access database. I`ve used Access on a regular basis, and know a little about it... But I`m having trouble trying to figure out if the database I would like to create is even possible...

I need to set up a schedule of inspections that are required every 28 days. So, I would like to put an initial date in a block, and let the computer calculate every 28 days and plot it on a calendar. So for instance, if an inspection is due today, I could pull up a calendar and see the next time it`s due... Plus, I could pull up persay next June and see what day it was going to fall on.

If this is at all possible in MS access, I need to throw in another equation to the mix. We have the option to pull an inspection 3 days early, or complete it 3 days late if the requirement deems. So I would need to put an equation in to plot those days as well.

It`s kinda hard to explain, but I drew a small example to help... This an example of what I would like to see (minus the arrows) on any month that I decided to look at...

If MS Access is the wrong program to try this in, please give me your insite - I need to complete this in a timely manner for a project at work.

Thanks in advance.

jkrohn · August 24th, 2005, 01:47 AM

How I read it, Access is the wrong thing to do this in....

You could simply do this in javascript and have it run as an HTML file. (No webserver necessary)
You could also write this in any language under the sun. You do not need and sort of Database backend as you would provide it with a seed date and it would do the rest for any month you picked in any year.

How it would work.......
Seed date = Known date of inspection
Month to look at
Take the difference in days from the first of that month and the seed date
DateDiff(d, seed Date, 1st of month 200X)
take the resulting figure mod 28
Add 28 - (DateDiff(days, seed Date, 1st of month 200X) % 28) to the first of the month to get the day you are looking for.
Color three days before and three days after.

That math may be slightly off as I am damn tired at the moment

But this is the general flow of the program. It is pretty easy. If you need any more help just let me know. Most languages have a decent date function, and if not it is pretty easy to write your own as every language has a UTC function.

Jkrohn

usslindstrom · August 24th, 2005, 02:06 AM

Holy crap! Thanks for the quick response!

And - You`re official WAY over my head. I did some programming with the orignal BASIC on an old Apple II when I was a kid, but nothing more than making the computer choose random numbers and putting together a choose-your-own adventure game. - Nothing too in depth.

If it wasn`t too much trouble, could somebody walk me through the process on this task? - As long as I`m not bothering anybody.

Rootstonian · August 24th, 2005, 10:13 AM

Anything like this help?

usslindstrom · August 24th, 2005, 07:29 PM

Actually, yes. For what I need. - But I already know how to do those kind of formulas from within Excel....

What I'm looking for, is for my millions of boss' that really don't know anything about computers - so they can graphically see the inspection cycle on a calendar in front of them.

Thanks for the suggestion though.

Rootstonian · August 25th, 2005, 08:22 AM

Ok, do you use Microsoft Outlook for e-mail? Use it's calendar feature to setup a recurring event; you can set it for 28 days. Not sure how to do the +/- 3 days though.

usslindstrom · August 30th, 2005, 09:41 PM

Yeah, I know about the Outlook Scheduling, but it`s pretty much imperative that I set the + / - 3 days also. Perferably color coded. Plus, I would need to have the option of changing the inspection base date on the fly, as we sometimes do the work as operations require.

Rootstonian · August 31st, 2005, 07:12 PM

Well, I'm out

Access is NOT my strong suit. Hopefully someone will stop in who is!

VHockey86 · August 31st, 2005, 07:28 PM

How exactly do you want your bosses to view this?

Seems to me like it might be best done through scripting (PHP perhaps) on a web server on the companys local network. That way you can throw a bookmark on their computers for them to view it. As far as recording the dates, you could do something as simple as what jkrohn has laid out above, or reading and outputting to a text file, or a mysql database installed on the same server would work pretty well.

Probably a better solution...but I dunno.

Perhaps a VisualBasic macro in an excel spreadsheet?
Not exactly sure how you could generate the calendars, but in generally, you could create a calendar out of Excel cells (on a different worksheet than the input stuff), and then have a little Macro button, color in the dates that you generate with whatever formula you have.

usslindstrom · September 1st, 2005, 02:12 AM

Yeah, basically anything is on the table for this idea. I was just a little familiar with access, but didn`t know if it was possible with that program...

If writing it in a programing language best suits this, then by all means I`m game... But I`m not to edumacated in the area of programming, and would need some guidance if it`s not too much trouble.

Here is kinda` a draft of what I would like the thing to do. I would like to have the Boss man just push a button on whatever month he/she wanted to look at, and all they would see is what inspections fell due in that particular one. Color coded if possible.