Avatar of recycleaus
recycleaus
Flag for Australia asked on

Change table headings from horizontal to vertical in Access table

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
DatabasesMicrosoft Access

Avatar of undefined
Last Comment
recycleaus

8/22/2022 - Mon
arnold

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 Tsioumpris

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.
recycleaus

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Partha Mandayam

Change the form design as per your excel sheet. Drag and drop the extra fields
John Tsioumpris

The Excel design matches my suggestion for "Single Form" Default View.
Scott McDaniel (EE MVE )

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).
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Mark Edwards

John's on the right track.
PatHartman

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
recycleaus

ASKER
thanks all... got it sorted with your suggestions!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23