Find date difference 1

[BrianLe]

This is quiet long. I have a table (tblSchedule). It has fields for anticipated and actual dates related to the delivery, unloading, etc. of coal. I want to determine the "scheduling accuracy" of each phase.

tblSchedule
ScheduleID - autonum
TrainID - autonum
TrainPartID - autonum (1,2,3 or 4)
DateTypeID - text (1 or 2)
DateID - text (1,2,3 or 4)
ScheduleDate - date
TStamp - date (Time stamp)

tblDateTypeID
1 = Anticipated
2 = Actual

DateID
1 = Load
2 = Arrive
3 = Empty
4 = Remove

The date info can be considered to be in sets of DateTypeID and DateID, i.e.
1,1 = Anticipated Load
2,1 = Actual Load
1,2 = Anticiapted Arrive
2,2 = Actual Arrive
1,3 = Anticipated Empty
2,3 = Actual Empty
1,4 = Anticipated Remove
2,4 = Actual Remove

For each TrainID-TrainPartID set, I want to find the scheduling accuracy for each phase (Load, Arrive, Empty, Remove). The accuracy will be defined as follows:

LoadAcc = (Earliest(1,1)ScheduleDate - Earliest(1,1)TStamp) divided by ((2,1)SchduleDate - Earliest(1,1) TStamp)

ArriveAcc = (Earliest(1,2)ScheduleDate - Earliest(1,2)TStamp) divided by ((2,2)SchduleDate - Earliest(1,2)TStamp)

EmptyAcc = (Earliest(1,3)ScheduleDate - Earliest(1,3)TStamp) divided by ((2,3)SchduleDate - Earliest(1,3)TStamp)

RemoveAcc = (Earliest(1,4)ScheduleDate - Earliest(1,4)TStamp) divided by ((2,4)SchduleDate - Earliest(1,4)TStamp)

Example:

For a particular TrainID-TrainPartID

DateTypeID DateID ScheduleDate TStamp
1 1 10/21/07 10/19/07
1 1 10/22/07 10/20/07
2 1 10/23/07 10/23/07
1 2 10/26/07 10/23/07
2 2 10/26/07 10/26/07
1 3 10/29/07 10/26/07
1 3 10/30/07 10/29/07
2 3 10/31/07 10/31/07
1 4 11/1/07 10/31/07
1 4 11/2/07 11/1/07
2 4 11/3/07 11/3/07

Desired result

LoadAcc = (10/21 - 10/19) / (10/23 - 10/19) = 2/4 = .5

ArriveAcc = (10/26 - 10/23) / (10/26 - 10/23) = 3/3 = 1

EmptyAcc = (10/29 - 10/26) / (10/31 - 10/26) = 3/5 = .6

RemoveAcc = (11/1 - 10/31) / ( 11/3 - 10/31) = 1/3 = .33

All the phases may not be complete for the trains in the date range, or there may be no trains in the date range. the query should only determine accuracies for the completed phases of each train, i.e. there is an actual (DateTypeID =2)for the phase (load, arrive, empty or remove) for the TrainID-TrainPartID set.

I would like to be able to report on a selected range of ScheduleDate dates.

I have the following query to find the trains-parts that have a load DateID = 1.

SELECT tblSchedule.ScheduleID, tblSchedule.TrainID, tblSchedule.TrainPartID, tblSchedule.DateTypeID, tblSchedule.DateID, tblSchedule.ScheduleDate, tblSchedule.Notes, tblSchedule.TStamp
FROM tblSchedule
WHERE tblSchedule.DateID = "1"
ORDER BY TrainID, TrainPartID;

I anticipate using a form frmSchedulingAccuracy that will be used to select the start and end dates, and the phase (Load, Arrive, Empty, Remove).

From there I'm pretty much lost how to get the accuracy values.

Any suggestions?

Thanks,

Brian

 

[GingerR]

Hmmm....this was fun.

Ok, I have a few queries. PHV or someone else could probably do this in one query, but I can't so here it goes:

First query groups all the combinations of DateTypeID and DateID for each TrainID + TrainPartID. Also determines MIN TStamp and Schedule dates.

2nd query is based on the first one: if there are 8 of rows of data for a TrainID + TrainPartID, that means that all the phases are complete and this TrainID + TrainPartID should be reported.

First query (name it "CompletedPhasesDetail"):

