Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-05-2017, 07:42 PM   #1
Harper
Newly Registered User
Silver Supporter
 
Join Date: Dec 2017
Posts: 17
Thanks: 11
Thanked 0 Times in 0 Posts
Harper is on a distinguished road
Crosstab Query - Concatenate Rows & Columns

I have one table (Table1) in a crosstab query. I'd like to combine the values of rows "Strawberry" and "Blueberry" into 1-row called "Berries." And I'd like to combine the values of date columns (date sold) into 1-column called "Jan 2017." How can I do this? Thanks!

Code:
TRANSFORM Count(Table1.[Ice Cream]) AS [CountOfIce Cream] SELECT Table1.[Ice Cream] FROM Table1 GROUP BY Table1.[Ice Cream] PIVOT Table1.[Date Sold]
Crosstab:


Desired results:


Last edited by Harper; 12-05-2017 at 09:01 PM.
Harper is offline   Reply With Quote
Old 12-05-2017, 08:25 PM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,288
Thanks: 78
Thanked 1,409 Times in 1,329 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Crosstab Query - Concatenate Rows & Columns

Use a translation table to get the groups for berries.

Code:
Flavour    | FlavourGroup
Strawberry | Berries
Blueberry  | Berries
Derive a field based on the date formatted as "mmm yyyy"

Build a query with those fields then Crosstab the results
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
Harper (12-11-2017)
Old 12-05-2017, 08:59 PM   #3
Harper
Newly Registered User
Silver Supporter
 
Join Date: Dec 2017
Posts: 17
Thanks: 11
Thanked 0 Times in 0 Posts
Harper is on a distinguished road
Re: Crosstab Query - Concatenate Rows & Columns

Quote:
Originally Posted by Galaxiom View Post
Derive a field based on the date formatted as "mmm yyyy"

Build a query with those fields then Crosstab the results
Thanks Galaxiom! Sorry, I'm not sure what you mean by "derive a field." I added screenshots of crosstab query and desired results.

Harper is offline   Reply With Quote
Old 12-05-2017, 09:06 PM   #4
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,288
Thanks: 78
Thanked 1,409 Times in 1,329 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Crosstab Query - Concatenate Rows & Columns

Quote:
Originally Posted by Harper View Post
Thanks Galaxiom! Sorry, I'm not sure what you mean by "derive a field." I added screenshots of crosstab query and desired results.
In the Field cell of query designer:
Code:
MonthYear: Format([DateSold], "mmm yyyy")
Galaxiom is offline   Reply With Quote
Old 12-06-2017, 12:37 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Crosstab Query - Concatenate Rows & Columns

To continue Galaxiom's thought:

The group table can be sparse meaning that it only contains entries that you want to group. In that case, your query would use a left join to the group table and an IIf() so that it would return either the FlavorGroup from the group table when this field is not null or the Flavor value when the group is null.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 12-10-2017, 09:18 PM   #6
Harper
Newly Registered User
Silver Supporter
 
Join Date: Dec 2017
Posts: 17
Thanks: 11
Thanked 0 Times in 0 Posts
Harper is on a distinguished road
Re: Crosstab Query - Concatenate Rows & Columns

Hi Galaxiom/Pat,

I got the query to work the way I want it to work. The way I'm doing it, left or right join yields the same result.

I think I understand what Pat is saying about using the IIf() statement. Where would I put it in the SQL code? I've attached the db for your reference. Thx!

Code:
TRANSFORM Count(Table1.DateSold) AS CountOfDateSold
SELECT Table2.FlavorGroup
FROM Table2 RIGHT JOIN Table1 ON Table2.Flavor = Table1.Flavor
GROUP BY Table2.FlavorGroup
ORDER BY Table2.FlavorGroup
PIVOT Format([DateSold],"mmm yyyy") In ("Jan 2017","Feb 2017","Mar 2017","Apr 2017","May 2017","Jun 2017","Jul 2017","Aug 2017","Sep 2017","Oct 2017","Nov 2017","Dec 2017");
Attached Files
File Type: accdb Database1.accdb (1.71 MB, 33 views)
Harper is offline   Reply With Quote
Old 12-11-2017, 05:36 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Crosstab Query - Concatenate Rows & Columns

You are making a mistake with the crosstab query. Do you really want to create a new query every year? Do you really want to create new reports and forms to view that query? If the answer is NO - then you cannot hard-code year into the columns property. Relational databases are not spreadsheets. If you attempt to use them like spreadsheets, you will be verrrrry unhappy with Access. In a properly designed relational database, you will never be working with object names that contain variable data such as "2017".

I know its not as pretty but for now, just use the month name in the column header. Leave the Year as a prominent field in the header. This will save you from having to create a new query/report every year. Then, you would use a where clause that selects only data for a given year.

Select ...
From ...
Where Year(DateSold) = Forms!myformname!txtYear;

If Left and Right joins produce the same result, then in effect, you have an Inner Join. The way you built table2, you have to create an entry for every Flavor regardless of whether or not it belongs to a group. I don't know about Glaxion, but that wasn't what I suggested.

I modified the example
Attached Files
File Type: accdb Database1_Pat.accdb (576.0 KB, 26 views)

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Harper (12-11-2017)
Old 12-17-2017, 07:07 AM   #8
Harper
Newly Registered User
Silver Supporter
 
Join Date: Dec 2017
Posts: 17
Thanks: 11
Thanked 0 Times in 0 Posts
Harper is on a distinguished road
Re: Crosstab Query - Concatenate Rows & Columns

Hi Pat! I have 2 questions:

1. Is the purpose of the Nz function to return Table1.Flavor if Table2.FlavorGroup is null?

2. If there's a flavor, like Sherbet, that hasn't had any sales, then it wouldn't show in query. How do you force Sherbet to show in query?
Harper is offline   Reply With Quote
Old 12-17-2017, 08:42 AM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Crosstab Query - Concatenate Rows & Columns

1. The query does a left join between table1 and table2 because table 2 doesn't always have an entry. In order to get EITHER the Flavor name or the Flavor Group to be returned as the GroupName, the Nz is used. When there is no matchin row in table2, FlavorGroup would be null and so the Nz() will substitute Flavor from table1 so this field always has a value.
2. I modified the query to add additional criteria. The form limits the selected data to a single year so I added - Or Is Null so the query shows all rows from table1 if the date is in the year picked or if the date is null.

Open the query in design view and change the WHERE clause:

TRANSFORM Count(Table1.DateSold) AS CountOfDateSold
SELECT Nz(Table2.FlavorGroup,Table1.Flavor) AS GroupName, Year(table1.DateSold) AS SoldYear, Count([GroupName]) AS [Total Of Flavor]
FROM Table1 LEFT JOIN Table2 ON Table1.Flavor = Table2.Flavor
WHERE (((Year([DateSold]))=[forms]![form1]![cboYear] Or (Year([DateSold])) Is Null))
GROUP BY Nz(Table2.FlavorGroup,Table1.Flavor), Year(table1.DateSold)
ORDER BY Nz(Table2.FlavorGroup,Table1.Flavor), Year(table1.DateSold)
PIVOT Format([DateSold],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Harper (12-17-2017)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate Rows based on grouped query cprobertson1 Queries 3 07-18-2016 05:16 AM
Crosstab to convert rows to columns foxtrot123 Queries 1 09-16-2014 06:04 AM
Concatenate field into additional query rows jetersauce Queries 3 02-04-2012 04:30 PM
Showing all Rows & Columns in a crosstab query. coach.32 Queries 3 09-08-2011 11:04 PM
Crosstab Query with Multiple Rows & Columns manu Queries 0 05-01-2003 07:12 AM




All times are GMT -8. The time now is 04:01 AM.


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

Sponsored Links

How to advertise

Media Kit


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