Grouping fields in report header

Topic

New Reply

I have Access 2010 as a front end linked to a SQL database back end. I have a report with two tables (“Orders”, “Notes”) joined on a common field [OrderID].

Each record in the Orders table has four corresponding records in the Notes table (for four types of note). Each set of four records in the Notes table has the same value in a date/time field [NotesDateTime] that is set when each set of four note records is created.

The report lists fields from each record in the Order table as a header line, followed by fields from the corresponding four records in the Notes table as four detail lines. So far, so good.

However, I want to show the date/time value from each set of four records in the Notes table not repeated four times in the four detail lines (i.e., along with other fields on the four note records) but once in the header line (i.e., along with fields from the Order table). Is this possible?

Reply | Quote

Replies

You might try creating a SQL Server view of the Order table with a join to the Notes table and include only the NotesDateTime field, but use the DISTINCT keyword, and use that as the data source for the header line. If you are not comfortable with that approach, you could make the data source for the header line a query joining one of the specific notes types. That however presumes that you will always have all four notes for every Orders table record.

Reply | Quote

Hi Murgatroyd,
From your description, it seems that NotesDateTime is dependent solely on OrderID. Therefore, even though NotesDateTime is “about” the notes rather than the order, I’d be inclined to put it in the Orders table rather than the Notes table, which not coincidentally would address the issue you’re having with where that information appears on the report.

Dave

Reply | Quote

Thanks for your replies. Every order record always has four corresponding notes records, so Wendell’s suggestion of getting a notes date/time value from one of the four notes records for each order record, and putting that in a field in the report header line, sounds like it should work.

The notes date/time value for an order record where [OrderID] = 123 can be represented by the [NotesDateTime] field on the corresponding notes record where the joining field [OrderID] = the same, and the note ID field [NoteID] = 1 (the four notes records have the same [OrderID] but [NoteID] = 1, 2, 3, or 4].

How can I set up a “Notes date/time” field on the report header line to retrieve this from the first corresponding notes record for each order record?

Reply | Quote

If all 4 notes records have the same value, then this seemingly would be solved by just putting a control in the header where its controlsource is that NoteDateTime field. When Access prints the header, it is looking at the first record in the group, so that’s what it will display there.

Reply | Quote

Thanks for your reply. Yes, that works fine.

Reply | Quote