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!).
:
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
message
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
(e-mail address removed)
:
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
message
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
(e-mail address removed)
:
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
in
message
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...
:
You can use the generic concatenate() function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
as
