Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    pbDudley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39

    Create Report Header with unique values but with some from a many to one

    Hello
    I am creating a report. The main header report page is tied to a table where none of the records repeat, so I can keep it all on one page for the header.
    But a part of the header would be listing values from a table that has a relationship with the unique records but can have more than one record
    So for example:
    PLAN: N-1234, district, name, etc, records that are only once in the table with the unique records.

    Then the next table can have
    N-1234, Tree1, color,
    N-1234, Tree2, color

    Or more records,

    I'm trying to add this Tree1 and Tree 2 to the header but the header will become 2 pages now when I do this.. Is there a way to force it to one page, like a stacked table/columns on the header page?


    Or does the Tree1 and Tree2 have to be stored in one field to make this work.

    In the past the header page I created were all of the unique records so I didn't have this issue.
    Then the body of the report, one page would be tree1 with its records and then page 2, Tree 2 with its records.
    I'm being asked to have a field on the header that would list these (tree) values either as column type or separated by commas, tree1, tree2. But I would still have the main report with the the pages for each of these
    Thanks

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can try a subreport or use one of the available concatenation function (like this one http://www.accessmvp.com/thedbguy/co...itle=simplecsv).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    pbDudley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    I think I got it with a sub report on the report and linking it with the plan ID
    Thought I did that prior but it appears to be working now

    Or maybe that doesnt work, somehow i have to group or I see online it states to set: Force new page to none but it's already set to none

  4. #4
    pbDudley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    Ok, Can the concatenation be done in a query window using the build?

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not really but is is not that hard to do, just follow the example in the link or show us the table here and explain what fields you need.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    pbDudley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    Report_Example.zipReport_Example.zip
    Here is a sample with an image
    So 2 reports, N-1234 is shown in the image,
    The trees would be a seperate report, for each type

    But the header needs a field that lists the tree types as in the image example

    Its a very simplified version but I dont know how to write the code to concatenate that into one field

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Thanks for the file, not sure which report is the one you want as none seem to have a report header as your title suggest Do you mean the Plan Group header?

    Review the UniqueRecord updated report to see if that is what you need.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    pbDudley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    No sorry I did that very quick
    Rprt_TREE_Unique_Records
    This has both plans on it though, I was trying to get it where each plan is its own record, I have the one I'm working on set up that way by using the Force New Page on plan. I forgot to do that on this example. Then another report would do the same thing, plan, but each page would be the plan, tree_1 info, then page 2, plan tree_2, etc
    But it would be the plan: N-1234,,
    then tree types for that plan. So a field or query that shows the trees in the plan on the header

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you had a chance to look at the updated file?

  10. #10
    pbDudley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    Im looking at it now trying to figure it out

  11. #11
    pbDudley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    I see that you created the txt field: TreesInPlan
    I'm not seeing how it ties to the code in the module to where it knows to use the PLANT field.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You need to add the function from module1 into a standard module of your actual application then add the TreesForPlan calculated field to the report's a record source.
    Cheers,

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Look at report's record source.

  14. #14
    pbDudley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    TreesInPlan_Expression.zip

    So the TreesInPlan is created as an expression based on that code?
    I will attempt to recreate this, thank you very much

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Open the report in design view and open the Record Source in the Properties window:
    Click image for larger version. 

Name:	Screenshot 2023-03-13 135849.png 
Views:	13 
Size:	109.5 KB 
ID:	49880

    Click image for larger version. 

Name:	Screenshot 2023-03-13 135627.png 
Views:	14 
Size:	57.3 KB 
ID:	49879

    You will need to copy the public function SimpleCSV into one of your standard module.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-08-2017, 06:00 AM
  2. Count unique customers in report header
    By hithere in forum Reports
    Replies: 7
    Last Post: 02-08-2013, 12:47 AM
  3. Export First Page of Report for Each Unique Header
    By ertweety in forum Programming
    Replies: 3
    Last Post: 06-05-2012, 06:23 PM
  4. Create a unique report
    By top1hat19 in forum Reports
    Replies: 1
    Last Post: 01-31-2011, 12:42 PM
  5. How to create and print unique report for each entry???
    By Stelios Panayides in forum Reports
    Replies: 2
    Last Post: 10-05-2009, 08:54 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums