How to set up database to show history of information and test frequency

[Diamond]

I am starting a database in Access 2003. I would like it to show a history of a particular device; I want to be able to edit and add a device, and have it connected to an individual account and show the manufacturer. There is one device with many histories, and one account may have many devices. I would like to show the history for each device on one account.



One device needs to be tested. The choices are annual, bi-annual, and quarterly. How do I set up the database to calculate a next test date for each of the choices of annual, bi-annual, and quarterly? Should the choices be check boxes or a different control? I am going to have to query these dates eventually to send out letters.

I am still trying to map the database out on paper. Any help is appreciated.

[Stingaway]

Couple of ways to go about it - any and all might fit your needs. One way - you can set up a query that references the date, then use that query to give a number of days until the next test date. I have done this in the past by creating a table with terms - for example "Term" would be one field containing annual, bi-annual, quarterly for example, and term duration would be another field containing the time (in this case in days) - 365, 180, 120 etc...

By looking at the start date or last test date, and then comparing that with it's term and the current date (easily done in a query by using the Now() function), you can find and present a date for the next test date.

You can add date fields by using functions such as Now() Example: Now()+180 = 180 days from now, Now() - 7 = 7 days ago, etc. Look up the datediff function to get the amount of days between two dates, etc. All of this can be done IN the query, you don't have to use VBA for it at all..

Hope it helps.

[orange]

Here is a process with a sample scenario that leads you through the steps to design your tables and relationships.
http://www.rogersaccesslibrary.com/T...lationship.zip

Good luck.