SELECT tblSchedule.TrainID, tblSchedule.TrainPartID, tblDateID.DateID, tblDateTypeID.DateTypeID, Min(tblSchedule.TStamp) AS MinTStamp, Min(tblSchedule.ScheduleDate) AS MinScheduleDate, tblDateID.DateTask, tblDateTypeID.DateType
FROM (tblSchedule INNER JOIN tblDateTypeID ON tblSchedule.DateTypeID = tblDateTypeID.DateTypeID) INNER JOIN tblDateID ON tblSchedule.DateID = tblDateID.DateID
WHERE (((tblSchedule.TStamp) Is Not Null))
GROUP BY tblSchedule.TrainID, tblSchedule.TrainPartID, tblDateID.DateID, tblDateTypeID.DateTypeID, tblDateID.DateTask, tblDateTypeID.DateType;

2nd query (name it CompletedPhasesFinal):

SELECT CompletedPhasesDetail.TrainID, CompletedPhasesDetail.TrainPartID
FROM CompletedPhasesDetail
GROUP BY CompletedPhasesDetail.TrainID, CompletedPhasesDetail.TrainPartID
HAVING (((Count(CompletedPhasesDetail.TrainID))=8));

Then a third query which determines all the calculations using DLOOKUP(). Do you know of that function? It looks up values in tables/queries that you are not currently in. This looks long, but essentially there are four calculated controls which are pretty much the same, looking up the MinTStamp and MinScheduleDate for DateTypeID = X and DateID = Y:

SELECT CompletePhasesFinal.TrainID, CompletePhasesFinal.TrainPartID, (DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '1'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '1'"))/(DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '2' and DateID = '1'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '1'")) AS LoadAccuracy, (DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '2'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '2'"))/(DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '2' and DateID = '2'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '2'")) AS ArriveAccuracy, (DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '3'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '3'"))/(DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '2' and DateID = '3'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '3'")) AS EmptyAccuracy, (DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '4'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '4'"))/(DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '2' and DateID = '4'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '4'")) AS RemoveAccuracy
FROM CompletePhasesFinal;

You can paste these SQL statements into your SQL View of the queries, then look at the design view to see what I mean.

I didn't do the part where you wanted to enter a Scheduled date range into a form and hit a button to run the query because I didn't know what you meant by "range of schedule dates". You did such a great job explaining your structure, what you need, and some sample data!! If you give an example of a date range you'd enter and what you expect to return (if you can't figure it out yourself), we can help. I didn't know if you mean to filter the orig data with the date range, or find any/all TrainID + TrainPartID with ANY date in that range and then pick that Train to report on? not sure, but maybe you can figure it out now that all this other stuff is done. Let us know how you make out.

Ginger


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[BrianLe]

Ginger,

Have a star.

Your queries work great, minor errors in 3rd query - CompletePhases ... should be CompletedPhases....

They give me the completed trains (Actual Remove), which is just fine. I had originally thought that I'd want the accuracy for partially completed trains but there are generally few of them anyway. In my example, the RemoveAccuracy is actually 1/3.

I made a few changes to give me the TrainYear and TrainNo instead of just the TrainID.

For the date selection, I was thinking of selecting the trains that have MinScheduleDate between the selected dates.

To select the date range I've tried adding to the WHERE clause in the 1st query.

SELECT tblSchedule.TrainID, tblSchedule.TrainPartID, tblDatecode.DateID, tblDatetype.DateTypeID, tblTrain.TrainYear, tblTrain.TrainNo, Min(tblSchedule.TStamp) AS MinTStamp, Min(tblSchedule.ScheduleDate) AS MinScheduleDate, tblDatecode.DateTitle, tblDatetype.DateType
FROM (tblDatetype INNER JOIN (tblDatecode INNER JOIN tblSchedule ON tblDatecode.DateID = tblSchedule.DateID) ON tblDatetype.DateTypeID = tblSchedule.DateTypeID) INNER JOIN tblTrain ON tblSchedule.TrainID = tblTrain.TrainID
WHERE (((tblSchedule.TStamp) Is Not Null)) And DateValue(Min([tblSchedule].[ScheduleDate]) Between [Forms]![frmScheduleAccuracyReport]![tbxStartDate] And ([Forms]![frmScheduleAccuracyReport]![tbxEndDate]))
GROUP BY tblSchedule.TrainID, tblSchedule.TrainPartID, tblDatecode.DateID, tblDatetype.DateTypeID, tblDatecode.DateTitle, tblDatetype.DateType, tblTrain.TrainYear, tblTrain.TrainNo;

However, I get an error "Cannot aggregate on WHERE ...".

Any suggestions?

Thanks,

Brian

 

[GingerR]

I don't think it's going to work for you like that. Again, can you give examples? With your data in your first post, if your START DATE/END DATES were

10/21/07 - 10/26/07

would you expect your example above to be included in the results?

So are you expecting:

1) Trains that have ANY scheduled dates within the date range

or

2) Trains that have ALL scheduled dates within the date range?


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[BrianLe]

