Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-30-2019, 10:09 AM   #1
alvingenius
IT Specialist
 
alvingenius's Avatar
 
Join Date: Jul 2016
Posts: 50
Thanks: 7
Thanked 0 Times in 0 Posts
alvingenius is on a distinguished road
Arrow Splitting 1 field in table to 2 text boxes in report

Hello,

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

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
Attached Images
File Type: png 1.PNG (8.8 KB, 53 views)
File Type: png 2.PNG (11.1 KB, 58 views)
Attached Files
File Type: accdb Report.accdb (832.0 KB, 8 views)

alvingenius is offline   Reply With Quote
Old 09-30-2019, 10:25 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,361
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Splitting 1 field in table to 2 text boxes in report

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
Attached Files
File Type: accdb Report.accdb (832.0 KB, 6 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
alvingenius (09-30-2019)
Old 09-30-2019, 11:56 PM   #3
alvingenius
IT Specialist
 
alvingenius's Avatar
 
Join Date: Jul 2016
Posts: 50
Thanks: 7
Thanked 0 Times in 0 Posts
alvingenius is on a distinguished road
Re: Splitting 1 field in table to 2 text boxes in report

Quote:
Originally Posted by isladogs View Post
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

alvingenius is offline   Reply With Quote
Old 10-01-2019, 12:13 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,361
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Splitting 1 field in table to 2 text boxes in report

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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Tags
report

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating a table field using unbound text boxes ozzy68 Forms 4 03-27-2013 02:39 PM
Second Form Text Boxes To Populate Table Field Aussie60 Forms 0 05-06-2011 11:01 PM
Update multiple text boxes in a report from a single table field Nangasaur Reports 10 05-11-2010 04:38 PM
table field values to appear in form text boxes Orson9750 Forms 1 04-13-2009 11:26 AM
Splitting text field for report fdservices Crystal Reports 3 07-04-2008 08:44 AM




All times are GMT -8. The time now is 05:32 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World