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?
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
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).
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.
recycleaus
ASKER
thanks all... got it sorted with your suggestions!
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 ....