Display a Due date based on other tables

[tri.stand]

Hi All,

I have two tables; Tasks and Maintenance Log.



The user uses the Maintenance Log to input the Task completed (which i looked up from the Task table) and then inputs the person who completed it and also the date it was done.

I would like to run another table(?) which looks at the newest record for completed task (example) "Clean Floor" and then displays a Next Due date based on the last date completed plus the Interval time for that task also in the Tasks Table (Eg, 2; this would mean weeks).

I'm hoping to then run a popup once all complete that shows any "Task Due" within a week or days of the due date upon opening.

Is this possible? Also possible within Access itself as I have no clue how to use the coding side or macros

Thank You for any help

[orange]

If you have people/employees that do tasks that are repeated at some known frequency, then you probably need more than 2 tables.

tblPeople
tblTask
tblAssignedTask

in tblTask you may have info like
TaskName
TaskFrequency
TaskDescription

in
tblAssignedTask
AssignedTaskID PK
PersonID (FK to tblPeople)
TaskID (FK to tblTask)
AssignmentDate
AssignmentStatus (assigned, completed...)

Just some ideas for consideration. Good luck.

[tri.stand]

I've attached what I have at the moment. I'm not sure its completely right, but with limited knowledge I've been trying to figure it out my self.

I have attached what I have come up with so far. I have deleted a few tables due to file size. but the basics are there.

I think I am on the right path?

[orange]

Hmmm???
You have a few hidden entities that should be explicitly identified in my view.
TaskType, TaskArea, Machine...

For ease of maintenance and frustration you should not name field or any objects with embedded spaces in the name. You should also avoid special characters such as "()" in names. Better to use Underscore "_" .
Each table should represent a single thing/entity. Your Task table includes the concepts of Type and Area.

Consider user "Smokey Butts" is assigned the TaskType "Grease" the "Top Table" in the "Forming Station" TaskArea. It isn't clear from the info just what a Machine is --perhaps these have local names in your environment. It will help you with your design if you start with a description of the things and processes that you are trying to support with this database.

I recommend you identify each of the things in your "business" and attempt to normalize the proposed database. If you spend about 45 minutes working though 1 or 2 of the tutorials from RogersAccessLibrary mentioned in this link. You will experience a process that will teach you about database design that can be used with any database.
If you also look at the "stump the model" link in that referenced material, you can test your design before getting too deep into physical database. In effect, you will have a "blueprint" for your design.

Getting you tables and relationships designed and tested with some data from your environment on paper will be useful before jumping to physical Access.

There are many articles/tutorials related to Database Planning and Design in the link I provided above.


The TaskTypes I see in your database are:

type
Clean
Debris Check
Grease
Grease Check
Oil
Oil Check

The Areas are:

area
Chains
Cutter Station
Forming Station
Heaters
Hole Punch Station
Other
Reel Stand
Scrap Station
Stacker Station
Vacuum Pumps

Good luck with your project.

[ssanfu]

Also note that "TYPE" is a reserved word in Access and shouldn't be used for object names. Plus it is not very descriptive - "TYPE" of What??

You should not use "Look Up FIELDS" (different that look up tables) in your table designs. See The Evils of Lookup Fields

[CarlettoFed]

To be able to help you correctly, you must attach a file with all the tables.