Report Grouping - merged fields and group header, is it possible? (1 Viewer)

JOC

Registered User.
Local time
Today, 07:28
Joined
Sep 2, 2014
Messages
16
I have a table which contains the many data of a one to many relationship. Within this 'many table' there maybe anything up to about 6 many items, but there can be fewer. I am making a report for this information.

I can use report grouping to pull out just the many items for each one item. The item I've grouped on can either sit as a group heading, or I can move it down into report detail and select to ignore duplicates. This sees it as a single entry sitting level with the top item from the many side of the table.

I now want what seems impossible in an access report, but which would be childs play in something like Excel. Can I get the entry for the one item into a single field that is the same height as all the many rows that apply to it and have it sit alongside the many rows? In excel I would select all the fields down the requisite number of rows and hit the merge button. Thus getting one tall field and all the separate data sitting next to it in separate rows. OK, I would have to do it individually for each dataset as each one would have a different number of rows, but its that look that I'm after

The layout function lets me merge rows, but clearly not across the grouped data items. I don't want to have to do it for each item either so the height change will have to depend on the number of rows of data in many fields.

If no-one has a definitive answer or ideas for a work around to 'see' the same effect this would be welcome - I'm currently playing with border layouts, but this isn't working.

Many thanks
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:28
Joined
Feb 19, 2013
Messages
16,553
immediate thought is to group the report at a higher level, but perhaps a visual of what you are trying to achieve would clarify your requirement
 

JOC

Registered User.
Local time
Today, 07:28
Joined
Sep 2, 2014
Messages
16
Hi CJ_London - hang on the table didn't appear

I'd rather not take a visual from my DBase, but I've recreated something that looks vaguely similar in Excel - assume that my table structure for my actual data permits the necessary queries as I am sure they are right:

Page Header Animal type Mammal Day Weight Weighed by Animal type header Detail Dog 5 1.2 Person 1 10 1.8 15 1.9 20 1.9 Cat 5 4.5 Person 2 7 5.6 9 6.7 15 6.8 18 6.9 20 7.1 Giraffe 10 56 Person 1 20 67 30 78


Dog, Cat Giraffe, are currently in the detail section with duplicate entries hidden, but can also appear in the Animal type header (which is what I am grouping on) - its just that then they are above and to the left of the weights. The visual above is what I am trying to achieve, currently they only appear next to the first weight with the duplicates deleted. As you can see I want to achieve something similar with the weighed by field in the same table. At the moment the days and weights are in the many table and the substances and weighed by are on the 'one' side of the table joins.

I hope this makes sense, but I doubt it is possible - if someone can confirm or otherwise I will save a lot of time.
 

JOC

Registered User.
Local time
Today, 07:28
Joined
Sep 2, 2014
Messages
16
Ah, it seems to suggest that I've got a jpg. uploaded.
 

Attachments

  • Capture.JPG
    Capture.JPG
    40.2 KB · Views: 277

CJ_London

Super Moderator
Staff member
Local time
Today, 07:28
Joined
Feb 19, 2013
Messages
16,553
sorry - makes no sense, columns do not line up - take a screenshot of the excel mockup - and also include a mockup of what you are getting at the moment
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:28
Joined
Feb 19, 2013
Messages
16,553
seen the image

you need to group by animal and person as one (perhaps need a temporary field to combine them), then I think you will need to use a subreport for day and weight columns, then some code in the detail format event to a) resize the section and subreport for the number of rows in the subreport and b) centre the animal type and weighed by controls vertically.

google something like 'access report vba resize section' or similar to find examples of code
 

JOC

Registered User.
Local time
Today, 07:28
Joined
Sep 2, 2014
Messages
16
Hi CJ_London

Thanks for the input - I had wondered if I was going to need to drop to a separate sub-report for the day and weight data. That bit I can probably manage. The coding for the sizing is probably 'beyond my ken' esp. from scratch, but I'll take a look at the google search and see if there anything out there that I can poach and adapt - occasionally I do work things out from that angle, but despite courses and many years with Access the nuances of a VBA still seem to elude me. Which is annoying as I manage to solve most other things as could do so much more with a little coding under my belt.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:28
Joined
Feb 19, 2013
Messages
16,553
just guessing but something like

Code:
 [COLOR=yellowgreen]'adjusts subform (subform should be continuous form, not datasheet)[/COLOR]
 me.subreportname.height=(subreportname.form.section(0).height*subreportname.form.recordcount)+subreportname.form.section(1).height
  
 [COLOR=yellowgreen]'adjust detail height[/COLOR]
 me.section(0).height=me.subreportname.height 
  
 [COLOR=yellowgreen]'adjust other control positions[/COLOR]
 me.animaltype.top=(me.section(0).height-me.animaltype.height)/2 
 me.people.top=me.animaltype.top
not sure of section numbers, you need to check
 

JOC

Registered User.
Local time
Today, 07:28
Joined
Sep 2, 2014
Messages
16
Thanks very much for that - you've obviously got one of those minds that works in that way - I'll redesign the reports and then have a play and report back. It might take a few days as I find time.

The daft thing is that I can 'think' database table layouts and designs in my minds eye - I never write anything down, yet the moment I start to consider coding I never know where to start!! Hence I sit in constant awe of those that can :-D
 

Users who are viewing this thread

Top Bottom