Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

Crosstab Query to Summarize Data Horizontally

This is a follow up to the question at the link below.

http://www.experts-exchange.com/Database/MS_Access/Q_28562271.html

I have my schema set up as stated in the above solution using the hybrid approach - one central table for the shared attributes and then EAV tables to describe the non-shared attributes.  In some cases, for certain forms and reports, I need to be able to display data as column headings that are displayed as row headings in this schema.  Instead of having the following information displayed for one property for example:

Type : Residential
Owner : John Smith
Year Built : 2005
Lot Size : 1 acre
Sewer :  Septic
Water : Well

I need to be able to display this data summarized for multiple properties like this :


                   Type                   :  Owner                    :  Year Built          :    Lot size    :    Sewer    :      Water
Property 1   Residential             John Smith                2005                     1 acre           Septic           Well
Property 2   Commercial            Bob Thomas             1987                     5 acres         Public           Public
Property 3   Residential             Mark Jones                2011                    .38 acre        Public           Well


I thought this was what a crosstab query was for but I can't get it to display the actual data elements.  So far it will only let me count, or sum the elements.  I want to actually display them.
0
yoducati
Asked:
yoducati
  • 17
  • 8
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
a copy of your tables/db will help..
0
 
yoducatiAuthor Commented:
This is the best I can do.  I can't post the full db. But these are the essential tables and query.  I need the qryTest to display the information as I mentioned above.
Sample.accdb
0
 
Rey Obrero (Capricorn1)Commented:
sorry,  i can't open your A2010 version db.
can you create a new db and just import the tables, query to the newly created db,
save db as Access 2003 database
then upload
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
yoducatiAuthor Commented:
It doesnt give me the option to save it as an older version.
0
 
yoducatiAuthor Commented:
Rather it prohibits me from saving in an older version.
0
 
Jeffrey CoachmanCommented:
Based on this specific question, ...are you quite sure the "Hybrid" approach was best?
What you are asking for here now seems to need another design methodology?

I am sure an expert here can "twist" the data to give you what you are after here, ...
...but sometimes it is more efficient to reconsider you design approach... (so hat this is not needed...

Lets see if either of the experts form your previous Q chimes in with more in depth insight...
0
 
Jeffrey CoachmanCommented:
If it were me, ...I would have separated the Vehicles from the Properties,...
But again, lets see if Jim or Pat Chimes in on this...
(I may not have a full grasp of your requirements...)
0
 
yoducatiAuthor Commented:
Nope not sure.  Working it all out still.  There are many more types than just vehicles and properties.  Those were just examples.  I went with the hybrid approach because it accomplished all I needed it to do very nicely when I though a crosstab query would let me reorganize for views/reporting.  Now I am considering a main table, with many other individual tables for each asset type.
0
 
Rey Obrero (Capricorn1)Commented:
see the crosstab query "qryTest_Crosstab"
Sample.accdb
0
 
yoducatiAuthor Commented:
Im not able to open it.  I get a pop up window with random characters in it when I click the link.  This happend to me before but I can't remember what the issue was.  Maybe the version?  Or it might need to be zipped?
0
 
Rey Obrero (Capricorn1)Commented:
do a right click and select save link as
or use another browser (chrome)
0
 
yoducatiAuthor Commented:
I dont have access to another browser.  Save as options aren't available.  Let me go back through my other questions and Ill see if I can figure out what happened last time.
0
 
yoducatiAuthor Commented:
The other ones had to be zipped in order for me to open them.  I think its a combination of the access version, and maybe my browser.
0
 
yoducatiAuthor Commented:
For what its worth I can't even open the sample database that I posted above.
0
 
Rey Obrero (Capricorn1)Commented:
export the 4 tables to excel (right click on table > export )

place all excel file in a folder, zipped the folder and upload
0
 
yoducatiAuthor Commented:
It wont let me upload them
0
 
Rey Obrero (Capricorn1)Commented:
upload all the excel files
0
 
yoducatiAuthor Commented:
0
 
yoducatiAuthor Commented:
0
 
yoducatiAuthor Commented:
0
 
yoducatiAuthor Commented:
0
 
yoducatiAuthor Commented:
It wouldnt let me do that before either.  I think its mostly my browser causing the issue.  I have no control over that though.  Its supposed to get updated soon.
0
 
Rey Obrero (Capricorn1)Commented:
post the SQL statement of your query "qryTest"
0
 
yoducatiAuthor Commented:
SELECT tblAssets.AssetID, tblAttributes.AttributeDescription, tblAssetAttributes.PValue
FROM tblAssetTypes INNER JOIN (tblAttributes INNER JOIN (tblAssets INNER JOIN tblAssetAttributes ON tblAssets.AssetID = tblAssetAttributes.AssetID) ON tblAttributes.AttributeID = tblAssetAttributes.[Attribute ID]) ON tblAssetTypes.TypeID = tblAttributes.TypeID;
0
 
yoducatiAuthor Commented:
Here is a pdf of the query results as is.  I need the asset ID as a row heading and they should be grouped by asset ID.  Each attribute description should be a column heading.  The PValue should be the data displayed for each asset ID.
qryTest.pdf
0
 
Rey Obrero (Capricorn1)Commented:
first create this query

SELECT tblAssets.AssetID, tblAttributes.AttributeDescription, tblAssetAttributes.PValue, tblAssetTypes.TypeDescription
FROM tblAssetTypes INNER JOIN (tblAttributes INNER JOIN (tblAssets INNER JOIN tblAssetAttributes ON tblAssets.AssetID = tblAssetAttributes.AssetID) ON tblAttributes.AttributeID = tblAssetAttributes.[Attribute ID]) ON tblAssetTypes.TypeID = tblAttributes.TypeID;

save as qryTest

then create this query

TRANSFORM First(qryTest.[PValue]) AS FirstOfPValue
SELECT qryTest.[AssetID], qryTest.[TypeDescription]
FROM qryTest
GROUP BY qryTest.[AssetID], qryTest.[TypeDescription]
PIVOT qryTest.[AttributeDescription];
0
 
yoducatiAuthor Commented:
Thanks for the help. This is exactly what I was trying to do but I didn't realize I needed two queries to do it.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 17
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now