Need help figuring this out (1 Viewer)

[2wistd]

I have one table with training dates, names, courses etc in it. Another has dates when training should be completed, which is different for everyone. Training has to be done 1 year prior to entering their time frame.

Training Table:
Name, LastCompletionDate, CourseName
 
TimingTable:
Identifier, Start Date, End Date

Currently I have a query that shows

LastCompleteDate criteria "< Date() - 305"

which shows everyone who is within 60 days of being due. and another criteria under CourseName for each course.

How do I write the query to give me the information I need?

 

[plog]

Can you post sample starting data from both tables, and then what data should be produced from your query based on that sample starting data?

 

[2wistd]

Yes, I just need to delete the data and put some fake data in for now.

 

[2wistd]

Wierd, I removed pretty much all the data/tables, queries, and reports...but the file is still 31mb, same as the full file.

 

[plog]

Compact and repair.

 

[2wistd]

okay, database is attached, I hope I didn't strip it down too much.

 

[plog]

Back to my initial post:

Can you post sample starting data from both tables, and then what data should be produced from your query based on that sample starting data?

I have all the data, however I don't know what fields nor tables you were referencing. Nor do I have what data you expect this query to produce. Just tell me what fields/tables are relevant and what data the query should produce based on the sample data you have in there.

 

[2wistd]

OKay, I'm looking for a report that shows 60 days prior to when someone is due for a course. The Xband is certain periods open up and they need to have the courses complete a year ahead of time.

So I would like to see Worker1 has Xband of X1 which has a start date of 1/1/15, so his courses are due 1/1/14. Course1 he completed 11/14/2013, meaning he is overdue for that. Course2 was completed 3/1/2014 so that is fine and doesn't need to be on the report.

Somehow I have to relate the Xband dates to the Xband selection of the user within the report. I hope this makes sense.


For a bonus, i'd like that information visible on the 'maintable' form. So Worker1 can see his dates of completion for all his courses.

 

[plog]

Not looking for another explanation. I want 2 sets of data: starting data then what data should be produced based on that initial data.

 

[2wistd]

Data in table "permanentparty":
persName:                   persRank                  
Worker1                      SSgt
Worker2                      TSgt
Worker3                      SrA
Worker4                      LtCol
Worker5                      Col
Worker6                      Lt
Worker7                      Capt
Worker8                      SSgt
Worker9                      SrA
Worker10                    SSgt
Worker11                    Capt
Worker12                    Capt
 
Data in permpartyextra
persName:                   xband
Worker1                      X1
Worker2                      X1
Worker3                      X2
Worker4                      X4
Worker5                      X3
Worker6                      X5
Worker7                      X4
Worker8                      X2
Worker9                      X3
Worker10                    x4
Worker11                    X1
Worker12                    X5
 
data in Xband:
Xband                       StartDate
X1                            1/1/2015
X2                            6/1/2015
X3                            1/1/2014
X4                            6/1/2014
X5                            6/1/2013
 
Data inside CombinedTraining
courseName              persName            LastCompleteDate           Rank
Course1                   Worker1              11/14/2013                    SSgt
Course2                   Worker1              3/1/2014                       SSgt
Course3                   Worker1              1/15/2014                      SSgt
Course4                   Worker1              10/15/2013                    SSgt
Course1                   Worker2              12/14/2013                    SSgt
Course2                   Worker2              1/1/2012                       SSgt
Course3                   Worker2              2/15/2014                      SSgt
Course4                   Worker2              10/15/2012                    SSgt
 
etc

The final result I want:

Report: 
Worker1 in Xband X1       
    Courses due:    Course1      due in   "78 days"
                         Course4      due in   "47 days"
 
Worker2 in Xband X1
    Courses due:    Course1      due in "16 days"
                         Course2      due in "770 days"
                         Course3      due in "412 days"
 
etc

 

[plog]

In your results you have Worker2 associated with X2, that connection is not possible with the sample data you posted above.

Also, I am unable to determine how 45 days is calculated for Worker1, Course2. What two dates are used in that calculation?

 

[2wistd]

updated to show more accurate information!

 

[plog]

Walk me through the calculation of 78 days for Worker1 Course1. Also explain why Worker1 Course2 isnt' to show up.