Display a table horizontally in a form

[titiakila]

Hello,


I would like to know how to display a table horizontally in a form in ACCESS.


For example I have the table :


product price brand
coca-cola 3,0 coca
pepsi-cola 2,5 pepsi
orangina 3,0 -


and I would like to display


product coca-cola pepsi-cola orangina
price 3,0 2,5 3,0
brand coca pepsi -


with the names of the fields in a column


Thanks

[isladogs]

Create a crosstab query and use that as your form's record source

[titiakila]

Thank you. Yes but what would be the query ?

[mike60smart]

Hi
Can you upload a zipped copy of the database?

[titiakila]

No I'm sorry I can't.
It's was just a test database to do it on a bigger.
The names of columns are in the first line.

[Minty]

Open the query designer. Add your table.
Right Click the section above the grid and Select Query Type - "CrossTab"

Add Product to the Grid, in the crosstab section select Column heading.
Add Price to the Grid and select Value as the Crosstab selection
Add Brand and select Row Heading as the cross tab selection

It won't be exactly want you want, but you'll see how it works.

[isladogs]

Actually re-reading your question, I don't think you can get exactly what you want in any recent version of Access.
You can use the crosstab query wizard to get something similar. Use your own table name instead of Table1

TRANSFORM First(Table1.[Price]) AS FirstOfPrice
SELECT Table1.[Brand]
FROM Table1
GROUP BY Table1.[Brand]
ORDER BY Table1.[Brand] DESC 
PIVOT Table1.[Product];

You may be able to modify that to approximately suit your needs
However, the easiest solution would be to do this in Excel using Transpose Data.

Good luck

[titiakila]

effectively a crosstab query is not exactly what I am looking for because you have to chose columns to the left and one unique field of values and I want to have the name of the fields to the left so that is not exactly that. Oh ok maybe there is no solution with access. Thank you all very much for your help.

[isladogs]

Its trivial to do in Excel:

[titiakila]

Yes I know but I wanted to do that on access because that is there I do my interface on the database not in Excel. Thank you for your response.

[isladogs]

In that case, it looks like you're out of luck.
Its not how Access is designed to work.

Obviously you could paste the transposed data back into Access or open the Excel file within Access using a web browser control as shown below
For info the Excel file is fully editable in a web browser control

[Minty]

You could (if you really have to) achieve this in VBA, but it's a lot of work.
Sometimes it's better to get people (end users) to accept the different presentation and get used to it, rather than having to jump through a lot of hoops trying to make it look and behave the same as excel.

[titiakila]

Thank you very much. I will consider both options. You're right maybe it's better to leave the columns vertically and just convince the users.

[CJ_London]

If you want to make the effort, see these links

https://access-4suc6.blogspot.com/20...umns-into.html

or google 'access vba transpose columns to rows' or similar for other links