ontime /late report from audit latest date (1 Viewer)

[rainbows]

hi,

I have a report that shows me if a target date was completed ontime or not to the dateclosed field and how many days late or early
this works ok

but I discovered that the administrator of the system could change the target date in the table without being noticed

I have with the help of others in this forum created a audit log for the target date

but what I need is now to tell a query to look at the record ID and find the first fieldvalueold but the last fieldValue


example recordID 185 is latest target date 29/01 but the first value was 23/01
how can I record that it was 11 days late not 5 to the orginal target date



ARNO FieldValue FieldValueOld Dateclosed
185 29/01/2019 20/01/2019 03/02/2019
185 20/01/2019 23/01/2019 03/02/2019
209 22/01/2019 20/01/2019 08/01/2019
209 20/01/2019 31/01/2019 08/01/2019

thanks steve

 

[June7]

That is a very simple and limited sample dataset. Always be only 2 records per recordID? Consider:

SELECT Table1.ARNO, Max(DateClosed)-Max(Table1.FieldValueOld) AS DaysLate
FROM Table1
GROUP BY Table1.ARNO;

 

[rainbows]

made a mistake when paosting my first post but the ARNO was actually Recordid
so I copied this in my query and got an error message

SELECT RecordID, Max(FieldValue)-Min(FieldValueOld) AS DifOfFieldValue
FROM tbl_audit_log
GROUP BY RecordID

now I have look at what you are I can see all the information I gave is not enough as

the min and max is not correct it is on the first entry of the oldvalue againt the last entry of the new value this is the ID field number (auto number )

sorry for the confusion

ID RecordID FieldName FieldValue FieldValueOld
5 185 targetDate 02/02/2019 16/01/2019
1 185 targetDate 16/01/2019 28/01/2019
3 209 targetDate 10/01/2019 18/01/2019
4 216 targetDate 13/01/2019 16/01/2019
2 216 targetDate 16/01/2019 22/01/2019




steve

 

[bastanu]

Create a query based on your audit table that will give you the FieldValueOld for the MIN(ID) - I assume that is an autonumber so you want the first entry for each ARNO or RecordID, then join that in your other query that calculates the elapsed days. Please note that I would add it as a left outer join and use a formula that would use the current value if the target date has not been modified there for the audit trail entry is missing):
ElapsedDays:[DateClosed]-Nz([FieldValueOldForMinIDfromAuditTableByRecordID],[TargetDate]).

Cheers,
Vlad

 

[bastanu]

I think the OP wants the first value ever entered (original) in the TargetDate field, not the Max or Min.

 

[rainbows]

HI ,
I have attached the sample database for you to look at

you can see the last target date for (ARNO 184) IS 10/02/2019 but the first target date was 11/01/2019

this means the original target date was late by 2 days

before I introduced the audit trail it would have shown it was ontime which is not the result I want

how do I get the query to shown me the first target date was late to the dateclosed


thanks
steve

 

[essaytee]

Some samples, that are included in your db that I've attached

SELECT tbl_Audit_Log.RecordID, Min(tbl_Audit_Log.ID) AS MinOfID, First(tbl_Audit_Log.FieldValueOld) AS FirstOfFieldValueOld
FROM tbl_Audit_Log
GROUP BY tbl_Audit_Log.RecordID;

which leads to

SELECT Issues.ARNO, Issues.targetDate, qry_01_FirstValue.FirstOfFieldValueOld, DateDiff("d",[targetDate],[FirstOfFieldValueOld]) AS Diff
FROM Issues INNER JOIN qry_01_FirstValue ON Issues.ARNO = qry_01_FirstValue.RecordID;

At least you can fiddle with this and adapt it to your needs (probably improve upon).

Note: The reason for the

Min(tbl_Audit_Log.ID) AS MinOfID

is that it's an Autonumber field, the first entry entered will be the field value you are interested in, always. Without reading further into the docs for Access, I'm not sure precisely what 'First' value means. If it means what I think it means then the Min of LogID is redundant. You'll need to get a greater sampling of data in order to check and test for yourself.

 

[June7]

You probably read post 2 before I edited it. Might review again.

 

[bastanu]

