Alignment of group header (1 Viewer)

amir0914

Registered User.
Local time
Today, 11:22
Joined
May 21, 2018
Messages
151
Hi everyone, I have a database and two columns of one table (sname,slump) is common for 3 records, so I created grouped report by sname, slump. but my problem is that the header record's are on the above of records, like this :
Screenshot (1720) - Copy - Copy.png


Is there a way that header record's will be side of the records without repeat? (not header ) like this :
Screenshot (1720) - Copy.png
 
Last edited:

June7

AWF VIP
Local time
Today, 10:22
Joined
Mar 9, 2014
Messages
5,423
Access reports can't behave like worksheet.

Instead of grouping could just sort records and set sname and slump textboxes to not display repeated values. However, cannot center vertically in relation to the 3 records. At least not without some VBA, possibly. Would there always be 3 records?
 

amir0914

Registered User.
Local time
Today, 11:22
Joined
May 21, 2018
Messages
151
Access reports can't behave like worksheet.

Instead of grouping could just sort records and set sname and slump textboxes to not display repeated values. However, cannot center vertically in relation to the 3 records. At least not without some VBA, possibly. Would there always be 3 records?
Yest, always 3 records.,can you explain sort way and textboxes to not display repeated?
 

June7

AWF VIP
Local time
Today, 10:22
Joined
Mar 9, 2014
Messages
5,423
As I said, in report design, don't apply Grouping, just Sorting. Textbox has HideDuplicates property.

Grouping is really important if need to do aggregate calculations by groups, such as Sum, Avg, Count.
 

amir0914

Registered User.
Local time
Today, 11:22
Joined
May 21, 2018
Messages
151
Thanks June7, I used HideDuplicates property, that was great,, but still two fields are on the first row, is it possible to move down the fields with or without vba?
Screenshot (1723).png
 

June7

AWF VIP
Local time
Today, 10:22
Joined
Mar 9, 2014
Messages
5,423
Can't 'move down the fields'. However, might be able to use VBA or Conditional Formatting to hide the first and third and leave the second visible. Following options occurred to me this morning.

1. go back to grouping you originally had, include sname and slump in both group header and detail section, HideDuplicates set to no, header Visible property set to no

2. have another textbox named tbxCnt in detail section with ControlSource: =1, and RunningSum set to 'over group'

3. Conditional Formatting rule for sname and slump textboxes in detail section:
Expression is: [tbxCnt] <> 2
Set rule to change font color to white if tbxCnt <>2

Possible alternative:

1. no grouping, HideDuplicates set to no

2. textbox named tbxCnt with expression: =DCount("*", "tablename", "sname='" & [sname] & "' AND slump='" & [slump] & "' AND ID<=" & [ID])

3. same as above

Neither have been tested. If you want more help, provide db for analysis.

VBA could be used to conditionally set visibility of textboxes for each record. However, this code would be in Format event which executes only for report in PrintPreview or direct to printer. Conditional Formatting works for any mode.
 
Last edited:

Micron

AWF VIP
Local time
Today, 14:22
Joined
Oct 20, 2018
Messages
3,476
Maybe 2 subreports on a main report. Left side sub with only 2 fields, right side sub for rows. This could only work if the row count is always going to be the same. Even then, it's just a wild guess by me.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:22
Joined
May 7, 2009
Messages
19,169
i think it would best to leave it as is, on the left of the first element in the group.
as you can see almost all records are somewhat identical, so placing it in-between may confuse
others viewing your report.

the technique here applies to Print Preview only.
 

Attachments

  • slump.zip
    73.4 KB · Views: 100
Last edited:

amir0914

Registered User.
Local time
Today, 11:22
Joined
May 21, 2018
Messages
151
Thank you very much June7, but there is a problem, your trick hide all sname and slump without the second record ([tbxCnt] = 2). whilst the report has other sname and slump in "tbxCnt <>5" or "tbxCnt <>8" or "tbxCnt <>11" ,....
Screenshot (1725).png
 

June7

AWF VIP
Local time
Today, 10:22
Joined
Mar 9, 2014
Messages
5,423
The DCount should result in only 1, 2, 3 sequence count for each sname/slump group. As I said, if you need more help, provide database.
 

amir0914

Registered User.
Local time
Today, 11:22
Joined
May 21, 2018
Messages
151
i think it would best to leave it as is, on the left of the first element in the group.
as you can see almost all records are somewhat identical, so placing it in-between may confuse
others viewing your report.

the technique here applies to Print Preview only.
bravo arnelgp, that's exactly what I want.
 

amir0914

Registered User.
Local time
Today, 11:22
Joined
May 21, 2018
Messages
151
Hello again everyone, Has subform group or merge cells like report? I mean can I do your way on subform like this :

Screenshot (1733).png

Of course I could hide rows except the second record by conditional formatting, but I couldn't add red line at the bottom of records.
 

Attachments

  • slump(a).accdb
    608 KB · Views: 108

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:22
Joined
Feb 28, 2001
Messages
26,999
In Access, those items in the first two columns are not cells like those that would appear in Excel. Each row is potentially a complete and independent record in a detail section, the boundaries of which would be defined by a group-break if you had reports grouping on SName or SLump. You could engage in complex logic to number the records within each "cluster" and use a conditional expression in a query to only show SName and SLump for the middle record. This is not a trivial operation, though.

A subform is no better than a form with regard to manipulating individual row field sizes. Usually, Access is all or nothing at all when it comes to mucking about with texbox sizes. Access and Excel are NOT the same and this that you are asking is one example of the difference.

Excel cells are technically independent of each other with regard to sizing and layout (hence the ability to merge or split a single cell at will.) You can explicitly reference a cell's content from another cell, though it becomes complex if you've done some cell splitting/merging.

With Access, the idea is that the fields in a record should be treated uniformly. You cannot highlight one cell or one row in a detail section without conditional formatting and/or complex logic. You cannot split or merge two corresponding fields in two records just because they appear to be adjacent to each other. You cannot even easily reference one record from another record.

It is a conceptual difference between the two that is difficult for new Access users to absorb, but it is true. You don't have the same type of control using Excel that you would have using Access. But the other side of the coin is the data integrity safeguards and other features of Access that make it so worthwhile.

Now, maybe for once an easier answer. If you want a red border between groupings like that, make a group footer that only contains a red border. But this WOULD imply that you have to have a footer section for that group.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:22
Joined
Feb 19, 2013
Messages
16,553
another way is to have a textbox, set very thin and no borders at the bottom of each row - then use conditionalformating to set the back colour to red if

[tbxCnt] mod 3 = 0
 

Users who are viewing this thread

Top Bottom