Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Grouping fields in report header

    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?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Jul 2013
    Location
    Murphy, NC
    Posts
    66
    Thanks
    0
    Thanked 8 Times in 8 Posts
    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
    Last edited by DavidHLevin; 2015-06-08 at 09:54.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    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?

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    Murgatroyd (2015-06-10)

  7. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. Yes, that works fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •