Change table headings from horizontal to vertical in Access table

recycleaus
recycleaus used Ask the Experts™
on
I am having a friend help me build a small trading database in MS Access and he has hit a roadblock in terms of the useability of a form.

In the attached DB, the deals tab has a table embedded called Deal Products. The deal products headings run horizontally like a normal table but we need to find a way to have the headings run vertically as there is never more than 4 products per deal and you have to scroll a long way sideways just to see all the deal products details.

Could someone please let me know any way we can make this work?

Thanks
TradingDB.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
Can not access your db, but what you are describing is a pivot see
https://support.office.com/en-us/article/Create-PivotTable-or-PivotChart-views-in-an-Access-desktop-database-83e524df-dfbd-456d-9dd0-0a48c1aa6752

Often a sample data and what you want to end up with ....
John TsioumprisSoftware & Systems Engineer

Commented:
If you have maximum 4 products per deal maybe you should change your design concept...just change from Continuous form to Single form...have 4 subforms Single Form ....each representing one product and have them linked to the master key.

Author

Commented:
Hey, I have attached a couple of screenshots. The first is showing the access form as it is now with the headings horizontal and the second screenshot is my current excel worksheet showing roughly how I'd like the form to look
Have.png
Deal-Calculations.png
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Partha MandayamTechnical Director

Commented:
Change the form design as per your excel sheet. Drag and drop the extra fields
John TsioumprisSoftware & Systems Engineer

Commented:
The Excel design matches my suggestion for "Single Form" Default View.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Access doesn't work that way (nor does any normalized database container). You're asking to have data attributes (i.e. Tons, #Containers, etc) as Rows, and the actual data associated with those attributes (i.e. the Number of Tons, Number of Containers) as Columns, and that can't be done in the database container - or at least not in a normalized database container.

You can certainly "pivot" your data and create any sort of view you want, but understand when you do that you take on the task of handling data manipulation yourself - that is, you're responsible for all data saves, updates, etc. This is commonly done by using temporary tables to house your data in a manner that works for your desired form, and then using code behind the scenes to move the data from the temporary tables back to the live tables. Note when I write "pivot", I don't mean using pivot tables. Access doesn't support those, nor could you edit them even if you could. And to be clear, Access can create a Crosstab query, which is similar to a pivot table, but you still can't edit a crosstab query.

You can try John's approach of using subforms, but you're not going to get anything that looks like your Excel sheet. It may be close enough to work, but the only way you'll get a form that looks like that is through either (a) judicious use of temp tables or subforms or (b) moving to an unbound model (which kinda defeats the purpose of Access).
Chief Technology Officer
Commented:
Now you're seeing why Excel is a great report creating tool, while Access is designed more for data processing - not desired display format.
To mimic Excel in Access to this extent requires some serious jury-rigging of Access objects.
First, you'll need a single-form for your first product type with the field controls setup vertically with the textbox labels on the left and the textboxes on the right.  Each form displaying one record - the one for the product.
For the rest of the products, a form with just the textboxes.
Then, you'll need to put the forms in subforms that are placed next to each other so that the rows are side-by-side as needed.

You can tweak it from there, provided you're willing to go to this extent.
Mark EdwardsChief Technology Officer

Commented:
John's on the right track.
Distinguished Expert 2017

Commented:
I think you are looking for a layout like this form.  Nothing in Access makes this easy but with some tweaking you can make it work.  In this case I wanted to be able to view several properties side-by-side.  The preceding form allows me to choose up to 5 properties and each of the 5 is shown in a subform.  Notice in design view that the labels are hardcoded on the mainform rather than being embedded in each subform.  Try it both ways, you just have to play with the layout until you can see what you need.  I didn't have to make the subforms sync because I could make the form long enought to show all the attributes.  If you need to make the forms sync, you'll need to use labels from the subform, you will not be able to put the labels on the main form.  You can probably make the left most subform wider than the others so the leftmost subform includes the labels and the others hide them.  Perhaps the rightmost subform can show the labels on the right.
5AccrossSubforms.JPG5AccrossSubformsDesign.JPG
thanks all... got it sorted with your suggestions!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial