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

alvingenius

IT Specialist
Local time
Today, 05:06
Joined
Jul 10, 2016
Messages
168
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: 115
  • 2.PNG
    2.PNG
    11.1 KB · Views: 126
  • Report.accdb
    832 KB · Views: 60

isladogs

CID VIP
Local time
Today, 04:06
Joined
Jan 14, 2017
Messages
16,382
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: 60

alvingenius

IT Specialist
Local time
Today, 05:06
Joined
Jul 10, 2016
Messages
168
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

CID VIP
Local time
Today, 04:06
Joined
Jan 14, 2017
Messages
16,382
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