Solved Splitting 1 field in table to 2 text boxes in report (1 Viewer)

alvingenius

IT Specialist
Local time
Tomorrow, 01:48
Joined
Jul 10, 2016
Messages
169
Hello,

I dont't know exactly how to write my issue in title :banghead:

but i hope you understand it from screen shots

i made a sample DB to match my same issue but in a simple db

i've this table


and i wanna generate a report from it with (USA & London ) as labels in Header

and showing results as this screenshot


( This photo is edited by Photoshop to show you the result i want from the report )

is it possible ?
and there's no way to change the structure of the table

Example Database attached

Thanks all
 

Attachments

  • 1.PNG
    1.PNG
    8.8 KB · Views: 190
  • 2.PNG
    2.PNG
    11.1 KB · Views: 204
  • Report.accdb
    832 KB · Views: 106

isladogs

MVP / VIP
Local time
Today, 23:48
Joined
Jan 14, 2017
Messages
18,186
Use a crosstab query for this. There is a wizard to help you.
The query SQL is
Code:
TRANSFORM First(tbl_Names.[Shape]) AS FirstOfShape
SELECT tbl_Names.[UserName]
FROM tbl_Names
GROUP BY tbl_Names.[UserName]
PIVOT tbl_Names.[Country];

See attached
 

Attachments

  • Report.accdb
    832 KB · Views: 128

alvingenius

IT Specialist
Local time
Tomorrow, 01:48
Joined
Jul 10, 2016
Messages
169
Use a crosstab query for this. There is a wizard to help you.
The query SQL is
Code:
TRANSFORM First(tbl_Names.[Shape]) AS FirstOfShape
SELECT tbl_Names.[UserName]
FROM tbl_Names
GROUP BY tbl_Names.[UserName]
PIVOT tbl_Names.[Country];

See attached

ooh
Thanks alot @isladogs, it helped alot and i never used crosstab before
and i may post again with more complex report if u can help me it didn't work with crosstab
 

isladogs

MVP / VIP
Local time
Today, 23:48
Joined
Jan 14, 2017
Messages
18,186
You're welcome.
Crosstabs are used to get a spreadsheet like format from normalised data.
Very useful for summary reports.

Its worth practising crosstabs using the wizard at first but they can also be created/modified using the query designer.
 

Users who are viewing this thread

Top Bottom