[X]My Assistant

[3dzer0]

I have been fighting with this one for a couple of days, and I just can't figure exactly how to accomplish it.

I have a Projects Table (Image 1) and a Tasks Table (Image 2). Under each project there are tasks that are associated with them and for each task there is a start date and a deadline date.

What I would like to show on the Project List (Image 3)page what is the next task deadline date that is upcoming. I was able to do a Select statement but it is showing all of the task dates for every project in a combo list. I also did a different statement that is showing only the first record of the task table but it is the same for each project.

So I decided to do a Query(Image 4) which is showing the each task Due_Date with the corresponding Project Name ...

Ultimately I would like to be able to show the Due_Date and use conditional formatting with color to alert the user that a milestone due_date is within 5 days of today ...

Any thoughts or ideas on how I would be able to accomplish this?

Images of the table attached ... Thank you all !
This post has been edited by 3dzer0: Feb 11 2019, 01:26 PM

Attached File(s)

 Image_1.JPG ( 31.68K )Number of downloads: 2
 Image_2.JPG ( 83.22K )Number of downloads: 3
 Image_3.JPG ( 46.6K )Number of downloads: 2
 Image_4.JPG ( 36.33K )Number of downloads: 2

 

[mike60smart]

Hi

I am a little concerned about your table structure(s).

Your Projects table should have ProjectID as the Primary Key - Autonumber.

Your related Tasks table should have TaskID as the Primary Key - Autonumber and the related Foreign Key you need ProjectID - Number.

You would then link ProjectID to ProjectID and set Referential Integrity.

[3dzer0]

Under the Projects Table and the Tasks Table each has an ID that are Primary Keys .... and Auto numbered. I'm not sure what Referential Integrity is though...

[3dzer0]

I'm sorry I do understand what you are talking about.. you mean the relationships.

Here is what I have in the image below.

I think it is set up correctly ... Maybe ..

Attached File(s)

 Image_5.JPG ( 81.28K )Number of downloads: 4

 

[bakersburg9]

I'm open to hear what others have to say, but as far as design, I would strongly recommend renaming all the "ID" fields to "ProjectID," "TaskID," "EmployeeID," etc.

Edit: Let me clarify - I'm not saying you shouldn't have ANY fields named "ID," definitely the Projects table - and any tasks /work completed link to the original projects...


This post has been edited by bakersburg9: Feb 11 2019, 02:08 PM

Attached File(s)

 ProjectID.jpg ( 38.58K )Number of downloads: 0

 

[3dzer0]

I honestly think that is a good idea and something that I will most certainly will do.

Thanks !