I would like all the trains with 2,4 (Actual-Remove) date between the start and end dates to be returned by the query.

Using my example, and your dates of 10/21/07 - 10/26/07, this train would not be included because the 2,4 date (tblSchedule.ScheduleDate) was 11/3/07.

If the selected Start/End dates were 10/26/07 - 11/3/07, the train would be returned.

Thanks,

Brian

 

[GingerR]

So did you try what I suggested? Make another query which will be your "first" query, used in the previous "first" query?

You'd make a query, go VIEW+TOTALS so it groups on TrainID + other unique ID; bring down the DateTypeID make it's criteria 2 and DateID make it's criteria 4; bring down ScheduleDate and make it WHERE instead of GROUP BY and make it's criteria

Between [Forms]![FormName]![txtStartDate] and [Forms]![FormName]![txtEndDate] (or whatever you are calling them). This should give you the ID fields (TrainID, TrainPartID, whatever) of only the items that have 2 and 4 scheduled between the dates on your form.

THen go to the first query above and put this query inside of it, join on the TrainID and TrainPartID (etc). That should do it.

So, obviously I'm not just writing this code for you. I could, but I won't. You should be able to figure this out with my semi-vague instructions. Let us know how you do.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[BrianLe]

Ginger,

Thanks. Got it.

Here's what I have that appears to work.

First query: qryScheduleAccuracy

SELECT TrainID, TrainPartID
FROM tblSchedule
WHERE (((tblSchedule.DateTypeID)="2") AND ((tblSchedule.DateID)="4")) And  (((DateValue([tblSchedule].[ScheduleDate])) Between [Forms]![frmScheduleAccuracyReport]![tbxStartDate] And ([Forms]![frmScheduleAccuracyReport]![tbxEndDate])))
ORDER BY tblSchedule.TrainID, tblSchedule.TrainPartID;

Second query: CompletedPhasesDetail

SELECT tblSchedule.TrainID, tblSchedule.TrainPartID, tblDatecode.DateID, tblDatetype.DateTypeID, tblTrain.TrainYear, tblTrain.TrainNo, Min(tblSchedule.TStamp) AS MinTStamp, Min(tblSchedule.ScheduleDate) AS MinScheduleDate, tblDatecode.DateTitle, tblDatetype.DateType
FROM tblDatetype INNER JOIN (tblDatecode INNER JOIN ((tblSchedule INNER JOIN tblTrain ON tblSchedule.TrainID = tblTrain.TrainID) INNER JOIN qryScheduleAccuracy ON tblTrain.TrainID = qryScheduleAccuracy.TrainID) ON tblDatecode.DateID = tblSchedule.DateID) ON tblDatetype.DateTypeID = tblSchedule.DateTypeID
WHERE (((tblSchedule.TStamp) Is Not Null))
GROUP BY tblSchedule.TrainID, tblSchedule.TrainPartID, tblDatecode.DateID, tblDatetype.DateTypeID, tblTrain.TrainYear, tblTrain.TrainNo, tblDatecode.DateTitle, tblDatetype.DateType;

Third query: CompletedPhasesFinal

ELECT CompletedPhasesDetail.TrainID, CompletedPhasesDetail.TrainPartID, CompletedPhasesDetail.TrainYear, CompletedPhasesDetail.TrainNo
FROM CompletedPhasesDetail
GROUP BY CompletedPhasesDetail.TrainID, CompletedPhasesDetail.TrainPartID, CompletedPhasesDetail.TrainYear, CompletedPhasesDetail.TrainNo
HAVING (((Count(CompletedPhasesDetail.TrainID))=8));

Forth query: ScheduleAccuracyReport

SELECT CompletedPhasesFinal.TrainID, CompletedPhasesFinal.TrainPartID, CompletedPhasesFinal.TrainYear, CompletedPhasesFinal.TrainNo, (DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '1'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '1'"))/(DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '2' and DateID = '1'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '1'")) AS LoadAccuracy, (DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '2'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '2'"))/(DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '2' and DateID = '2'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '2'")) AS ArriveAccuracy, (DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '3'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '3'"))/(DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '2' and DateID = '3'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '3'")) AS EmptyAccuracy, (DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '4'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '4'"))/(DLookUp("[MinScheduleDate]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '2' and DateID = '4'")-DLookUp("[MinTStamp]","CompletedPhasesDetail","TrainID = " & [TrainID] & " and TrainPartID = " & [TrainPartID] & " and DateTypeID = '1' and DateID = '4'")) AS RemoveAccuracy
FROM CompletedPhasesFinal;

Thanks again,

Brian

 

[GingerR]

No problem. It was fun.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html