Sorry essaytee, but wasn't that what I suggested in post # 4?
Anyway, please have a look at attached, using First is not always reliable.

Cheers,
Vlad

 

[essaytee]

Sorry essaytee, but wasn't that what I suggested in post # 4?
Anyway, please have a look at attached, using First is not always reliable.

Cheers,
Vlad

Yes, you are right, I should not speed read prior posts.

 

[rainbows]

thanks you

this is the data I have but I think it is incorrect I have tried to redo it without any joy

should the elapsed days be 30?

the first target date is the 11/01 and the date closed is the 13/01




ARNO targetDate FirstOfFieldValueOld Diff Dateclosed ElapsedDays
184 10/02/2019 11/01/2019 -2 13/01/2019 -28



thanks steve

 

[essaytee]

thanks you

this is the data I have but I think it is incorrect I have tried to redo it without any joy

should the elapsed days be 30?

the first target date is the 11/01 and the date closed is the 13/01

ARNO targetDate FirstOfFieldValueOld Diff Dateclosed ElapsedDays
184 10/02/2019 11/01/2019 -2 13/01/2019 -28

thanks steve

Ok, I used the wrong date field in the query, all you have to do is substitute [targetdate] with [dateclosed] and you will get the correct answer as 2 days difference, when referring to Record 184.

SELECT Issues.ARNO, Issues.Dateclosed, qry_01_FirstValue.FirstOfFieldValueOld, DateDiff("d",[Dateclosed],[FirstOfFieldValueOld]) AS Diff
FROM Issues INNER JOIN qry_01_FirstValue ON Issues.ARNO = qry_01_FirstValue.RecordID;

 

[rainbows]

hi

thank you

but how do we get it to say if the "FirstOfFieldValueOld" is null then targetdate -dateclosed

this would give me a result for all dateclosed

thanks steve

 

[bastanu]

No worries Steve!
rainbows, have you had a look at the file I uploaded? It deals with exactly your latest question (as is my post #4). I use dateclosed-nz(OriginalTargetDate,TargetDate) instead of datediff function but you should get the same result.

Cheers,
Vlad

 

[rainbows]

hi,

this is the result I am getting for 4 daysearly is showing an error arno no 184 and 185 is showing an error

what I need is for all feilds to give me a result weather early or late
it should look at the original targetdate if null then calculate targetdate-dateclosed
or if not null then orginaltargetdate -dateclosed



ARNO targetDate OriginalTargetDate Dateclosed DaysEarly
184 10/02/2019 11/01/2019 13/01/2019
185 31/01/2019 31/01/2019 15/02/2019
186 19/04/2017 19/04/2017 0
197 31/05/2017 25/05/2017 -6


thanks steve

 

[bastanu]

In the sample you provided ARNO 184 and 185 are not Closed but Active and they are missing the DateClosed therefore the error. In my query I added extra criteria to only include the records that are closed and have a DateClosed date.
Are you using this query or something else?

The formula I used is:

DaysEarly: [DateClosed]-Nz([OriginalTargetDate],[targetDate])

but you can switch them around if you wish:

DaysEarly: Nz([OriginalTargetDate],[targetDate])-[DateClosed]

The Nz function is similar to what you are asking:
DaysEarly: iif(isnull([OriginalTargetDate]),[targetDate],[OriginalTargetDate])-[DateClosed]

Cheers,
Vlad

 

[essaytee]

hi,

this is the result I am getting for 4 daysearly is showing an error arno no 184 and 185 is showing an error

what I need is for all feilds to give me a result weather early or late
it should look at the original targetdate if null then calculate targetdate-dateclosed
or if not null then orginaltargetdate -dateclosed



ARNO targetDate OriginalTargetDate Dateclosed DaysEarly
184 10/02/2019 11/01/2019 13/01/2019
185 31/01/2019 31/01/2019 15/02/2019
186 19/04/2017 19/04/2017 0
197 31/05/2017 25/05/2017 -6


thanks steve

I assume from this point forward every time an entry is created, an entry will also be created in your audit table for the TargetDate. If that is the case, then there would be no need to check for an empty or null TargetDate in the Audit table and be referring back to the Issues Table.

Assuming an Audit entry is created every time there is a new record and change in the Target Date, there is no need to also include the old value within the same Audit entry, that would already be present in the previous entry.

Copy the existing records from the issues table, that is, the TargetDate, to your new Audit table, at this point there would be a one-to-one type relationship, as you do not know, at this point, if any of the existing records have in fact changed. From this point forward, you will know.

Following on from the above, the only niggling issue is the DateClosed field, if that is empty. This can be resolved with the IIF function. If the DateClosed field is empty then use Date() in the calculation in lieu of the DateClosed field.

First query sql:

SELECT tbl_Audit_Log.RecordID, Min(tbl_Audit_Log.ID) AS MinOfID, First(tbl_Audit_Log.FieldValueOld) AS [TargetDate-First]
FROM tbl_Audit_Log
GROUP BY tbl_Audit_Log.RecordID;

Second query sql:

SELECT Issues.ARNO, Issues.Dateclosed, qry_01_FirstValue.[TargetDate-First], IIf(IsNull([DateClosed]),DateDiff("d",Date(),[TargetDate-First]),DateDiff("d",[Dateclosed],[TargetDate-First])) AS [Days-Diff]
FROM Issues LEFT JOIN qry_01_FirstValue ON Issues.ARNO = qry_01_FirstValue.RecordID;

Running the above query gives me this result:

You'll note that I've included all records from the Issues Table, but if the Audit table is populated with data as mentioned above, only include records from the Audit table.

 

[rainbows]

hi Bastanu

really appreciate your help, I have attached the file again you will see I have now a query called steve
you can see the record 184 has 2 in the diff column the other 2 columns have 28 , I would like the -2 that is in the diff column to go where the 28 is or all the other numbers going in the diff column


sorry to be a pain

thanks steve

 

[bastanu]

rainbows (steve), you are working with the file uploaded by essaytee (also steve?) and you are trying my formulas... Can you please download the file I uploaded in post # 9 and have a look at that?

Cheers,
Vlad

 

[rainbows]

bastanu

below is a screen shot of your post 9

it errors on all the original target dates


ok

1. the original target date is enterd by the user .if this is not changes in the audit log form. it will not register on the audit log , ( no need to )
therefore I can determined the on time /late from date closed if a date closed is not entered then it will just count the days as from date()
when it is closed it will tell me late or early

if the administrator changes the target date in the audit log form it will record that in the table audit log

so I want all the records made in table issues and audit to be shown . like post 12 weather open all closed and weather date closed completed or not

and the results are in one column .


eassytee

you sample is 95% there but I need all the records from the targetdates from table issue also in that query and show me if late or early also in column day-diff

but don't look at the record target in target date ( from table issues) if there is a date in target date first. thefore record 197 should show 6

thanks

steve






ARNOtargetDate OriginalTargetDateDateclosedDaysEarly
180 21/07/2017 21/07/2017 0
181 25/05/2017 25/05/2017 0
182 19/04/2017 19/04/2017 0
183 25/05/2017 25/05/2017 0
184 10/02/201911/01/2019 13/01/2019
185 31/01/201931/01/2019
186 19/04/2017 19/04/2017 0
187 21/07/2017 21/07/2017 0
188 24/07/2017 24/07/2017 0
189 04/08/2017 04/08/2017 0
190 03/05/2017 03/05/2017 0
191 25/05/2017 25/05/2017 0
192 04/05/2017 04/05/2017 0
193 08/06/2017 08/06/2017 0
194 03/02/2017 03/02/2017 0
195 24/07/2017 24/07/2017 0
196 24/07/2017 24/07/2017 0
197 31/05/2017 25/05/2017 -6
198 21/07/2017 21/07/2017 0
199 30/08/2017 30/08/2017 0
200 20/07/2017 20/07/2017 0
201 30/08/2017 30/08/2017 0
202 27/05/2017 26/05/2017 -1
203 25/08/2017 20/07/2017 -36
204 30/08/2017 30/08/2017 0
205 11/07/2018 02/10/2018 83
206 25/05/2017 25/05/2017 0
207 03/08/2017 03/08/2017 0
208 25/05/2017 25/05/2017 0
209 26/02/2019 28/02/2019
210 31/08/2017 02/08/2017 -29