Tasty Question (1 Viewer)

[daverskully]

Form created with buttons that prompt a query that opens a report based on specific criteria. I am looking to create a specific query with the details listed below.

Relevant Fields:
Department
ReportDate (recorded on the date when the report/record is entered)

I am looking to have a query created (or any method in which this can be done, always open to suggestions) so that it generates reports, based on information derived from one table, for all of the last entered records for each specific department. I am looking to have reports created for the last reporting date of each department (100 of them, currently) so that 100 of the latest records are showing (one record for each department based on last date).

It would be amazing if this can be done, I've been having a great deal of trouble with it. If you require any further information please ask, I will usually respond very quickly. Thank you in advance.

 

[jversiz]

Tasty McTasterson

Hi,

First of all, you need an auto-number field (let's call this field 'RecordID') in the table you are pointing the report to. On your report, create a text box. In the control source for this text box you can use the 'DMax' function to show the highest record within a given domain. In other words, you want to show the maximum RecordID for a given department. You can play with the exact syntax, but it's something like...=DMax("[RecordID]", "Department").

Now for the rest of the data on the report, the 'DMax' function will probably not work, especially on text fields, but now that you have the max. RecordID that you are looking for, the rest of the text boxes on your report can use the 'DLookup' function to find the exact data that matches the corresponding RecordID.

I would suggest doing some research on the exact syntax of both the 'DLookup' and 'DMax' functions if you are not familiar with them. Also, depending on how complex your report is will obviously depend on how much time the report takes to develop. Writing that many 'Dlookup' functions will probably take some time.


Best Regards,

James C.
EDI Analyst

 

[jversiz]

Try again...

Sorry that 'DMax' function won't quite work, but it's still easy to do...

1. Make a query (let's call it qryGroupByDepartment)
2. The query should have two fields in it: The RecordID (from my previous post) and the Department
3. The total field for the RecordID should be set to 'Max'
4. The total field for the DepartmentNumber should be set to 'Group By'
5. Make your report using qryGroupByDepartment as it's source
6. Put the 'MaxOfRecordID' field in the details section of the report.
7. Put the DepartmentNumber field in the details section of the report.
8. All other fields on the report will use the 'Dlookup' function, having criteria for the 'Dlookup' be the RecordID field on your report.

Here is a 'Dlookup' I did showing the product description for the maximum of an item number field with a given brand..

=DLookUp("[Product Name]","tblISF","[Item #] = Reports!Report1![MaxOfItem #]")

This definitely works!

 

[daverskully]

Works great, can't thank you enough jversiz!