Crosstab: show contents of field rather than sum? Steve Vincent 07.08.2006 20:41:02 Hi, I've been scouring the Crosstab postings hoping for help on my specific issue, but can't seem to find exactly the answer or the problem: I have a table of data that shows Companies, Cities and Products. I would like the report to be laid out crosstab-fashion with Cities as the Row headings, Products as the Column headings, but instead of data being summed or counted, I would like the Companies to be listed in the cells at the intersection of the Cities and Products. There will be more than one company in some cells; some will be empty. I'm looking for this kind of layout (I hope it transfers to the forum intact...). Is it possible to "substitute" the actual data in the fields, instead of a calculation, for the "data" area of the crosstab? Trying the same time in Excel with Pivot Tables, with similar (no) results. Products City Prod1 Prod2 Prod3 Prod4 Prod5 Los Angeles Company1 Company3 Company2 Company5 Company7 Company2 Company7 Company9 Seattle Company5 Company1 Company5 Company3 Company4 Company7 Company2 Company7 Company5 Company5 Company9 Pheonix Company3 Company3 Company3 Company3 Dallas Company2 Company1 Company1 Company2 Company2 Company6 Company2 Company3 Chicago Company8 Company7 Company7 Company8 Company8 Thanks in advance for any advice you can offer, Steve Vincent svincent[ at ]harbornet.com

Re: Crosstab: show contents of field rather than sum? "Duane Hookom" <DuaneAtNoSpanHookomDotNet> 07.08.2006 21:28:35 You can use the generic concatenate() function found at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane as the Value in your crosstab query. Set the Total: to First. -- Duane Hookom MS Access MVP "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message news:869EEA03-4D76-4509-9104-A52C986E3A29[ at ]microsoft.com... [Quoted Text] > Hi, I've been scouring the Crosstab postings hoping for help on my > specific > issue, but can't seem to find exactly the answer or the problem: > > I have a table of data that shows Companies, Cities and Products. I would > like the report to be laid out crosstab-fashion with Cities as the Row > headings, Products as the Column headings, but instead of data being > summed > or counted, I would like the Companies to be listed in the cells at the > intersection of the Cities and Products. There will be more than one > company > in some cells; some will be empty. I'm looking for this kind of layout (I > hope it transfers to the forum intact...). Is it possible to "substitute" > the actual data in the fields, instead of a calculation, for the "data" > area > of the crosstab? Trying the same time in Excel with Pivot Tables, with > similar (no) results. > > Products > City Prod1 Prod2 Prod3 Prod4 Prod5 > Los Angeles Company1 Company3 Company2 Company5 Company7 > Company2 Company7 Company9 > Seattle Company5 Company1 Company5 Company3 Company4 > Company7 Company2 Company7 Company5 > Company5 Company9 > Pheonix Company3 Company3 Company3 Company3 > Dallas Company2 Company1 Company1 Company2 Company2 > Company6 Company2 Company3 > Chicago Company8 Company7 Company7 > Company8 Company8 > > Thanks in advance for any advice you can offer, > Steve Vincent > svincent[ at ]harbornet.com >

Re: Crosstab: show contents of field rather than sum? Steve Vincent 07.08.2006 22:49:02 Duane, thanks to your information, I think I'm almost there... I have managed to get the "data" in the crosstab using the "First" total, but I'm not having much luck cobbling together a working Concatenate function. In my situation, all of my data is in the same "table" (actually, a query, but...), not drawing from different tables like yours. I am querying a simple query that contains City, Product, and Company. I want to show the one-to-many relationship of Product to Company, so for each Product (column) in each City (row), i'd like to show all of the Companies offering that product (table, totaled by "First" currently). I don't need to look outside this query/table for data; it is all contained in the data source. So I find myself trying to concatenate "Company" to "Company", but Access doesn't like my syntax so far. Any suggestions? Thanks so much for getting me this far. And, fantastic samples on your site! I will be back there for sure... "Duane Hookom" wrote: [Quoted Text] > You can use the generic concatenate() function found at > http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane as the > Value in your crosstab query. Set the Total: to First. > > -- > Duane Hookom > MS Access MVP > > "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message > news:869EEA03-4D76-4509-9104-A52C986E3A29[ at ]microsoft.com... > > Hi, I've been scouring the Crosstab postings hoping for help on my > > specific > > issue, but can't seem to find exactly the answer or the problem: > > > > I have a table of data that shows Companies, Cities and Products. I would > > like the report to be laid out crosstab-fashion with Cities as the Row > > headings, Products as the Column headings, but instead of data being > > summed > > or counted, I would like the Companies to be listed in the cells at the > > intersection of the Cities and Products. There will be more than one > > company > > in some cells; some will be empty. I'm looking for this kind of layout (I > > hope it transfers to the forum intact...). Is it possible to "substitute" > > the actual data in the fields, instead of a calculation, for the "data" > > area > > of the crosstab? Trying the same time in Excel with Pivot Tables, with > > similar (no) results. > > > > Products > > City Prod1 Prod2 Prod3 Prod4 Prod5 > > Los Angeles Company1 Company3 Company2 Company5 Company7 > > Company2 Company7 Company9 > > Seattle Company5 Company1 Company5 Company3 Company4 > > Company7 Company2 Company7 Company5 > > Company5 Company9 > > Pheonix Company3 Company3 Company3 Company3 > > Dallas Company2 Company1 Company1 Company2 Company2 > > Company6 Company2 Company3 > > Chicago Company8 Company7 Company7 > > Company8 Company8 > > > > Thanks in advance for any advice you can offer, > > Steve Vincent > > svincent[ at ]harbornet.com > > > > >

Re: Crosstab: show contents of field rather than sum? "Duane Hookom" <DuaneAtNoSpanHookomDotNet> 08.08.2006 03:11:57 It would help to know the exact query and field names however: TheValue: Concatenate("SELECT Company FROM qryYours WHERE City=""" & [City] & """ AND Product=""" & [Product] & """") -- Duane Hookom MS Access MVP "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message news:8C3B4752-B8F2-47C4-A953-87C21DDAD23F[ at ]microsoft.com... [Quoted Text] > Duane, thanks to your information, I think I'm almost there... I have > managed > to get the "data" in the crosstab using the "First" total, but I'm not > having > much luck cobbling together a working Concatenate function. In my > situation, > all of my data is in the same "table" (actually, a query, but...), not > drawing from different tables like yours. > > I am querying a simple query that contains City, Product, and Company. I > want to show the one-to-many relationship of Product to Company, so for > each > Product (column) in each City (row), i'd like to show all of the Companies > offering that product (table, totaled by "First" currently). I don't need > to > look outside this query/table for data; it is all contained in the data > source. So I find myself trying to concatenate "Company" to "Company", > but > Access doesn't like my syntax so far. > > Any suggestions? Thanks so much for getting me this far. And, fantastic > samples on your site! I will be back there for sure... > > > "Duane Hookom" wrote: > >> You can use the generic concatenate() function found at >> http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane as the >> Value in your crosstab query. Set the Total: to First. >> >> -- >> Duane Hookom >> MS Access MVP >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message >> news:869EEA03-4D76-4509-9104-A52C986E3A29[ at ]microsoft.com... >> > Hi, I've been scouring the Crosstab postings hoping for help on my >> > specific >> > issue, but can't seem to find exactly the answer or the problem: >> > >> > I have a table of data that shows Companies, Cities and Products. I >> > would >> > like the report to be laid out crosstab-fashion with Cities as the Row >> > headings, Products as the Column headings, but instead of data being >> > summed >> > or counted, I would like the Companies to be listed in the cells at the >> > intersection of the Cities and Products. There will be more than one >> > company >> > in some cells; some will be empty. I'm looking for this kind of layout >> > (I >> > hope it transfers to the forum intact...). Is it possible to >> > "substitute" >> > the actual data in the fields, instead of a calculation, for the "data" >> > area >> > of the crosstab? Trying the same time in Excel with Pivot Tables, with >> > similar (no) results. >> > >> > Products >> > City Prod1 Prod2 Prod3 Prod4 Prod5 >> > Los Angeles Company1 Company3 Company2 Company5 Company7 >> > Company2 Company7 Company9 >> > Seattle Company5 Company1 Company5 Company3 Company4 >> > Company7 Company2 Company7 Company5 >> > Company5 Company9 >> > Pheonix Company3 Company3 Company3 Company3 >> > Dallas Company2 Company1 Company1 Company2 Company2 >> > Company6 Company2 Company3 >> > Chicago Company8 Company7 Company7 >> > Company8 Company8 >> > >> > Thanks in advance for any advice you can offer, >> > Steve Vincent >> > svincent[ at ]harbornet.com >> > >> >> >>

Re: Crosstab: show contents of field rather than sum? Steve Vincent 18.08.2006 23:01:02 Duane, I'm getting closer. Here are the actual names of the objects and fields: I have a query named MatrixTestQuery, containing three fields: City, CompanyAbbreviation, and ProductLineName. I used your Concatenate function, like this: Company: Concatenate("SELECT CompanyAbbreviation FROM MatrixTestQuery WHERE City=""" & [City] & """ AND ProductLineName=""" & [ProductLineName] & """") The whole crosstab query looks like this in SQL (although I set it up in the QBE grid): TRANSFORM First(MatrixTestQuery.CompanyAbbreviation) AS [FirstOfCompany Abbreviation] SELECT MatrixTestQuery.City, First(MatrixTestQuery.CompanyAbbreviation) AS [Total Of Company Abbreviation] FROM MatrixTestQuery GROUP BY MatrixTestQuery.City, Concatenate("SELECT CompanyAbbreviation FROM MatrixTestQuery WHERE City=""" & [City] & """ AND ProductLineName=""" & [ProductLineName] & """") PIVOT MatrixTestQuery.ProductLineName; I have succeeded in running the crosstab query without any errors, but rather than giving me all of the CompanyAbbreviation's concatenated into a single cell, instead I get a separate row for each City, like this: City Cardiac Invasive/Interventional Cardiac Medical Cardiothoracic Surgery Cardiovascular OP Diagnostics MRI Vascular Auburn ARMC Auburn ARMC Auburn ARMC Auburn ARMC Bremerton HMC HMC HMC Bremerton HMC Burien HLMC Federal Way CC Federal Way CDI Federal Way SFH Federal Way SFH Gig Harbor CVA Gig Harbor CVA I would like the data (CompanyAbbreviation) for each City summarized in one row/cell under a column headed with ProductLineName, like I see in your examples on your website. Can you see what I'm doing wrong? Sorry I'm not very savvy with SQL or VB. When this works, I will have learned a lot. Thanks in advance, Steve Vincent svincent[ at ]harbornet.com "Duane Hookom" wrote: [Quoted Text] > It would help to know the exact query and field names however: > TheValue: Concatenate("SELECT Company FROM qryYours WHERE City=""" & [City] > & """ AND Product=""" & [Product] & """") > > > -- > Duane Hookom > MS Access MVP > > "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message > news:8C3B4752-B8F2-47C4-A953-87C21DDAD23F[ at ]microsoft.com... > > Duane, thanks to your information, I think I'm almost there... I have > > managed > > to get the "data" in the crosstab using the "First" total, but I'm not > > having > > much luck cobbling together a working Concatenate function. In my > > situation, > > all of my data is in the same "table" (actually, a query, but...), not > > drawing from different tables like yours. > > > > I am querying a simple query that contains City, Product, and Company. I > > want to show the one-to-many relationship of Product to Company, so for > > each > > Product (column) in each City (row), i'd like to show all of the Companies > > offering that product (table, totaled by "First" currently). I don't need > > to > > look outside this query/table for data; it is all contained in the data > > source. So I find myself trying to concatenate "Company" to "Company", > > but > > Access doesn't like my syntax so far. > > > > Any suggestions? Thanks so much for getting me this far. And, fantastic > > samples on your site! I will be back there for sure... > > > > > > "Duane Hookom" wrote: > > > >> You can use the generic concatenate() function found at > >> http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane as the > >> Value in your crosstab query. Set the Total: to First. > >> > >> -- > >> Duane Hookom > >> MS Access MVP > >> > >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message > >> news:869EEA03-4D76-4509-9104-A52C986E3A29[ at ]microsoft.com... > >> > Hi, I've been scouring the Crosstab postings hoping for help on my > >> > specific > >> > issue, but can't seem to find exactly the answer or the problem: > >> > > >> > I have a table of data that shows Companies, Cities and Products. I > >> > would > >> > like the report to be laid out crosstab-fashion with Cities as the Row > >> > headings, Products as the Column headings, but instead of data being > >> > summed > >> > or counted, I would like the Companies to be listed in the cells at the > >> > intersection of the Cities and Products. There will be more than one > >> > company > >> > in some cells; some will be empty. I'm looking for this kind of layout > >> > (I > >> > hope it transfers to the forum intact...). Is it possible to > >> > "substitute" > >> > the actual data in the fields, instead of a calculation, for the "data" > >> > area > >> > of the crosstab? Trying the same time in Excel with Pivot Tables, with > >> > similar (no) results. > >> > > >> > Products > >> > City Prod1 Prod2 Prod3 Prod4 Prod5 > >> > Los Angeles Company1 Company3 Company2 Company5 Company7 > >> > Company2 Company7 Company9 > >> > Seattle Company5 Company1 Company5 Company3 Company4 > >> > Company7 Company2 Company7 Company5 > >> > Company5 Company9 > >> > Pheonix Company3 Company3 Company3 Company3 > >> > Dallas Company2 Company1 Company1 Company2 Company2 > >> > Company6 Company2 Company3 > >> > Chicago Company8 Company7 Company7 > >> > Company8 Company8 > >> > > >> > Thanks in advance for any advice you can offer, > >> > Steve Vincent > >> > svincent[ at ]harbornet.com > >> > > >> > >> > >> > > >

Re: Crosstab: show contents of field rather than sum? "Duane Hookom" <DuaneAtNoSpanHookomDotNet> 19.08.2006 02:27:35 As per my previous post, you need to place the Concatenate() function in the value so it should be like TRANSFORM First(Concatenate(....)) as TheValue -- Duane Hookom MS Access MVP "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message news:771D43B4-7540-43EF-BD08-A8C0E0A5C1B9[ at ]microsoft.com... [Quoted Text] > Duane, I'm getting closer. Here are the actual names of the objects and > fields: > > I have a query named MatrixTestQuery, > containing three fields: City, CompanyAbbreviation, and ProductLineName. > I used your Concatenate function, like this: > > Company: Concatenate("SELECT CompanyAbbreviation FROM MatrixTestQuery > WHERE > City=""" & [City] & """ AND ProductLineName=""" & [ProductLineName] & > """") > > The whole crosstab query looks like this in SQL (although I set it up in > the > QBE grid): > > TRANSFORM First(MatrixTestQuery.CompanyAbbreviation) AS [FirstOfCompany > Abbreviation] > SELECT MatrixTestQuery.City, First(MatrixTestQuery.CompanyAbbreviation) AS > [Total Of Company Abbreviation] > FROM MatrixTestQuery > GROUP BY MatrixTestQuery.City, Concatenate("SELECT CompanyAbbreviation > FROM > MatrixTestQuery WHERE City=""" & [City] & """ AND ProductLineName=""" & > [ProductLineName] & """") > PIVOT MatrixTestQuery.ProductLineName; > > I have succeeded in running the crosstab query without any errors, but > rather than giving me all of the CompanyAbbreviation's concatenated into a > single cell, instead I get a separate row for each City, like this: > > City Cardiac Invasive/Interventional Cardiac Medical Cardiothoracic > Surgery Cardiovascular OP Diagnostics MRI Vascular > Auburn ARMC > Auburn ARMC > Auburn ARMC > Auburn ARMC > Bremerton HMC HMC HMC > Bremerton HMC > Burien HLMC > Federal Way CC > Federal Way CDI > Federal Way SFH > Federal Way SFH > Gig Harbor CVA > Gig Harbor CVA > > I would like the data (CompanyAbbreviation) for each City summarized in > one > row/cell under a column headed with ProductLineName, like I see in your > examples on your website. > > Can you see what I'm doing wrong? Sorry I'm not very savvy with SQL or VB. > When this works, I will have learned a lot. > Thanks in advance, > Steve Vincent > svincent[ at ]harbornet.com > > > > > > "Duane Hookom" wrote: > >> It would help to know the exact query and field names however: >> TheValue: Concatenate("SELECT Company FROM qryYours WHERE City=""" & >> [City] >> & """ AND Product=""" & [Product] & """") >> >> >> -- >> Duane Hookom >> MS Access MVP >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message >> news:8C3B4752-B8F2-47C4-A953-87C21DDAD23F[ at ]microsoft.com... >> > Duane, thanks to your information, I think I'm almost there... I have >> > managed >> > to get the "data" in the crosstab using the "First" total, but I'm not >> > having >> > much luck cobbling together a working Concatenate function. In my >> > situation, >> > all of my data is in the same "table" (actually, a query, but...), not >> > drawing from different tables like yours. >> > >> > I am querying a simple query that contains City, Product, and Company. >> > I >> > want to show the one-to-many relationship of Product to Company, so for >> > each >> > Product (column) in each City (row), i'd like to show all of the >> > Companies >> > offering that product (table, totaled by "First" currently). I don't >> > need >> > to >> > look outside this query/table for data; it is all contained in the data >> > source. So I find myself trying to concatenate "Company" to "Company", >> > but >> > Access doesn't like my syntax so far. >> > >> > Any suggestions? Thanks so much for getting me this far. And, >> > fantastic >> > samples on your site! I will be back there for sure... >> > >> > >> > "Duane Hookom" wrote: >> > >> >> You can use the generic concatenate() function found at >> >> http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane as >> >> the >> >> Value in your crosstab query. Set the Total: to First. >> >> >> >> -- >> >> Duane Hookom >> >> MS Access MVP >> >> >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in >> >> message >> >> news:869EEA03-4D76-4509-9104-A52C986E3A29[ at ]microsoft.com... >> >> > Hi, I've been scouring the Crosstab postings hoping for help on my >> >> > specific >> >> > issue, but can't seem to find exactly the answer or the problem: >> >> > >> >> > I have a table of data that shows Companies, Cities and Products. I >> >> > would >> >> > like the report to be laid out crosstab-fashion with Cities as the >> >> > Row >> >> > headings, Products as the Column headings, but instead of data being >> >> > summed >> >> > or counted, I would like the Companies to be listed in the cells at >> >> > the >> >> > intersection of the Cities and Products. There will be more than >> >> > one >> >> > company >> >> > in some cells; some will be empty. I'm looking for this kind of >> >> > layout >> >> > (I >> >> > hope it transfers to the forum intact...). Is it possible to >> >> > "substitute" >> >> > the actual data in the fields, instead of a calculation, for the >> >> > "data" >> >> > area >> >> > of the crosstab? Trying the same time in Excel with Pivot Tables, >> >> > with >> >> > similar (no) results. >> >> > >> >> > Products >> >> > City Prod1 Prod2 Prod3 Prod4 Prod5 >> >> > Los Angeles Company1 Company3 Company2 Company5 Company7 >> >> > Company2 Company7 Company9 >> >> > Seattle Company5 Company1 Company5 Company3 Company4 >> >> > Company7 Company2 Company7 Company5 >> >> > Company5 Company9 >> >> > Pheonix Company3 Company3 Company3 Company3 >> >> > Dallas Company2 Company1 Company1 Company2 Company2 >> >> > Company6 Company2 Company3 >> >> > Chicago Company8 Company7 Company7 >> >> > Company8 Company8 >> >> > >> >> > Thanks in advance for any advice you can offer, >> >> > Steve Vincent >> >> > svincent[ at ]harbornet.com >> >> > >> >> >> >> >> >> >> >> >>

Re: Crosstab: show contents of field rather than sum? Steve Vincent 21.08.2006 17:01:03 Duane, I think I'm close, but now I get a Run-time Error '3061' , "Too few parameters. Expected 2." When I run debug, it highlights the following line from the Concatenate module: Set rs = db.OpenRecordset(pstrSQL) Here is how I now have the crosstab query set up (in the QBE grid): Column 1: Field: City Total: Group By Crosstab: Row Heading Column 2: Field: Company: Concatenate("Select Company_Abbreviation FROM MatrixTestQuery WHERE City=""" & [City] & """AND Product_Line_Name=""" & [ProductLineName] & """") Total: First Crosstab: Value Column 3: Field: ProductLineName Total: Group By Crosstab: Column Heading And for the record, the SQL statement is: TRANSFORM First(Concatenate("Select Company_Abbreviation FROM MatrixTestQuery WHERE City=""" & [City] & """AND Product_Line_Name=""" & [ProductLineName] & """")) AS Company SELECT MatrixTestQuery.City FROM MatrixTestQuery GROUP BY MatrixTestQuery.City PIVOT MatrixTestQuery.ProductLineName; Thank you again for taking the time to help troubleshoot this for me. Steve Vincent svincent[ at ]harbornet.com "Duane Hookom" wrote: [Quoted Text] > As per my previous post, you need to place the Concatenate() function in the > value so it should be like > TRANSFORM First(Concatenate(....)) as TheValue > > -- > Duane Hookom > MS Access MVP > > "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message > news:771D43B4-7540-43EF-BD08-A8C0E0A5C1B9[ at ]microsoft.com... > > Duane, I'm getting closer. Here are the actual names of the objects and > > fields: > > > > I have a query named MatrixTestQuery, > > containing three fields: City, CompanyAbbreviation, and ProductLineName. > > I used your Concatenate function, like this: > > > > Company: Concatenate("SELECT CompanyAbbreviation FROM MatrixTestQuery > > WHERE > > City=""" & [City] & """ AND ProductLineName=""" & [ProductLineName] & > > """") > > > > The whole crosstab query looks like this in SQL (although I set it up in > > the > > QBE grid): > > > > TRANSFORM First(MatrixTestQuery.CompanyAbbreviation) AS [FirstOfCompany > > Abbreviation] > > SELECT MatrixTestQuery.City, First(MatrixTestQuery.CompanyAbbreviation) AS > > [Total Of Company Abbreviation] > > FROM MatrixTestQuery > > GROUP BY MatrixTestQuery.City, Concatenate("SELECT CompanyAbbreviation > > FROM > > MatrixTestQuery WHERE City=""" & [City] & """ AND ProductLineName=""" & > > [ProductLineName] & """") > > PIVOT MatrixTestQuery.ProductLineName; > > > > I have succeeded in running the crosstab query without any errors, but > > rather than giving me all of the CompanyAbbreviation's concatenated into a > > single cell, instead I get a separate row for each City, like this: > > > > City Cardiac Invasive/Interventional Cardiac Medical Cardiothoracic > > Surgery Cardiovascular OP Diagnostics MRI Vascular > > Auburn ARMC > > Auburn ARMC > > Auburn ARMC > > Auburn ARMC > > Bremerton HMC HMC HMC > > Bremerton HMC > > Burien HLMC > > Federal Way CC > > Federal Way CDI > > Federal Way SFH > > Federal Way SFH > > Gig Harbor CVA > > Gig Harbor CVA > > > > I would like the data (CompanyAbbreviation) for each City summarized in > > one > > row/cell under a column headed with ProductLineName, like I see in your > > examples on your website. > > > > Can you see what I'm doing wrong? Sorry I'm not very savvy with SQL or VB. > > When this works, I will have learned a lot. > > Thanks in advance, > > Steve Vincent > > svincent[ at ]harbornet.com > > > > > > > > > > > > "Duane Hookom" wrote: > > > >> It would help to know the exact query and field names however: > >> TheValue: Concatenate("SELECT Company FROM qryYours WHERE City=""" & > >> [City] > >> & """ AND Product=""" & [Product] & """") > >> > >> > >> -- > >> Duane Hookom > >> MS Access MVP > >> > >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message > >> news:8C3B4752-B8F2-47C4-A953-87C21DDAD23F[ at ]microsoft.com... > >> > Duane, thanks to your information, I think I'm almost there... I have > >> > managed > >> > to get the "data" in the crosstab using the "First" total, but I'm not > >> > having > >> > much luck cobbling together a working Concatenate function. In my > >> > situation, > >> > all of my data is in the same "table" (actually, a query, but...), not > >> > drawing from different tables like yours. > >> > > >> > I am querying a simple query that contains City, Product, and Company. > >> > I > >> > want to show the one-to-many relationship of Product to Company, so for > >> > each > >> > Product (column) in each City (row), i'd like to show all of the > >> > Companies > >> > offering that product (table, totaled by "First" currently). I don't > >> > need > >> > to > >> > look outside this query/table for data; it is all contained in the data > >> > source. So I find myself trying to concatenate "Company" to "Company", > >> > but > >> > Access doesn't like my syntax so far. > >> > > >> > Any suggestions? Thanks so much for getting me this far. And, > >> > fantastic > >> > samples on your site! I will be back there for sure... > >> > > >> > > >> > "Duane Hookom" wrote: > >> > > >> >> You can use the generic concatenate() function found at > >> >> http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane as > >> >> the > >> >> Value in your crosstab query. Set the Total: to First. > >> >> > >> >> -- > >> >> Duane Hookom > >> >> MS Access MVP > >> >> > >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in > >> >> message > >> >> news:869EEA03-4D76-4509-9104-A52C986E3A29[ at ]microsoft.com... > >> >> > Hi, I've been scouring the Crosstab postings hoping for help on my > >> >> > specific > >> >> > issue, but can't seem to find exactly the answer or the problem: > >> >> > > >> >> > I have a table of data that shows Companies, Cities and Products. I > >> >> > would > >> >> > like the report to be laid out crosstab-fashion with Cities as the > >> >> > Row > >> >> > headings, Products as the Column headings, but instead of data being > >> >> > summed > >> >> > or counted, I would like the Companies to be listed in the cells at > >> >> > the > >> >> > intersection of the Cities and Products. There will be more than > >> >> > one > >> >> > company > >> >> > in some cells; some will be empty. I'm looking for this kind of > >> >> > layout > >> >> > (I > >> >> > hope it transfers to the forum intact...). Is it possible to > >> >> > "substitute" > >> >> > the actual data in the fields, instead of a calculation, for the > >> >> > "data" > >> >> > area > >> >> > of the crosstab? Trying the same time in Excel with Pivot Tables, > >> >> > with > >> >> > similar (no) results. > >> >> > > >> >> > Products > >> >> > City Prod1 Prod2 Prod3 Prod4 Prod5 > >> >> > Los Angeles Company1 Company3 Company2 Company5 Company7 > >> >> > Company2 Company7 Company9 > >> >> > Seattle Company5 Company1 Company5 Company3 Company4 > >> >> > Company7 Company2 Company7 Company5 > >> >> > Company5 Company9 > >> >> > Pheonix Company3 Company3 Company3 Company3 > >> >> > Dallas Company2 Company1 Company1 Company2 Company2 > >> >> > Company6 Company2 Company3 > >> >> > Chicago Company8 Company7 Company7 > >> >> > Company8 Company8 > >> >> > > >> >> > Thanks in advance for any advice you can offer, > >> >> > Steve Vincent > >> >> > svincent[ at ]harbornet.com > >> >> > > >> >> > >> >> > >> >> > >> > >> > >> > > >

Re: Crosstab: show contents of field rather than sum? "Duane Hookom" <DuaneAtNoSpanHookomDotNet> 21.08.2006 19:23:35 I think you have used the wrong field names in the call to the Concatenate function. Are your field names in MatrixTestQuery Company_Abbreviation, City, and Product_Line_Names? -- Duane Hookom MS Access MVP "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message news:2E7EFFF5-25FB-4B13-A1F0-DB32AB9CB3C8[ at ]microsoft.com... [Quoted Text] > Duane, > > I think I'm close, but now I get a Run-time Error '3061' , "Too few > parameters. Expected 2." When I run debug, it highlights the following > line > from the Concatenate module: > > Set rs = db.OpenRecordset(pstrSQL) > > Here is how I now have the crosstab query set up (in the QBE grid): > > Column 1: > Field: City > Total: Group By > Crosstab: Row Heading > > Column 2: > Field: Company: Concatenate("Select Company_Abbreviation FROM > MatrixTestQuery WHERE City=""" & [City] & """AND Product_Line_Name=""" & > [ProductLineName] & """") > Total: First > Crosstab: Value > > Column 3: > Field: ProductLineName > Total: Group By > Crosstab: Column Heading > > And for the record, the SQL statement is: > TRANSFORM First(Concatenate("Select Company_Abbreviation FROM > MatrixTestQuery WHERE City=""" & [City] & """AND Product_Line_Name=""" & > [ProductLineName] & """")) AS Company > SELECT MatrixTestQuery.City > FROM MatrixTestQuery > GROUP BY MatrixTestQuery.City > PIVOT MatrixTestQuery.ProductLineName; > > Thank you again for taking the time to help troubleshoot this for me. > > Steve Vincent > svincent[ at ]harbornet.com > > > > "Duane Hookom" wrote: > >> As per my previous post, you need to place the Concatenate() function in >> the >> value so it should be like >> TRANSFORM First(Concatenate(....)) as TheValue >> >> -- >> Duane Hookom >> MS Access MVP >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message >> news:771D43B4-7540-43EF-BD08-A8C0E0A5C1B9[ at ]microsoft.com... >> > Duane, I'm getting closer. Here are the actual names of the objects >> > and >> > fields: >> > >> > I have a query named MatrixTestQuery, >> > containing three fields: City, CompanyAbbreviation, and >> > ProductLineName. >> > I used your Concatenate function, like this: >> > >> > Company: Concatenate("SELECT CompanyAbbreviation FROM MatrixTestQuery >> > WHERE >> > City=""" & [City] & """ AND ProductLineName=""" & [ProductLineName] & >> > """") >> > >> > The whole crosstab query looks like this in SQL (although I set it up >> > in >> > the >> > QBE grid): >> > >> > TRANSFORM First(MatrixTestQuery.CompanyAbbreviation) AS [FirstOfCompany >> > Abbreviation] >> > SELECT MatrixTestQuery.City, First(MatrixTestQuery.CompanyAbbreviation) >> > AS >> > [Total Of Company Abbreviation] >> > FROM MatrixTestQuery >> > GROUP BY MatrixTestQuery.City, Concatenate("SELECT CompanyAbbreviation >> > FROM >> > MatrixTestQuery WHERE City=""" & [City] & """ AND ProductLineName=""" & >> > [ProductLineName] & """") >> > PIVOT MatrixTestQuery.ProductLineName; >> > >> > I have succeeded in running the crosstab query without any errors, but >> > rather than giving me all of the CompanyAbbreviation's concatenated >> > into a >> > single cell, instead I get a separate row for each City, like this: >> > >> > City Cardiac Invasive/Interventional Cardiac Medical Cardiothoracic >> > Surgery Cardiovascular OP Diagnostics MRI Vascular >> > Auburn ARMC >> > Auburn ARMC >> > Auburn ARMC >> > Auburn ARMC >> > Bremerton HMC HMC HMC >> > Bremerton HMC >> > Burien HLMC >> > Federal Way CC >> > Federal Way CDI >> > Federal Way SFH >> > Federal Way SFH >> > Gig Harbor CVA >> > Gig Harbor CVA >> > >> > I would like the data (CompanyAbbreviation) for each City summarized in >> > one >> > row/cell under a column headed with ProductLineName, like I see in your >> > examples on your website. >> > >> > Can you see what I'm doing wrong? Sorry I'm not very savvy with SQL or >> > VB. >> > When this works, I will have learned a lot. >> > Thanks in advance, >> > Steve Vincent >> > svincent[ at ]harbornet.com >> > >> > >> > >> > >> > >> > "Duane Hookom" wrote: >> > >> >> It would help to know the exact query and field names however: >> >> TheValue: Concatenate("SELECT Company FROM qryYours WHERE City=""" & >> >> [City] >> >> & """ AND Product=""" & [Product] & """") >> >> >> >> >> >> -- >> >> Duane Hookom >> >> MS Access MVP >> >> >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in >> >> message >> >> news:8C3B4752-B8F2-47C4-A953-87C21DDAD23F[ at ]microsoft.com... >> >> > Duane, thanks to your information, I think I'm almost there... I >> >> > have >> >> > managed >> >> > to get the "data" in the crosstab using the "First" total, but I'm >> >> > not >> >> > having >> >> > much luck cobbling together a working Concatenate function. In my >> >> > situation, >> >> > all of my data is in the same "table" (actually, a query, but...), >> >> > not >> >> > drawing from different tables like yours. >> >> > >> >> > I am querying a simple query that contains City, Product, and >> >> > Company. >> >> > I >> >> > want to show the one-to-many relationship of Product to Company, so >> >> > for >> >> > each >> >> > Product (column) in each City (row), i'd like to show all of the >> >> > Companies >> >> > offering that product (table, totaled by "First" currently). I >> >> > don't >> >> > need >> >> > to >> >> > look outside this query/table for data; it is all contained in the >> >> > data >> >> > source. So I find myself trying to concatenate "Company" to >> >> > "Company", >> >> > but >> >> > Access doesn't like my syntax so far. >> >> > >> >> > Any suggestions? Thanks so much for getting me this far. And, >> >> > fantastic >> >> > samples on your site! I will be back there for sure... >> >> > >> >> > >> >> > "Duane Hookom" wrote: >> >> > >> >> >> You can use the generic concatenate() function found at >> >> >> http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane >> >> >> as >> >> >> the >> >> >> Value in your crosstab query. Set the Total: to First. >> >> >> >> >> >> -- >> >> >> Duane Hookom >> >> >> MS Access MVP >> >> >> >> >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in >> >> >> message >> >> >> news:869EEA03-4D76-4509-9104-A52C986E3A29[ at ]microsoft.com... >> >> >> > Hi, I've been scouring the Crosstab postings hoping for help on >> >> >> > my >> >> >> > specific >> >> >> > issue, but can't seem to find exactly the answer or the problem: >> >> >> > >> >> >> > I have a table of data that shows Companies, Cities and Products. >> >> >> > I >> >> >> > would >> >> >> > like the report to be laid out crosstab-fashion with Cities as >> >> >> > the >> >> >> > Row >> >> >> > headings, Products as the Column headings, but instead of data >> >> >> > being >> >> >> > summed >> >> >> > or counted, I would like the Companies to be listed in the cells >> >> >> > at >> >> >> > the >> >> >> > intersection of the Cities and Products. There will be more than >> >> >> > one >> >> >> > company >> >> >> > in some cells; some will be empty. I'm looking for this kind of >> >> >> > layout >> >> >> > (I >> >> >> > hope it transfers to the forum intact...). Is it possible to >> >> >> > "substitute" >> >> >> > the actual data in the fields, instead of a calculation, for the >> >> >> > "data" >> >> >> > area >> >> >> > of the crosstab? Trying the same time in Excel with Pivot >> >> >> > Tables, >> >> >> > with >> >> >> > similar (no) results. >> >> >> > >> >> >> > Products >> >> >> > City Prod1 Prod2 Prod3 Prod4 Prod5 >> >> >> > Los Angeles Company1 Company3 Company2 Company5 Company7 >> >> >> > Company2 Company7 Company9 >> >> >> > Seattle Company5 Company1 Company5 Company3 Company4 >> >> >> > Company7 Company2 Company7 Company5 >> >> >> > Company5 Company9 >> >> >> > Pheonix Company3 Company3 Company3 Company3 >> >> >> > Dallas Company2 Company1 Company1 Company2 Company2 >> >> >> > Company6 Company2 Company3 >> >> >> > Chicago Company8 Company7 Company7 >> >> >> > Company8 Company8 >> >> >> > >> >> >> > Thanks in advance for any advice you can offer, >> >> >> > Steve Vincent >> >> >> > svincent[ at ]harbornet.com >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>

Re: Crosstab: show contents of field rather than sum? Steve Vincent 21.08.2006 21:35:06 I have renamed them CompanyAbbreviation and ProductLineName, to get rid of the spaces to be more VB-friendly (don't worry... i changed their references in underlying queries, etc.). I have actually experimented with calling the CompanyAbbreviation from the underlying CompanyInfo table by its CompanyID number, rather than referencing the same query that I'm querying. I'm thinking that I gave my query some kind of circular reference. I have tried to make the syntax as close to your online family database examples as possible. I'm getting good results, no errors... but still not getting the multiple CompanyAbbreviation "listings" in the crosstab query results. I've tried the concatenate statement in a normal select query with no errors, but also no grouping/listing like I'm hoping for. I don't really get what I'm looking for by grouping and layout on the Report level -- I really think I need it to be in a Crosstab query. Thanks for sticking with me... i'm really trying to do it as much on my own as possible, studying and following your online queries. If you don't mind, here's my latest attempt. I'll try to give you the territory here as simply as possible: I'm crosstab-querying a simple select query named MatrixTestQuery which now has four fields: City, ProductLineName, and CompanyAbbreviation, and CompanyID. CompanyID, CompanyAbbreviation and City are all from the CompanyInfoTable. ProductLineName is from the ProductLineTable. I have set up the Crosstab like this: Rows: [City] Columnns: [ProductLineName] Value: Expr1: First(Concatenate("Select CompanyAbbreviation FROM CompanyInfoTable WHERE CompanyID=" & [CompanyID] & " ORDER BY CompanyAbbreviation")) If I "Total" by "Expression" or "Where", then I just get one entry in each cell, not a concatenated "list" or "group". If I total by "First", like you suggest, I get the error message: "Cannot have an aggregate function in expression..." (and then it gives the Concatenate function/formula). If I remove the extra "First" from the expression, then I get the same one-CompanyAbbreviation-per-cell result as when I use "Expression" or "Where". I think I'm pretty close here, probably just missing a quote mark or two. Sorry for dragging this out, but I think we're on the verge of a breakthrough here, Duane (i hope, for your sake!). "Duane Hookom" wrote: [Quoted Text] > I think you have used the wrong field names in the call to the Concatenate > function. Are your field names in MatrixTestQuery Company_Abbreviation, > City, and Product_Line_Names? > > > -- > Duane Hookom > MS Access MVP > > "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message > news:2E7EFFF5-25FB-4B13-A1F0-DB32AB9CB3C8[ at ]microsoft.com... > > Duane, > > > > I think I'm close, but now I get a Run-time Error '3061' , "Too few > > parameters. Expected 2." When I run debug, it highlights the following > > line > > from the Concatenate module: > > > > Set rs = db.OpenRecordset(pstrSQL) > > > > Here is how I now have the crosstab query set up (in the QBE grid): > > > > Column 1: > > Field: City > > Total: Group By > > Crosstab: Row Heading > > > > Column 2: > > Field: Company: Concatenate("Select Company_Abbreviation FROM > > MatrixTestQuery WHERE City=""" & [City] & """AND Product_Line_Name=""" & > > [ProductLineName] & """") > > Total: First > > Crosstab: Value > > > > Column 3: > > Field: ProductLineName > > Total: Group By > > Crosstab: Column Heading > > > > And for the record, the SQL statement is: > > TRANSFORM First(Concatenate("Select Company_Abbreviation FROM > > MatrixTestQuery WHERE City=""" & [City] & """AND Product_Line_Name=""" & > > [ProductLineName] & """")) AS Company > > SELECT MatrixTestQuery.City > > FROM MatrixTestQuery > > GROUP BY MatrixTestQuery.City > > PIVOT MatrixTestQuery.ProductLineName; > > > > Thank you again for taking the time to help troubleshoot this for me. > > > > Steve Vincent > > svincent[ at ]harbornet.com > > > > > > > > "Duane Hookom" wrote: > > > >> As per my previous post, you need to place the Concatenate() function in > >> the > >> value so it should be like > >> TRANSFORM First(Concatenate(....)) as TheValue > >> > >> -- > >> Duane Hookom > >> MS Access MVP > >> > >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message > >> news:771D43B4-7540-43EF-BD08-A8C0E0A5C1B9[ at ]microsoft.com... > >> > Duane, I'm getting closer. Here are the actual names of the objects > >> > and > >> > fields: > >> > > >> > I have a query named MatrixTestQuery, > >> > containing three fields: City, CompanyAbbreviation, and > >> > ProductLineName. > >> > I used your Concatenate function, like this: > >> > > >> > Company: Concatenate("SELECT CompanyAbbreviation FROM MatrixTestQuery > >> > WHERE > >> > City=""" & [City] & """ AND ProductLineName=""" & [ProductLineName] & > >> > """") > >> > > >> > The whole crosstab query looks like this in SQL (although I set it up > >> > in > >> > the > >> > QBE grid): > >> > > >> > TRANSFORM First(MatrixTestQuery.CompanyAbbreviation) AS [FirstOfCompany > >> > Abbreviation] > >> > SELECT MatrixTestQuery.City, First(MatrixTestQuery.CompanyAbbreviation) > >> > AS > >> > [Total Of Company Abbreviation] > >> > FROM MatrixTestQuery > >> > GROUP BY MatrixTestQuery.City, Concatenate("SELECT CompanyAbbreviation > >> > FROM > >> > MatrixTestQuery WHERE City=""" & [City] & """ AND ProductLineName=""" & > >> > [ProductLineName] & """") > >> > PIVOT MatrixTestQuery.ProductLineName; > >> > > >> > I have succeeded in running the crosstab query without any errors, but > >> > rather than giving me all of the CompanyAbbreviation's concatenated > >> > into a > >> > single cell, instead I get a separate row for each City, like this: > >> > > >> > City Cardiac Invasive/Interventional Cardiac Medical Cardiothoracic > >> > Surgery Cardiovascular OP Diagnostics MRI Vascular > >> > Auburn ARMC > >> > Auburn ARMC > >> > Auburn ARMC > >> > Auburn ARMC > >> > Bremerton HMC HMC HMC > >> > Bremerton HMC > >> > Burien HLMC > >> > Federal Way CC > >> > Federal Way CDI > >> > Federal Way SFH > >> > Federal Way SFH > >> > Gig Harbor CVA > >> > Gig Harbor CVA > >> > > >> > I would like the data (CompanyAbbreviation) for each City summarized in > >> > one > >> > row/cell under a column headed with ProductLineName, like I see in your > >> > examples on your website. > >> > > >> > Can you see what I'm doing wrong? Sorry I'm not very savvy with SQL or > >> > VB. > >> > When this works, I will have learned a lot. > >> > Thanks in advance, > >> > Steve Vincent > >> > svincent[ at ]harbornet.com > >> > > >> > > >> > > >> > > >> > > >> > "Duane Hookom" wrote: > >> > > >> >> It would help to know the exact query and field names however: > >> >> TheValue: Concatenate("SELECT Company FROM qryYours WHERE City=""" & > >> >> [City] > >> >> & """ AND Product=""" & [Product] & """") > >> >> > >> >> > >> >> -- > >> >> Duane Hookom > >> >> MS Access MVP > >> >> > >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in > >> >> message > >> >> news:8C3B4752-B8F2-47C4-A953-87C21DDAD23F[ at ]microsoft.com... > >> >> > Duane, thanks to your information, I think I'm almost there... I > >> >> > have > >> >> > managed > >> >> > to get the "data" in the crosstab using the "First" total, but I'm > >> >> > not > >> >> > having > >> >> > much luck cobbling together a working Concatenate function. In my > >> >> > situation, > >> >> > all of my data is in the same "table" (actually, a query, but...), > >> >> > not > >> >> > drawing from different tables like yours. > >> >> > > >> >> > I am querying a simple query that contains City, Product, and > >> >> > Company. > >> >> > I > >> >> > want to show the one-to-many relationship of Product to Company, so > >> >> > for > >> >> > each > >> >> > Product (column) in each City (row), i'd like to show all of the > >> >> > Companies > >> >> > offering that product (table, totaled by "First" currently). I > >> >> > don't > >> >> > need > >> >> > to > >> >> > look outside this query/table for data; it is all contained in the > >> >> > data > >> >> > source. So I find myself trying to concatenate "Company" to > >> >> > "Company", > >> >> > but > >> >> > Access doesn't like my syntax so far. > >> >> > > >> >> > Any suggestions? Thanks so much for getting me this far. And, > >> >> > fantastic > >> >> > samples on your site! I will be back there for sure... > >> >> > > >> >> > > >> >> > "Duane Hookom" wrote: > >> >> > > >> >> >> You can use the generic concatenate() function found at > >> >> >> http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane > >> >> >> as > >> >> >> the > >> >> >> Value in your crosstab query. Set the Total: to First. > >> >> >> > >> >> >> -- > >> >> >> Duane Hookom > >> >> >> MS Access MVP > >> >> >> > >> >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in > >> >> >> message > >> >> >> news:869EEA03-4D76-4509-9104-A52C986E3A29[ at ]microsoft.com... > >> >> >> > Hi, I've been scouring the Crosstab postings hoping for help on > >> >> >> > my > >> >> >> > specific > >> >> >> > issue, but can't seem to find exactly the answer or the problem: > >> >> >> > > >> >> >> > I have a table of data that shows Companies, Cities and Products. > >> >> >> > I > >> >> >> > would > >> >> >> > like the report to be laid out crosstab-fashion with Cities as > >> >> >> > the > >> >> >> > Row > >> >> >> > headings, Products as the Column headings, but instead of data > >> >> >> > being > >> >> >> > summed > >> >> >> > or counted, I would like the Companies to be listed in the cells > >> >> >> > at > >> >> >> > the > >> >> >> > intersection of the Cities and Products. There will be more than > >> >> >> > one > >> >> >> > company > >> >> >> > in some cells; some will be empty. I'm looking for this kind of > >> >> >> > layout > >> >> >> > (I > >> >> >> > hope it transfers to the forum intact...). Is it possible to > >> >> >> > "substitute" > >> >> >> > the actual data in the fields, instead of a calculation, for the > >> >> >> > "data" > >> >> >> > area > >> >> >> > of the crosstab? Trying the same time in Excel with Pivot > >> >> >> > Tables, > >> >> >> > with > >> >> >> > similar (no) results. > >> >> >> > > >> >> >> > Products > >> >> >> > City Prod1 Prod2 Prod3 Prod4 Prod5 > >> >> >> > Los Angeles Company1 Company3 Company2 Company5 Company7 > >> >> >> > Company2 Company7 Company9 > >> >> >> > Seattle Company5 Company1 Company5 Company3 Company4 > >> >> >> > Company7 Company2 Company7 Company5 > >> >> >> > Company5 Company9 > >> >> >> > Pheonix Company3 Company3 Company3 Company3 > >> >> >> > Dallas Company2 Company1 Company1 Company2 Company2 > >> >> >> > Company6 Company2 Company3 > >> >> >> > Chicago Company8 Company7 Company7 > >> >> >> > Company8 Company8 > >> >> >> > > >> >> >> > Thanks in advance for any advice you can offer, > >> >> >> > Steve Vincent > >> >> >> > svincent[ at ]harbornet.com > >> >> >> > > >> >> >> > >> >> >> > >> >> >> > >> >> > >> >> > >> >> > >> > >> > >> > > >

Re: Crosstab: show contents of field rather than sum? "Duane Hookom" <DuaneAtNoSpanHookomDotNet> 21.08.2006 21:53:47 I would prefer to see your full SQL view. I would expect to see both the City and ProductLineName in the concatenate since you want to return all the CompanyAbbreviations for a specific City and Product Line I would create a group by query SELECT City, ProductLineName FROM MatrixTestQuery GROUP BY City, ProductLineName; Then create a xtab query with City as the Row Heading, ProductLineName as the Column Heading and Value: Expr1: First(Concatenate("Select CompanyAbbreviation FROM MatrixTestQuery WHERE [City]=""" & [City] & """ AND ProductLineName=""" & [ProductLineName] & """ ORDER BY CompanyAbbreviation")) -- Duane Hookom MS Access MVP "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message news:AAED36D7-B728-4953-8DF4-A7C386B28E21[ at ]microsoft.com... [Quoted Text] >I have renamed them CompanyAbbreviation and ProductLineName, to get rid of > the spaces to be more VB-friendly (don't worry... i changed their > references > in underlying queries, etc.). I have actually experimented with calling > the > CompanyAbbreviation from the underlying CompanyInfo table by its CompanyID > number, rather than referencing the same query that I'm querying. I'm > thinking that I gave my query some kind of circular reference. I have > tried > to make the syntax as close to your online family database examples as > possible. I'm getting good results, no errors... but still not getting > the > multiple CompanyAbbreviation "listings" in the crosstab query results. > I've > tried the concatenate statement in a normal select query with no errors, > but > also no grouping/listing like I'm hoping for. I don't really get what I'm > looking for by grouping and layout on the Report level -- I really think I > need it to be in a Crosstab query. > > Thanks for sticking with me... i'm really trying to do it as much on my > own > as possible, studying and following your online queries. If you don't > mind, > here's my latest attempt. I'll try to give you the territory here as > simply > as possible: > > I'm crosstab-querying a simple select query named MatrixTestQuery which > now > has four fields: City, ProductLineName, and CompanyAbbreviation, and > CompanyID. > > CompanyID, CompanyAbbreviation and City are all from the CompanyInfoTable. > ProductLineName is from the ProductLineTable. > > I have set up the Crosstab like this: > > Rows: [City] > Columnns: [ProductLineName] > Value: Expr1: First(Concatenate("Select CompanyAbbreviation FROM > CompanyInfoTable WHERE CompanyID=" & [CompanyID] & " ORDER BY > CompanyAbbreviation")) > > If I "Total" by "Expression" or "Where", then I just get one entry in each > cell, not a concatenated "list" or "group". If I total by "First", like > you > suggest, I get the error message: "Cannot have an aggregate function in > expression..." (and then it gives the Concatenate function/formula). If I > remove the extra "First" from the expression, then I get the same > one-CompanyAbbreviation-per-cell result as when I use "Expression" or > "Where". > > I think I'm pretty close here, probably just missing a quote mark or two. > Sorry for dragging this out, but I think we're on the verge of a > breakthrough > here, Duane (i hope, for your sake!). > > > > > "Duane Hookom" wrote: > >> I think you have used the wrong field names in the call to the >> Concatenate >> function. Are your field names in MatrixTestQuery Company_Abbreviation, >> City, and Product_Line_Names? >> >> >> -- >> Duane Hookom >> MS Access MVP >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message >> news:2E7EFFF5-25FB-4B13-A1F0-DB32AB9CB3C8[ at ]microsoft.com... >> > Duane, >> > >> > I think I'm close, but now I get a Run-time Error '3061' , "Too few >> > parameters. Expected 2." When I run debug, it highlights the following >> > line >> > from the Concatenate module: >> > >> > Set rs = db.OpenRecordset(pstrSQL) >> > >> > Here is how I now have the crosstab query set up (in the QBE grid): >> > >> > Column 1: >> > Field: City >> > Total: Group By >> > Crosstab: Row Heading >> > >> > Column 2: >> > Field: Company: Concatenate("Select Company_Abbreviation FROM >> > MatrixTestQuery WHERE City=""" & [City] & """AND Product_Line_Name=""" >> > & >> > [ProductLineName] & """") >> > Total: First >> > Crosstab: Value >> > >> > Column 3: >> > Field: ProductLineName >> > Total: Group By >> > Crosstab: Column Heading >> > >> > And for the record, the SQL statement is: >> > TRANSFORM First(Concatenate("Select Company_Abbreviation FROM >> > MatrixTestQuery WHERE City=""" & [City] & """AND Product_Line_Name=""" >> > & >> > [ProductLineName] & """")) AS Company >> > SELECT MatrixTestQuery.City >> > FROM MatrixTestQuery >> > GROUP BY MatrixTestQuery.City >> > PIVOT MatrixTestQuery.ProductLineName; >> > >> > Thank you again for taking the time to help troubleshoot this for me. >> > >> > Steve Vincent >> > svincent[ at ]harbornet.com >> > >> > >> > >> > "Duane Hookom" wrote: >> > >> >> As per my previous post, you need to place the Concatenate() function >> >> in >> >> the >> >> value so it should be like >> >> TRANSFORM First(Concatenate(....)) as TheValue >> >> >> >> -- >> >> Duane Hookom >> >> MS Access MVP >> >> >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in >> >> message >> >> news:771D43B4-7540-43EF-BD08-A8C0E0A5C1B9[ at ]microsoft.com... >> >> > Duane, I'm getting closer. Here are the actual names of the objects >> >> > and >> >> > fields: >> >> > >> >> > I have a query named MatrixTestQuery, >> >> > containing three fields: City, CompanyAbbreviation, and >> >> > ProductLineName. >> >> > I used your Concatenate function, like this: >> >> > >> >> > Company: Concatenate("SELECT CompanyAbbreviation FROM >> >> > MatrixTestQuery >> >> > WHERE >> >> > City=""" & [City] & """ AND ProductLineName=""" & [ProductLineName] >> >> > & >> >> > """") >> >> > >> >> > The whole crosstab query looks like this in SQL (although I set it >> >> > up >> >> > in >> >> > the >> >> > QBE grid): >> >> > >> >> > TRANSFORM First(MatrixTestQuery.CompanyAbbreviation) AS >> >> > [FirstOfCompany >> >> > Abbreviation] >> >> > SELECT MatrixTestQuery.City, >> >> > First(MatrixTestQuery.CompanyAbbreviation) >> >> > AS >> >> > [Total Of Company Abbreviation] >> >> > FROM MatrixTestQuery >> >> > GROUP BY MatrixTestQuery.City, Concatenate("SELECT >> >> > CompanyAbbreviation >> >> > FROM >> >> > MatrixTestQuery WHERE City=""" & [City] & """ AND >> >> > ProductLineName=""" & >> >> > [ProductLineName] & """") >> >> > PIVOT MatrixTestQuery.ProductLineName; >> >> > >> >> > I have succeeded in running the crosstab query without any errors, >> >> > but >> >> > rather than giving me all of the CompanyAbbreviation's concatenated >> >> > into a >> >> > single cell, instead I get a separate row for each City, like this: >> >> > >> >> > City Cardiac Invasive/Interventional Cardiac Medical Cardiothoracic >> >> > Surgery Cardiovascular OP Diagnostics MRI Vascular >> >> > Auburn ARMC >> >> > Auburn ARMC >> >> > Auburn ARMC >> >> > Auburn ARMC >> >> > Bremerton HMC HMC HMC >> >> > Bremerton HMC >> >> > Burien HLMC >> >> > Federal Way CC >> >> > Federal Way CDI >> >> > Federal Way SFH >> >> > Federal Way SFH >> >> > Gig Harbor CVA >> >> > Gig Harbor CVA >> >> > >> >> > I would like the data (CompanyAbbreviation) for each City summarized >> >> > in >> >> > one >> >> > row/cell under a column headed with ProductLineName, like I see in >> >> > your >> >> > examples on your website. >> >> > >> >> > Can you see what I'm doing wrong? Sorry I'm not very savvy with SQL >> >> > or >> >> > VB. >> >> > When this works, I will have learned a lot. >> >> > Thanks in advance, >> >> > Steve Vincent >> >> > svincent[ at ]harbornet.com >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > "Duane Hookom" wrote: >> >> > >> >> >> It would help to know the exact query and field names however: >> >> >> TheValue: Concatenate("SELECT Company FROM qryYours WHERE City=""" >> >> >> & >> >> >> [City] >> >> >> & """ AND Product=""" & [Product] & """") >> >> >> >> >> >> >> >> >> -- >> >> >> Duane Hookom >> >> >> MS Access MVP >> >> >> >> >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in >> >> >> message >> >> >> news:8C3B4752-B8F2-47C4-A953-87C21DDAD23F[ at ]microsoft.com... >> >> >> > Duane, thanks to your information, I think I'm almost there... I >> >> >> > have >> >> >> > managed >> >> >> > to get the "data" in the crosstab using the "First" total, but >> >> >> > I'm >> >> >> > not >> >> >> > having >> >> >> > much luck cobbling together a working Concatenate function. In >> >> >> > my >> >> >> > situation, >> >> >> > all of my data is in the same "table" (actually, a query, >> >> >> > but...), >> >> >> > not >> >> >> > drawing from different tables like yours. >> >> >> > >> >> >> > I am querying a simple query that contains City, Product, and >> >> >> > Company. >> >> >> > I >> >> >> > want to show the one-to-many relationship of Product to Company, >> >> >> > so >> >> >> > for >> >> >> > each >> >> >> > Product (column) in each City (row), i'd like to show all of the >> >> >> > Companies >> >> >> > offering that product (table, totaled by "First" currently). I >> >> >> > don't >> >> >> > need >> >> >> > to >> >> >> > look outside this query/table for data; it is all contained in >> >> >> > the >> >> >> > data >> >> >> > source. So I find myself trying to concatenate "Company" to >> >> >> > "Company", >> >> >> > but >> >> >> > Access doesn't like my syntax so far. >> >> >> > >> >> >> > Any suggestions? Thanks so much for getting me this far. And, >> >> >> > fantastic >> >> >> > samples on your site! I will be back there for sure... >> >> >> > >> >> >> > >> >> >> > "Duane Hookom" wrote: >> >> >> > >> >> >> >> You can use the generic concatenate() function found at >> >> >> >> http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane >> >> >> >> as >> >> >> >> the >> >> >> >> Value in your crosstab query. Set the Total: to First. >> >> >> >> >> >> >> >> -- >> >> >> >> Duane Hookom >> >> >> >> MS Access MVP >> >> >> >> >> >> >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote >> >> >> >> in >> >> >> >> message >> >> >> >> news:869EEA03-4D76-4509-9104-A52C986E3A29[ at ]microsoft.com... >> >> >> >> > Hi, I've been scouring the Crosstab postings hoping for help >> >> >> >> > on >> >> >> >> > my >> >> >> >> > specific >> >> >> >> > issue, but can't seem to find exactly the answer or the >> >> >> >> > problem: >> >> >> >> > >> >> >> >> > I have a table of data that shows Companies, Cities and >> >> >> >> > Products. >> >> >> >> > I >> >> >> >> > would >> >> >> >> > like the report to be laid out crosstab-fashion with Cities as >> >> >> >> > the >> >> >> >> > Row >> >> >> >> > headings, Products as the Column headings, but instead of data >> >> >> >> > being >> >> >> >> > summed >> >> >> >> > or counted, I would like the Companies to be listed in the >> >> >> >> > cells >> >> >> >> > at >> >> >> >> > the >> >> >> >> > intersection of the Cities and Products. There will be more >> >> >> >> > than >> >> >> >> > one >> >> >> >> > company >> >> >> >> > in some cells; some will be empty. I'm looking for this kind >> >> >> >> > of >> >> >> >> > layout >> >> >> >> > (I >> >> >> >> > hope it transfers to the forum intact...). Is it possible to >> >> >> >> > "substitute" >> >> >> >> > the actual data in the fields, instead of a calculation, for >> >> >> >> > the >> >> >> >> > "data" >> >> >> >> > area >> >> >> >> > of the crosstab? Trying the same time in Excel with Pivot >> >> >> >> > Tables, >> >> >> >> > with >> >> >> >> > similar (no) results. >> >> >> >> > >> >> >> >> > Products >> >> >> >> > City Prod1 Prod2 Prod3 Prod4 Prod5 >> >> >> >> > Los Angeles Company1 Company3 Company2 Company5 Company7 >> >> >> >> > Company2 Company7 Company9 >> >> >> >> > Seattle Company5 Company1 Company5 Company3 Company4 >> >> >> >> > Company7 Company2 Company7 Company5 >> >> >> >> > Company5 Company9 >> >> >> >> > Pheonix Company3 Company3 Company3 Company3 >> >> >> >> > Dallas Company2 Company1 Company1 Company2 Company2 >> >> >> >> > Company6 Company2 Company3 >> >> >> >> > Chicago Company8 Company7 Company7 >> >> >> >> > Company8 Company8 >> >> >> >> > >> >> >> >> > Thanks in advance for any advice you can offer, >> >> >> >> > Steve Vincent >> >> >> >> > svincent[ at ]harbornet.com >> >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>

Re: Crosstab: show contents of field rather than sum? Steve Vincent 24.08.2006 21:28:03 Success! I pretty much ignored the "group by query" part, but your latest SQL statement worked for me just great. All the CompanyAbbreviations show up in each cell of the crosstab. Beautiful! Just one more thing... I've been trying to program in a line break for each CompanyAbbreviation, so they appear vertically in a "column", like in your example queries from your site, but I can't seem to get the insertion of the character codes to really do anything when I run the query, no matter where I try placing them in the query. Here's my SQL of the "working" crosstab with your Concatenate function. Any ideas where I can plug in the Chr(13) and/or Chr(10) into the statement? TRANSFORM First(Concatenate("Select CompanyAbbreviation FROM MatrixTestQuery WHERE [City]=""" & [City] & """ AND ProductLineName=""" & [ProductLineName] & """ ORDER BY CompanyAbbreviation")) AS Company SELECT MatrixTestQuery.City FROM MatrixTestQuery GROUP BY MatrixTestQuery.City PIVOT MatrixTestQuery.ProductLineName; TIA, Steve "Duane Hookom" wrote: [Quoted Text] > I would prefer to see your full SQL view. I would expect to see both the > City and ProductLineName in the concatenate since you want to return all the > CompanyAbbreviations for a specific City and Product Line > > > I would create a group by query > SELECT City, ProductLineName > FROM MatrixTestQuery > GROUP BY City, ProductLineName; > > Then create a xtab query with City as the Row Heading, ProductLineName as > the Column Heading and > Value: Expr1: First(Concatenate("Select CompanyAbbreviation FROM > MatrixTestQuery WHERE [City]=""" & [City] & """ AND ProductLineName=""" & > [ProductLineName] & """ ORDER BY CompanyAbbreviation")) > > > -- > Duane Hookom > MS Access MVP > > "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message > news:AAED36D7-B728-4953-8DF4-A7C386B28E21[ at ]microsoft.com... > >I have renamed them CompanyAbbreviation and ProductLineName, to get rid of > > the spaces to be more VB-friendly (don't worry... i changed their > > references > > in underlying queries, etc.). I have actually experimented with calling > > the > > CompanyAbbreviation from the underlying CompanyInfo table by its CompanyID > > number, rather than referencing the same query that I'm querying. I'm > > thinking that I gave my query some kind of circular reference. I have > > tried > > to make the syntax as close to your online family database examples as > > possible. I'm getting good results, no errors... but still not getting > > the > > multiple CompanyAbbreviation "listings" in the crosstab query results. > > I've > > tried the concatenate statement in a normal select query with no errors, > > but > > also no grouping/listing like I'm hoping for. I don't really get what I'm > > looking for by grouping and layout on the Report level -- I really think I > > need it to be in a Crosstab query. > > > > Thanks for sticking with me... i'm really trying to do it as much on my > > own > > as possible, studying and following your online queries. If you don't > > mind, > > here's my latest attempt. I'll try to give you the territory here as > > simply > > as possible: > > > > I'm crosstab-querying a simple select query named MatrixTestQuery which > > now > > has four fields: City, ProductLineName, and CompanyAbbreviation, and > > CompanyID. > > > > CompanyID, CompanyAbbreviation and City are all from the CompanyInfoTable. > > ProductLineName is from the ProductLineTable. > > > > I have set up the Crosstab like this: > > > > Rows: [City] > > Columnns: [ProductLineName] > > Value: Expr1: First(Concatenate("Select CompanyAbbreviation FROM > > CompanyInfoTable WHERE CompanyID=" & [CompanyID] & " ORDER BY > > CompanyAbbreviation")) > > > > If I "Total" by "Expression" or "Where", then I just get one entry in each > > cell, not a concatenated "list" or "group". If I total by "First", like > > you > > suggest, I get the error message: "Cannot have an aggregate function in > > expression..." (and then it gives the Concatenate function/formula). If I > > remove the extra "First" from the expression, then I get the same > > one-CompanyAbbreviation-per-cell result as when I use "Expression" or > > "Where". > > > > I think I'm pretty close here, probably just missing a quote mark or two. > > Sorry for dragging this out, but I think we're on the verge of a > > breakthrough > > here, Duane (i hope, for your sake!). > > > > > > > > > > "Duane Hookom" wrote: > > > >> I think you have used the wrong field names in the call to the > >> Concatenate > >> function. Are your field names in MatrixTestQuery Company_Abbreviation, > >> City, and Product_Line_Names? > >> > >> > >> -- > >> Duane Hookom > >> MS Access MVP > >> > >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message > >> news:2E7EFFF5-25FB-4B13-A1F0-DB32AB9CB3C8[ at ]microsoft.com... > >> > Duane, > >> > > >> > I think I'm close, but now I get a Run-time Error '3061' , "Too few > >> > parameters. Expected 2." When I run debug, it highlights the following > >> > line > >> > from the Concatenate module: > >> > > >> > Set rs = db.OpenRecordset(pstrSQL) > >> > > >> > Here is how I now have the crosstab query set up (in the QBE grid): > >> > > >> > Column 1: > >> > Field: City > >> > Total: Group By > >> > Crosstab: Row Heading > >> > > >> > Column 2: > >> > Field: Company: Concatenate("Select Company_Abbreviation FROM > >> > MatrixTestQuery WHERE City=""" & [City] & """AND Product_Line_Name=""" > >> > & > >> > [ProductLineName] & """") > >> > Total: First > >> > Crosstab: Value > >> > > >> > Column 3: > >> > Field: ProductLineName > >> > Total: Group By > >> > Crosstab: Column Heading > >> > > >> > And for the record, the SQL statement is: > >> > TRANSFORM First(Concatenate("Select Company_Abbreviation FROM > >> > MatrixTestQuery WHERE City=""" & [City] & """AND Product_Line_Name=""" > >> > & > >> > [ProductLineName] & """")) AS Company > >> > SELECT MatrixTestQuery.City > >> > FROM MatrixTestQuery > >> > GROUP BY MatrixTestQuery.City > >> > PIVOT MatrixTestQuery.ProductLineName; > >> > > >> > Thank you again for taking the time to help troubleshoot this for me. > >> > > >> > Steve Vincent > >> > svincent[ at ]harbornet.com > >> > > >> > > >> > > >> > "Duane Hookom" wrote: > >> > > >> >> As per my previous post, you need to place the Concatenate() function > >> >> in > >> >> the > >> >> value so it should be like > >> >> TRANSFORM First(Concatenate(....)) as TheValue > >> >> > >> >> -- > >> >> Duane Hookom > >> >> MS Access MVP > >> >> > >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in > >> >> message > >> >> news:771D43B4-7540-43EF-BD08-A8C0E0A5C1B9[ at ]microsoft.com... > >> >> > Duane, I'm getting closer. Here are the actual names of the objects > >> >> > and > >> >> > fields: > >> >> > > >> >> > I have a query named MatrixTestQuery, > >> >> > containing three fields: City, CompanyAbbreviation, and > >> >> > ProductLineName. > >> >> > I used your Concatenate function, like this: > >> >> > > >> >> > Company: Concatenate("SELECT CompanyAbbreviation FROM > >> >> > MatrixTestQuery > >> >> > WHERE > >> >> > City=""" & [City] & """ AND ProductLineName=""" & [ProductLineName] > >> >> > & > >> >> > """") > >> >> > > >> >> > The whole crosstab query looks like this in SQL (although I set it > >> >> > up > >> >> > in > >> >> > the > >> >> > QBE grid): > >> >> > > >> >> > TRANSFORM First(MatrixTestQuery.CompanyAbbreviation) AS > >> >> > [FirstOfCompany > >> >> > Abbreviation] > >> >> > SELECT MatrixTestQuery.City, > >> >> > First(MatrixTestQuery.CompanyAbbreviation) > >> >> > AS > >> >> > [Total Of Company Abbreviation] > >> >> > FROM MatrixTestQuery > >> >> > GROUP BY MatrixTestQuery.City, Concatenate("SELECT > >> >> > CompanyAbbreviation > >> >> > FROM > >> >> > MatrixTestQuery WHERE City=""" & [City] & """ AND > >> >> > ProductLineName=""" & > >> >> > [ProductLineName] & """") > >> >> > PIVOT MatrixTestQuery.ProductLineName; > >> >> > > >> >> > I have succeeded in running the crosstab query without any errors, > >> >> > but > >> >> > rather than giving me all of the CompanyAbbreviation's concatenated > >> >> > into a > >> >> > single cell, instead I get a separate row for each City, like this: > >> >> > > >> >> > City Cardiac Invasive/Interventional Cardiac Medical Cardiothoracic > >> >> > Surgery Cardiovascular OP Diagnostics MRI Vascular > >> >> > Auburn ARMC > >> >> > Auburn ARMC > >> >> > Auburn ARMC > >> >> > Auburn ARMC > >> >> > Bremerton HMC HMC HMC > >> >> > Bremerton HMC > >> >> > Burien HLMC > >> >> > Federal Way CC > >> >> > Federal Way CDI > >> >> > Federal Way SFH > >> >> > Federal Way SFH > >> >> > Gig Harbor CVA > >> >> > Gig Harbor CVA > >> >> > > >> >> > I would like the data (CompanyAbbreviation) for each City summarized > >> >> > in > >> >> > one > >> >> > row/cell under a column headed with ProductLineName, like I see in > >> >> > your > >> >> > examples on your website. > >> >> > > >> >> > Can you see what I'm doing wrong? Sorry I'm not very savvy with SQL > >> >> > or > >> >> > VB. > >> >> > When this works, I will have learned a lot. > >> >> > Thanks in advance, > >> >> > Steve Vincent > >> >> > svincent[ at ]harbornet.com > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > "Duane Hookom" wrote: > >> >> > > >> >> >> It would help to know the exact query and field names however: > >> >> >> TheValue: Concatenate("SELECT Company FROM qryYours WHERE City=""" > >> >> >> & > >> >> >> [City] > >> >> >> & """ AND Product=""" & [Product] & """") > >> >> >> > >> >> >> > >> >> >> -- > >> >> >> Duane Hookom > >> >> >> MS Access MVP > >> >> >> > >> >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in > >> >> >> message > >> >> >> news:8C3B4752-B8F2-47C4-A953-87C21DDAD23F[ at ]microsoft.com... > >> >> >> > Duane, thanks to your information, I think I'm almost there... I > >> >> >> > have > >> >> >> > managed > >> >> >> > to get the "data" in the crosstab using the "First" total, but > >> >> >> > I'm > >> >> >> > not > >> >> >> > having > >> >> >> > much luck cobbling together a working Concatenate function. In > >> >> >> > my > >> >> >> > situation, > >> >> >> > all of my data is in the same "table" (actually, a query, > >> >> >> > but...), > >> >> >> > not > >> >> >> > drawing from different tables like yours. > >> >> >> > > >> >> >> > I am querying a simple query that contains City, Product, and > >> >> >> > Company. > >> >> >> > I > >> >> >> > want to show the one-to-many relationship of Product to Company, > >> >> >> > so > >> >> >> > for > >> >> >> > each > >> >> >> > Product (column) in each City (row), i'd like to show all of the > >> >> >> > Companies > >> >> >> > offering that product (table, totaled by "First" currently). I > >> >> >> > don't > >> >> >> > need > >> >> >> > to > >> >> >> > look outside this query/table for data; it is all contained in > >> >> >> > the > >> >> >> > data > >> >> >> > source. So I find myself trying to concatenate "Company" to > >> >> >> > "Company", > >> >> >> > but > >> >> >> > Access doesn't like my syntax so far. > >> >> >> > > >> >> >> > Any suggestions? Thanks so much for getting me this far. And, > >> >> >> > fantastic > >> >> >> > samples on your site! I will be back there for sure... > >> >> >> > > >> >> >> > > >> >> >> > "Duane Hookom" wrote: > >> >> >> > > >> >> >> >> You can use the generic concatenate() function found at > >> >> >> >> http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane > >> >> >> >> as

Re: Crosstab: show contents of field rather than sum? "Duane Hookom" <DuaneAtNoSpanHookomDotNet> 24.08.2006 22:11:03 Try: TRANSFORM First(Concatenate("Select CompanyAbbreviation FROM MatrixTestQuery WHERE [City]=""" & [City] & """ AND ProductLineName=""" & [ProductLineName] & """ ORDER BY CompanyAbbreviation", Chr(13) & Chr(10))) AS Company SELECT MatrixTestQuery.City FROM MatrixTestQuery GROUP BY MatrixTestQuery.City PIVOT MatrixTestQuery.ProductLineName; -- Duane Hookom MS Access MVP "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message news:E1724190-3897-4238-85A7-D8AFC4A7F4D6[ at ]microsoft.com... [Quoted Text] > Success! I pretty much ignored the "group by query" part, but your latest > SQL statement worked for me just great. All the CompanyAbbreviations show > up > in each cell of the crosstab. Beautiful! > > Just one more thing... I've been trying to program in a line break for > each > CompanyAbbreviation, so they appear vertically in a "column", like in your > example queries from your site, but I can't seem to get the insertion of > the > character codes to really do anything when I run the query, no matter > where I > try placing them in the query. > > Here's my SQL of the "working" crosstab with your Concatenate function. > Any > ideas where I can plug in the Chr(13) and/or Chr(10) into the statement? > > TRANSFORM First(Concatenate("Select CompanyAbbreviation FROM > MatrixTestQuery > WHERE [City]=""" & [City] & """ AND ProductLineName=""" & > [ProductLineName] & > """ ORDER BY CompanyAbbreviation")) AS Company > SELECT MatrixTestQuery.City > FROM MatrixTestQuery > GROUP BY MatrixTestQuery.City > PIVOT MatrixTestQuery.ProductLineName; > > > TIA, > Steve > > "Duane Hookom" wrote: > >> I would prefer to see your full SQL view. I would expect to see both the >> City and ProductLineName in the concatenate since you want to return all >> the >> CompanyAbbreviations for a specific City and Product Line >> >> >> I would create a group by query >> SELECT City, ProductLineName >> FROM MatrixTestQuery >> GROUP BY City, ProductLineName; >> >> Then create a xtab query with City as the Row Heading, ProductLineName as >> the Column Heading and >> Value: Expr1: First(Concatenate("Select CompanyAbbreviation FROM >> MatrixTestQuery WHERE [City]=""" & [City] & """ AND ProductLineName=""" & >> [ProductLineName] & """ ORDER BY CompanyAbbreviation")) >> >> >> -- >> Duane Hookom >> MS Access MVP >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in message >> news:AAED36D7-B728-4953-8DF4-A7C386B28E21[ at ]microsoft.com... >> >I have renamed them CompanyAbbreviation and ProductLineName, to get rid >> >of >> > the spaces to be more VB-friendly (don't worry... i changed their >> > references >> > in underlying queries, etc.). I have actually experimented with >> > calling >> > the >> > CompanyAbbreviation from the underlying CompanyInfo table by its >> > CompanyID >> > number, rather than referencing the same query that I'm querying. I'm >> > thinking that I gave my query some kind of circular reference. I have >> > tried >> > to make the syntax as close to your online family database examples as >> > possible. I'm getting good results, no errors... but still not getting >> > the >> > multiple CompanyAbbreviation "listings" in the crosstab query results. >> > I've >> > tried the concatenate statement in a normal select query with no >> > errors, >> > but >> > also no grouping/listing like I'm hoping for. I don't really get what >> > I'm >> > looking for by grouping and layout on the Report level -- I really >> > think I >> > need it to be in a Crosstab query. >> > >> > Thanks for sticking with me... i'm really trying to do it as much on >> > my >> > own >> > as possible, studying and following your online queries. If you don't >> > mind, >> > here's my latest attempt. I'll try to give you the territory here as >> > simply >> > as possible: >> > >> > I'm crosstab-querying a simple select query named MatrixTestQuery which >> > now >> > has four fields: City, ProductLineName, and CompanyAbbreviation, and >> > CompanyID. >> > >> > CompanyID, CompanyAbbreviation and City are all from the >> > CompanyInfoTable. >> > ProductLineName is from the ProductLineTable. >> > >> > I have set up the Crosstab like this: >> > >> > Rows: [City] >> > Columnns: [ProductLineName] >> > Value: Expr1: First(Concatenate("Select CompanyAbbreviation FROM >> > CompanyInfoTable WHERE CompanyID=" & [CompanyID] & " ORDER BY >> > CompanyAbbreviation")) >> > >> > If I "Total" by "Expression" or "Where", then I just get one entry in >> > each >> > cell, not a concatenated "list" or "group". If I total by "First", >> > like >> > you >> > suggest, I get the error message: "Cannot have an aggregate function >> > in >> > expression..." (and then it gives the Concatenate function/formula). >> > If I >> > remove the extra "First" from the expression, then I get the same >> > one-CompanyAbbreviation-per-cell result as when I use "Expression" or >> > "Where". >> > >> > I think I'm pretty close here, probably just missing a quote mark or >> > two. >> > Sorry for dragging this out, but I think we're on the verge of a >> > breakthrough >> > here, Duane (i hope, for your sake!). >> > >> > >> > >> > >> > "Duane Hookom" wrote: >> > >> >> I think you have used the wrong field names in the call to the >> >> Concatenate >> >> function. Are your field names in MatrixTestQuery >> >> Company_Abbreviation, >> >> City, and Product_Line_Names? >> >> >> >> >> >> -- >> >> Duane Hookom >> >> MS Access MVP >> >> >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in >> >> message >> >> news:2E7EFFF5-25FB-4B13-A1F0-DB32AB9CB3C8[ at ]microsoft.com... >> >> > Duane, >> >> > >> >> > I think I'm close, but now I get a Run-time Error '3061' , "Too few >> >> > parameters. Expected 2." When I run debug, it highlights the >> >> > following >> >> > line >> >> > from the Concatenate module: >> >> > >> >> > Set rs = db.OpenRecordset(pstrSQL) >> >> > >> >> > Here is how I now have the crosstab query set up (in the QBE grid): >> >> > >> >> > Column 1: >> >> > Field: City >> >> > Total: Group By >> >> > Crosstab: Row Heading >> >> > >> >> > Column 2: >> >> > Field: Company: Concatenate("Select Company_Abbreviation FROM >> >> > MatrixTestQuery WHERE City=""" & [City] & """AND >> >> > Product_Line_Name=""" >> >> > & >> >> > [ProductLineName] & """") >> >> > Total: First >> >> > Crosstab: Value >> >> > >> >> > Column 3: >> >> > Field: ProductLineName >> >> > Total: Group By >> >> > Crosstab: Column Heading >> >> > >> >> > And for the record, the SQL statement is: >> >> > TRANSFORM First(Concatenate("Select Company_Abbreviation FROM >> >> > MatrixTestQuery WHERE City=""" & [City] & """AND >> >> > Product_Line_Name=""" >> >> > & >> >> > [ProductLineName] & """")) AS Company >> >> > SELECT MatrixTestQuery.City >> >> > FROM MatrixTestQuery >> >> > GROUP BY MatrixTestQuery.City >> >> > PIVOT MatrixTestQuery.ProductLineName; >> >> > >> >> > Thank you again for taking the time to help troubleshoot this for >> >> > me. >> >> > >> >> > Steve Vincent >> >> > svincent[ at ]harbornet.com >> >> > >> >> > >> >> > >> >> > "Duane Hookom" wrote: >> >> > >> >> >> As per my previous post, you need to place the Concatenate() >> >> >> function >> >> >> in >> >> >> the >> >> >> value so it should be like >> >> >> TRANSFORM First(Concatenate(....)) as TheValue >> >> >> >> >> >> -- >> >> >> Duane Hookom >> >> >> MS Access MVP >> >> >> >> >> >> "Steve Vincent" <SteveVincent[ at ]discussions.microsoft.com> wrote in >> >> >> message >> >> >> news:771D43B4-7540-43EF-BD08-A8C0E0A5C1B9[ at ]microsoft.com... >> >> >> > Duane, I'm getting closer. Here are the actual names of the >> >> >> > objects >> >> >> > and >> >> >> > fields: >> >> >> > >> >> >> > I have a query named MatrixTestQuery, >> >> >> > containing three fields: City, CompanyAbbreviation, and >> >> >> > ProductLineName. >> >> >> > I used your Concatenate function, like this: >> >> >> > >> >> >> > Company: Concatenate("SELECT CompanyAbbreviation FROM >> >> >> > MatrixTestQuery >> >> >> > WHERE >> >> >> > City=""" & [City] & """ AND ProductLineName=""" & >> >> >> > [ProductLineName] >> >> >> > & >> >> >> > """") >> >> >> > >> >> >> > The whole crosstab query looks like this in SQL (although I set >> >> >> > it >> >> >> > up >> >> >> > in >> >> >> > the >> >> >> > QBE grid): >> >> >> > >> >> >> > TRANSFORM First(MatrixTestQuery.CompanyAbbreviation) AS >> >> >> > [FirstOfCompany >> >> >> > Abbreviation] >> >> >> > SELECT MatrixTestQuery.City, >> >> >> > First(MatrixTestQuery.CompanyAbbreviation) >> >> >> > AS >> >> >> > [Total Of Company Abbreviation] >> >> >> > FROM MatrixTestQuery >> >> >> > GROUP BY MatrixTestQuery.City, Concatenate("SELECT >> >> >> > CompanyAbbreviation >> >> >> > FROM >> >> >> > MatrixTestQuery WHERE City=""" & [City] & """ AND >> >> >> > ProductLineName=""" & >> >> >> > [ProductLineName] & """") >> >> >> > PIVOT MatrixTestQuery.ProductLineName; >> >> >> > >> >> >> > I have succeeded in running the crosstab query without any >> >> >> > errors, >> >> >> > but >> >> >> > rather than giving me all of the CompanyAbbreviation's >> >> >> > concatenated >> >> >> > into a >> >> >> > single cell, instead I get a separate row for each City, like >> >> >> > this: >> >> >> > >> >> >> > City Cardiac Invasive/Interventional Cardiac Medical >> >> >> > Cardiothoracic >> >> >> > Surgery Cardiovascular OP Diagnostics MRI Vascular >> >> >> > Auburn ARMC >> >> >> > Auburn ARMC >> >> >> > Auburn ARMC >> >> >> > Auburn ARMC >> >> >> > Bremerton HMC HMC HMC >> >> >> > Bremerton HMC >> >> >> > Burien HLMC >> >> >> > Federal Way CC >> >> >> > Federal Way CDI >> >> >> > Federal Way SFH >> >> >> > Federal Way SFH >> >> >> > Gig Harbor CVA >> >> >> > Gig Harbor CVA >> >> >> > >> >> >> > I would like the data (CompanyAbbreviation) for each City >> >> >> > summarized >> >> >> > in >> >> >> > one >> >> >> > row/cell under a column headed with ProductLineName, like I see >> >> >> > in >> >> >> > your >> >> >> > examples on your website. >> >> >> > >> >> >> > Can you see what I'm doing wrong? Sorry I'm not very savvy with >> >> >> > SQL >> >> >> > or >> >> >> > VB. >> >> >> > When this works, I will have learned a lot. >> >> >> > Thanks in advance, >> >> >> > Steve Vincent >> >> >> > svincent[ at ]harbornet.com >> >> >> > >> >> >> > >> >> >> > >> >> >> > >> >> >> > >> >> >> > "Duane Hookom" wrote: >> >> >> > >> >> >> >> It would help to know the exact query and field