Need help with building query similiar to cross tab design but not exactly fits MS Access criteria!

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
I want to do a cross tab report query in Microsoft Access but error message says won't let me.

The error when I try to go through the wizard is shown in this image attachment.
I've also included the Microsoft Access database that contains the data and the final Excel report I'm trying to build as an example.

Basically working off the query:  qDataCombined I want to build a report that shows whether or not a Tag (normalized in table:  usgaaptags) is assigned to a company (normalized as table:  usgaapcompanynames.  

The structure is described in the Excel and basically just puts a 'y' for the value of that Company.

Two questions:
How would I build a Microsoft Access query that would look exactly like my Excel attachment from this MS Access database?
Why does the crosstab query doesn't work in this situation?
ee-example-of-cross-tab-not-wokring.PNG
Final-report.xlsx
ee-tag-example.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Access used to have a Pivot table option, ...but it was deprecated.

You can do this with a Crosstab query in Access, ...but you need a field to "Summarize".

Try a query something like this:
TRANSFORM First(IIf(Len([TagName])>0,"Y","")) AS Yes
SELECT qDataCombined.tagname
FROM qDataCombined
GROUP BY qDataCombined.tagname
PIVOT qDataCombined.companyname;

Open in new window

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
I want to do a cross tab report query
Note:
You cannot really make this crosstab query into a true "Report", ...if that is your goal here,
...but the query itself looks like what you might be after.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
In order to get all of the combinations, I think you are going to have to start with a cartesian join between your companies and tags tables.  Make this into a subquery, and then join that to your linking table with an outer joint.  Finally, select your TagName as the Crosstabs RowHeader, CompanyName as the column header, and similar to what Jeff mentioned above, use an expression to determine whether there is  a record in the joining table for each combination.  Since you should only have one record in that table for each combination of companyId and tagID, you can then use the First aggregate as the Value.  Looks like:

TRANSFORM First(IIf(IsNull([JT].[TagID]) And IsNull([JT].[usgaapfilesid]),Null,"Y")) AS Expr1
SELECT CJ.Tagname
FROM (
SELECT usgaapcompanynames.usgaapfilesid, usgaapcompanynames.companyname, usgaaptags.tagid, usgaaptags.tagname
FROM usgaapcompanynames, usgaaptags
)  AS CJ 
LEFT JOIN usgaapfileswtags AS JT ON (CJ.tagid = JT.tagid) AND (CJ.usgaapfilesid = JT.usgaapfilesid)
GROUP BY CJ.Tagname
PIVOT CJ.companyname;

Open in new window


CJ alias stands for Cartesian Join
JT alias stands for Joining Table
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Wow guys.  I'm amazed.  Both work well.  
Jeffrey's works the fastest though!

The only thing I really need is a column at the end - that totals the number of "y"'s per tag.
How would I do that with above?
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
But Jeff's solution will only return those companies and tags that are included in your joining table.  So you will not get any empty columns or empty rows.  If you want to have all of the columns and all of the rows, you must use the Cartesian Join.

TRANSFORM First(IIf(IsNull([JT].[TagID]) And IsNull([JT].[usgaapfilesid]),Null,"Y")) AS Expr1
SELECT CJ.Tagname, Sum(IIf(IsNull([JT].[TagID]) And IsNull([JT].[usgaapfilesid]),0,1)) AS [Y's]
FROM (
SELECT usgaapcompanynames.usgaapfilesid, usgaapcompanynames.companyname, usgaaptags.tagid, usgaaptags.tagname
FROM usgaapcompanynames, usgaaptags
)  AS CJ 
LEFT JOIN usgaapfileswtags AS JT ON (CJ.usgaapfilesid = JT.usgaapfilesid) AND (CJ.tagid = JT.tagid)
GROUP BY CJ.Tagname
PIVOT CJ.companyname;

Open in new window

Author

Commented:
This is awesome - thank you both!

Always appreciate your comments, Dale.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial