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
> Crosstab Query, Access 2016    
 
   
lilus615
post Jun 2 2020, 02:49 PM
Post#1



Posts: 123
Joined: 6-September 06
From: CA, USA


good afternoon,

i have attached screenshots of a crosstab query and report in Access and one from Excel pivot table, showing what i want to accomplish in Access. how do i add current balance to the crosstab and total count of Status and Total Sum of current balance on the right columns?

thank you
Attached File(s)
Attached File  Crosstab_report.zip ( 66.67K )Number of downloads: 6
 
Go to the top of the page
 
June7
post Jun 2 2020, 02:54 PM
Post#2



Posts: 1,517
Joined: 25-January 16
From: The Great Land


Suggest you provide raw data for us to work with - DB or spreadsheet.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
lilus615
post Jun 2 2020, 03:34 PM
Post#3



Posts: 123
Joined: 6-September 06
From: CA, USA


also, how do you sort alphanumeric field, so week 10 is not after week 1

thank you
Attached File(s)
Attached File  Crosstab_query.zip ( 17.74K )Number of downloads: 5
 
Go to the top of the page
 
RJD
post Jun 2 2020, 03:38 PM
Post#4


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


...and be sure, to include the crosstab query in the db so we can see the SQL. The Design View picture of the query you posted is not really very helpful. The whole db would be best, as June7 indicated, along with how you want to calculate "current balance".

Ah, I see you posted an Excel file. Actually, could you post the Access db?

Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
lilus615
post Jun 2 2020, 03:59 PM
Post#5



Posts: 123
Joined: 6-September 06
From: CA, USA


SQL

TRANSFORM Count([Qry for deferral - FINAL].Status) AS CountOfStatus
SELECT [Qry for deferral - FINAL].Week_Name, [Qry for deferral - FINAL].[Region_#]
FROM [Qry for deferral - FINAL]
GROUP BY [Qry for deferral - FINAL].Week_Name, [Qry for deferral - FINAL].[Region_#]
PIVOT [Qry for deferral - FINAL].Status;
Go to the top of the page
 
June7
post Jun 2 2020, 06:20 PM
Post#6



Posts: 1,517
Joined: 25-January 16
From: The Great Land


If you use the query wizard it will automatically create one totals column. Then modify for a second:

TRANSFORM Count([Qry for deferral - FINAL].[CurrentBalance]) AS CountOfCurrentBalance
SELECT [Qry for deferral - FINAL].[Week_Name], [Qry for deferral - FINAL].[Region_#], Count([Qry for deferral - FINAL].[CurrentBalance]) AS [TotalCount], Sum([CurrentBalance]) AS [SumCB]
FROM [Qry for deferral - FINAL]
GROUP BY [Qry for deferral - FINAL].[Week_Name], [Qry for deferral - FINAL].[Region_#]
PIVOT [Qry for deferral - FINAL].[Status];

If you want both count and sum for each Status, review http://allenbrowne.com/ser-67.html#MultipleValues

This post has been edited by June7: Jun 2 2020, 06:25 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
lilus615
post Jun 24 2020, 08:01 PM
Post#7



Posts: 123
Joined: 6-September 06
From: CA, USA


I have 2 crosstabs 1 for Sum and 1 for Count, then i combined them, results is in the attached document.

TRANSFORM Sum([Qry for deferral - FINAL].[Current Balance]) AS [SumOfCurrent Balance]
SELECT [Qry for deferral - FINAL].Week_Name, [Qry for deferral - FINAL].[Region_#], Sum([Qry for deferral - FINAL].[Current Balance]) AS [Total Of Current Balance]
FROM [Qry for deferral - FINAL]
GROUP BY [Qry for deferral - FINAL].Week_Name, [Qry for deferral - FINAL].[Region_#]
PIVOT [Qry for deferral - FINAL].Status;


TRANSFORM Count([Qry for deferral - FINAL].[Current Balance]) AS CountOfCurrentBalance
SELECT [Qry for deferral - FINAL].Week_Name, [Qry for deferral - FINAL].[Region_#], Count([Qry for deferral - FINAL].[Current Balance]) AS TotalCount, Sum([Qry for deferral - FINAL].[Current Balance]) AS SumCB
FROM [Qry for deferral - FINAL]
GROUP BY [Qry for deferral - FINAL].Week_Name, [Qry for deferral - FINAL].[Region_#]
PIVOT [Qry for deferral - FINAL].Status;


SELECT * FROM [Qry for segments - Crosstab balances]
UNION SELECT * FROM [Qry for segments - crosstab count];
This post has been edited by lilus615: Jun 24 2020, 08:03 PM
Attached File(s)
Attached File  combined_SQL_queries.zip ( 74.69K )Number of downloads: 3
 
Go to the top of the page
 
June7
post Jun 24 2020, 08:08 PM
Post#8



Posts: 1,517
Joined: 25-January 16
From: The Great Land


So, issue is resolved?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
MadPiet
post Jun 24 2020, 08:11 PM
Post#9



Posts: 3,778
Joined: 27-February 09



UNION ALL not UNION.

UNION sorts and removes duplicates... do you really want that? and posting screenshots is pretty much a waste of time. We can't run a screen shot of some code.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th July 2020 - 03:22 PM