R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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 ....
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 ....
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.
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
Have.png
Deal-Calculations.png
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).
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).
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial