Showing data Horizontally instead of Vertically in a Query?

%3CLINGO-SUB%20id%3D%22lingo-sub-1316073%22%20slang%3D%22en-US%22%3EShowing%20data%20Horizontally%20instead%20of%20Vertically%20in%20a%20Query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1316073%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20relatively%20new%20to%20Access%2C%20but%20decently%20familiar%20with%20Excel.%26nbsp%3B%20I've%20been%20attempting%20to%20have%20data%20from%20multiple%20tables%20show%20up%20together.%26nbsp%3B%20I've%20figured%20out%20the%20query%20needed%20to%20an%20extent.%26nbsp%3B%20However%20I'm%20trying%20to%20have%20the%20data%20show%20up%20Horizontally.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20-%20a%20person%20plants%20things%20in%20their%20yard.%26nbsp%3B%20A%20person%20plants%20multiple%20areas%20(front%2C%20side%2C%20back)%20with%20multiple%20items%20(bushes%2C%20trees%2C%20flowers%2C%20etc)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%20Access%20showing%2C%20each%20in%20their%20own%20row%20(%3CEM%3EVertically%3C%2FEM%3E%3A(%3C%2Fimg%3E%3C%2FP%3E%3CP%3EPerson%20-%20Front%20-%20Bushes%3C%2FP%3E%3CP%3EPerson%20-%20Side%20-%20Trees%3C%2FP%3E%3CP%3EPerson%20-%20Side%20-%20Flowers%3C%2FP%3E%3CP%3EPerson%20-%20Back%20-%20Bushes%3C%2FP%3E%3CP%3EPerson%20-%20Back%20-%20Flowers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20Access%20to%20show%20each%20%3CSTRONG%3E%3CEM%3EArea%20of%20the%20Yard%3C%2FEM%3E%3C%2FSTRONG%3E%20in%20its%20own%20Row%20(%3CEM%3EHorizontally%3C%2FEM%3E%3A(%3C%2Fimg%3E%3C%2FP%3E%3CP%3EPerson%20-%20Front%20-%20Shrubs%3C%2FP%3E%3CP%3EPerson%20-%20Side%20-%20Trees%20-%20Flowers%3C%2FP%3E%3CP%3EPerson%20-%20Back%20-%20Bushes%20-%20Flowers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20even%20possible%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1316073%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1317414%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20data%20Horizontally%20instead%20of%20Vertically%20in%20a%20Query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1317414%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F626320%22%20target%3D%22_blank%22%3E%40cutesmaj%3C%2FA%3E%26nbsp%3BWhat%20you%20need%20is%20a%20Crosstab%20query%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CrossTabQueryWizardtwo.png%22%20style%3D%22width%3A%20418px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F185118i69FAA88BAD123717%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22CrossTabQueryWizardtwo.png%22%20alt%3D%22CrossTabQueryWizardtwo.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1321982%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20data%20Horizontally%20instead%20of%20Vertically%20in%20a%20Query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321982%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F126074%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3BThank%20you%20I%20just%20tried%20a%20cross%20tab%20query%20per%20your%20suggestion.%26nbsp%3B%20Which%20is%20a%20nice%20feature%2C%20unfortunately%20it%20is%20giving%20me%20counts%20of%20the%20data%20points%20instead%20of%20the%20actual%20list.%26nbsp%3B%20Is%20there%20a%20selection%20that%20I%20need%20to%20choose%20instead%3F%26nbsp%3B%20I%20really%20appreciate%20your%20suggestion.%26nbsp%3B%20And%20it%20will%20help%20me%20in%20other%20areas%2C%20but%20sadly%20not%20this%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1322575%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20data%20Horizontally%20instead%20of%20Vertically%20in%20a%20Query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322575%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F626320%22%20target%3D%22_blank%22%3E%40cutesmaj%3C%2FA%3E%20Unfortunately%20Access%20does%20not%20have%20any%20built%20in%20functions%20to%20list%20out%20multiple%20values%20like%20you%20want%20to%20do.%26nbsp%3B%20You%20will%20need%20to%20create%20a%20custom%20function.%26nbsp%3B%20There%20is%20a%20good%20link%20to%20one%20at%26nbsp%3B%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%3CA%20href%3D%22http%3A%2F%2Fallenbrowne.com%2Ffunc-concat.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fallenbrowne.com%2Ffunc-concat.html%3C%2FA%3E%3C%2FFONT%3E%20that%20does%20what%20you%20are%20looking%20for.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I'm relatively new to Access, but decently familiar with Excel.  I've been attempting to have data from multiple tables show up together.  I've figured out the query needed to an extent.  However I'm trying to have the data show up Horizontally. 

 

Example - a person plants things in their yard.  A person plants multiple areas (front, side, back) with multiple items (bushes, trees, flowers, etc)

 

Instead of Access showing, each in their own row (Vertically:(

Person - Front - Bushes

Person - Side - Trees

Person - Side - Flowers

Person - Back - Bushes

Person - Back - Flowers

 

I'd like Access to show each Area of the Yard in its own Row (Horizontally:(

Person - Front - Shrubs

Person - Side - Trees - Flowers

Person - Back - Bushes - Flowers

 

Is this even possible?

3 Replies
Highlighted

@cutesmaj What you need is a Crosstab query:

CrossTabQueryWizardtwo.png

Highlighted

@George Hepworth Thank you I just tried a cross tab query per your suggestion.  Which is a nice feature, unfortunately it is giving me counts of the data points instead of the actual list.  Is there a selection that I need to choose instead?  I really appreciate your suggestion.  And it will help me in other areas, but sadly not this one.

Highlighted

@cutesmaj Unfortunately Access does not have any built in functions to list out multiple values like you want to do.  You will need to create a custom function.  There is a good link to one at http://allenbrowne.com/func-concat.html that does what you are looking for.