Report need to show past and upcoming date

[doucet1963]

I have a report generated based on the query from a table named audit. In the table each row (audit) has a completed and a forecasted date. Before an audit is performed, the forecasted date will have a value but not the completed until the audit is completed.

The problem that I have is that the report does not show on the same line the organization's last audit and next forecasted audit, because they do not have the same auditID number. Also there may be more than one completed audit for the same organization but I need to see the last one.

As you'll see on the attachement, the report repeats the audits twice or more with the completed and forecasted date almost the same because they relate to the same auditID.

I am trying to get the last audit in a column and in the one beside the upcoming audit. Example"

Completed / Next
16 Feb 2015 / 19 may 2017

 

[sneuberg]

If you are trying to get data in another record typical this is done with a subquery. You can check out Get the value in another record in this Web page. Sometime this can also be done with Dmax.

If you can be more specific I help you set this up as it can be trick. An example of more specific would be:

I need the date_completed early than the date_complete in the any given record where the Official Name and the Specific Detail are the same.

If there can be earlier dates that are the same then they need to be distinguished by some other field. Sometime an autonumber is used for this.

 

[doucet1963]

Thanks Steve.

Tell me what I need and I will do it. Roughly, I have several organizations that is made of many sub-organizations. There always will be a forecasted audit for each sub-organization, and the completed date field will be updated once the task is completed.

The report needs to show on the same line:
1. the name of the organization,
2. the name of the sub-organization,
3. the type of audit,
4. the date of the last completed audit, and
5. finally the next audit.

I added the basic fields of the forms that I used in the example. Hopefully there is a simple solution.

 

[sneuberg]

I still don't understand what you are looking for. I have some guesses but I'm not sure. To help my understanding please give me an example table of the five fields of data in your last post for about four records from the data in the database you uploaded.

This editor doesn't display table data nicely so I would be better if you created this table in Word or Excel and attached it to your post.

 

[doucet1963]

the attached spreadsheet is made of fields that are required in the report, The first organization shows three audits, but the report should be able to capture the date of the last completed audit and the date of the next audit.

the second organization shows two rows, one completed and one forecasted while the last one is like the first one, three rows.

 

[sneuberg]

It's time for me to go to bed so I'll continue to work on this tomorrow. In the meantime you can see where I'm at so far by looking at the qryLastDateCompleted in the attached database. What's added is the expression LastDateCompleted which is

LastDateCompleted: IIf(IsNull([date_completed]),DMax("[date_completed]","[qryLastDateCompleted]","[Geographic_Location] = '" & [Geographic_Location] & "' And [Official_Name] = '" & [Official_Name] & "'"),[date_completed])

I've haven't compared yet this with the table you attached which by the way was very helpful

 

[sneuberg]

I've compare the output of the expression I've put together with the spreadsheet you provided and see we are not there yet. For any set of records grouped by Official Name and Geographic Location the expression retrieves the latest Date Completed and displays that in the record where Date Completed is null. The expression does not consider the AuditType which works like your spreadsheet for the Geographic Location camel where the Dates Complete are 10-May-16 and 2-Jan-17 for Audit Types Initial and Comprehensive and null Date Complete has Audit Type Surveillance the answer is 2-Jan-17 for both the spreadsheet and expression. This would tell me that the Audit Type isn't considered in the selection of the date. On the other hand if you use this logic for the Geographic Location Green Wood which has Dates Completed of 18-May-16 and 10-Jun-15 and Audit Types of Comprehensive and Initial the answer should be the later date of 18-May-16 and not the 10-Jun-15 date shown in the spreadsheet.

So it appears I don't understand the method to determine these dates. Please explain to me how you would do this this pencil and paper, e.g., "I would group the records by this and that and than scan this column for the latest/earliest date,...etc"

 

[doucet1963]

Steve,

Your understanding is good, I made a mistake in the excel for the first organization, sorry about it. I corrected it in this attachement. the Report should be able to provide the last audit date and the upcoming for an organization / sub-Organization.

The audit type is not a requirement and should not drive the report but it is good to view the type of audit in the report, it determine the level of effort that must be given.

I would like to help you but when I looked at your draft Qry you lost me.

Thanks for spending some time on this.

 

[sneuberg]

I've attached another version in which I changed the qryLastDateCompleted to look like the spreadsheet. Also I added qryLastDateCompletedReport which is the same query just filtered for the records where Date_Complete is null to match the rows in the spreadsheet marked as "Report should show". This looks right now. Please confirm this.

If you have problems putting this expression in the query you need it to be in let me know.

 

[doucet1963]

Steve,

The solution that you developed works great. Once I embedded the queries in the database and linked the fields to the report wow it makes wonder. This is exactly what I was looking for. Thanks.