- Local time
- Today, 21:27
- Joined
- Feb 19, 2013
- Messages
- 17,130
As a result of this post https://www.access-programmers.co.u...te-reports-grouping-levels-with-forms.326697/
I thought I would diddle around and come up with some examples.
This is a simple format - only shows the customer once and uses conditional formatting to change the backcolour for each customer. It utilises a rownumber function to determine if the customer has changed.
this one just uses the same query, but with a different formatting rule to highlight just the customer row
This one puts the customer on a separate row - requires the use of a union query
this one, similar to the simple one utilises the union query and the rownumber function
finally this one shows two levels of grouping with the order data on a separate row - adding another select to the union query
grouping to more levels is possible - just requires adding another sql statement to the union query and modifying the sort order.
The controls on the form should have their back style set to transparent and recommend that border style is also transparent. Use of conditional formatting is up to you, it is for visual effect only. Note that the control that is utilising conditional formatting should have its back style set to normal and it's position set to back.
Because this is a form the data will not be editable. In the attached I have not disabled/locked the controls so if a user selects a control it is likely to hide other controls on the same row if they are below. In a real environment I would expect the controls to be disabled and locked. Filtering can be done utilising unbound controls in the header if required - the right click filters may produce unexpected results.
I thought I would diddle around and come up with some examples.
This is a simple format - only shows the customer once and uses conditional formatting to change the backcolour for each customer. It utilises a rownumber function to determine if the customer has changed.
this one just uses the same query, but with a different formatting rule to highlight just the customer row
This one puts the customer on a separate row - requires the use of a union query
this one, similar to the simple one utilises the union query and the rownumber function
finally this one shows two levels of grouping with the order data on a separate row - adding another select to the union query
grouping to more levels is possible - just requires adding another sql statement to the union query and modifying the sort order.
The controls on the form should have their back style set to transparent and recommend that border style is also transparent. Use of conditional formatting is up to you, it is for visual effect only. Note that the control that is utilising conditional formatting should have its back style set to normal and it's position set to back.
Because this is a form the data will not be editable. In the attached I have not disabled/locked the controls so if a user selects a control it is likely to hide other controls on the same row if they are below. In a real environment I would expect the controls to be disabled and locked. Filtering can be done utilising unbound controls in the header if required - the right click filters may produce unexpected results.