UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Create Crosstab Report, Access 2013    
 
   
g0049978
post Sep 25 2019, 12:35 PM
Post#1



Posts: 26
Joined: 13-September 19



I have a table with 6 columns (source, reporttime, StartingOpen, ClosedInterval, TotalIncoming, Aging). The reporttime shows 5 different times during the day (8am, 12pm, 2pm, 4pm and 6pm). I'd like my report to display the first Source with all times across the top and each relative value associated. I'm assuming this is a multiple value crosstab. I'm not sure if I need to create a query and build the report off that or do numerous Dlookup references within the report text boxes. What's the best way to approach. I've attached an example of how I need the report to look.



Attached File(s)
Attached File  ReportExample.zip ( 27.33K )Number of downloads: 7
 
Go to the top of the page
 
GroverParkGeorge
post Sep 25 2019, 01:00 PM
Post#2


UA Admin
Posts: 36,041
Joined: 20-June 02
From: Newcastle, WA


What would also be REALLY helpful is some sample data in the actual table from which this was produced.

This looks like it could be done with a series of crosstab queries, perhaps even set up as a Union query, but the quickest way to get good suggestions here might be that sample data and table structure so the suggestions are tailored to your environment specifically.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
arnelgp
post Sep 25 2019, 01:18 PM
Post#3



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


I would think that the table is already a summarized
based on the attached excel file.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
g0049978
post Sep 25 2019, 01:32 PM
Post#4



Posts: 26
Joined: 13-September 19



See attached
Attached File(s)
Attached File  Database11.zip ( 18.28K )Number of downloads: 7
 
Go to the top of the page
 
isladogs
post Sep 25 2019, 01:47 PM
Post#5


UtterAccess VIP
Posts: 1,801
Joined: 4-June 18
From: Somerset, UK


Your table is a spreadsheet layout.
You might be better doing this in Excel.
If using Access, first you need to normalise your table then you can do this with a crosstab.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
MadPiet
post Sep 25 2019, 01:50 PM
Post#6



Posts: 3,360
Joined: 27-February 09



Do you have to do this report in Access? I might be remembering incorrectly, but I always hated what Access reports did to crosstabs.

You could do this in Excel in a nanosecond, but if you only need if for display and not further examination, it's probably not worth the trouble.
Go to the top of the page
 
GroverParkGeorge
post Sep 25 2019, 02:06 PM
Post#7


UA Admin
Posts: 36,041
Joined: 20-June 02
From: Newcastle, WA


You will need to massage this further to get that Excel spreadsheet style report, but the raw data is here.

It's not a pretty approach, although I believe it does the trick.

Perhaps someone will come up with a more elegant version.

Attached File  Database11.zip ( 27.28K )Number of downloads: 6


I agree with both Colin and Pieter, though, that the underlying table is not optimal grin.gif

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
g0049978
post Sep 25 2019, 02:06 PM
Post#8



Posts: 26
Joined: 13-September 19



I need to put this in the same format as what's seen in the Excel attachment and send it in an e-mail (not as attachment) but as a picture 5 times a day (for each time). All times should show even if they don't have values yet. I want it all to be automated so I don't have to worry about these e-mails going out every 2 hours.
Go to the top of the page
 
GroverParkGeorge
post Sep 25 2019, 02:17 PM
Post#9


UA Admin
Posts: 36,041
Joined: 20-June 02
From: Newcastle, WA


As noted, what you want to do here is partly getting the data into a layout and summary status you need, but also a lot about formatting as the final step. I would go ahead and set this up as an export to Excel where the Excel format you want is more easily done.

Create a template file in Excel, with formatting as you need it and push the data into that template from the query in Access. Or pull it from Excel if you prefer.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
arnelgp
post Sep 25 2019, 02:20 PM
Post#10



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


manual and union.

EDIT: just saw the OPs table now.
the only difference is my time is text.
This post has been edited by arnelgp: Sep 25 2019, 02:24 PM
Attached File(s)
Attached File  sourceUnion.zip ( 77.51K )Number of downloads: 5
 

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Sep 25 2019, 02:33 PM
Post#11


UA Admin
Posts: 36,041
Joined: 20-June 02
From: Newcastle, WA


It occurs to me that both suggestions do two things:

They offer "solutions" to the problem.

They make very clear why non-normalized tables are so cumbersome. You certainly can create monster union queries based on them, and end up providing care and nourishment to those monsters forever after.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
arnelgp
post Sep 25 2019, 03:00 PM
Post#12



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


we'll i know this case is not normal.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Sep 25 2019, 03:37 PM
Post#13


UA Admin
Posts: 36,041
Joined: 20-June 02
From: Newcastle, WA


And that gnarly query you had to create illustrates the problem with such table designs very well.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 09:27 PM