Duane,
Thought about it some more, and I think maybe it has to do with the
fact
that I want to do my concatenate at the Section/Product level vs. just
1
level in your example (Last Name).
I tried creating a query of unique Section/Product (just a simple
select
unique) so that now I have a recordset that I think is like your tblFamily
Then I wrote my query as such to pull in Section and Product from my
ProductTest2-1 query that has Section, Product, and SEQ1 fields.
Saying
that I want to concatenate where Section in Unique = Section in
ProductTest2-1 AND Product in Unique = Product in ProdectTest2-1.
Here is the SQL I am trying to run to get Section, Product,
Concatenated
SEQ1 field.
SELECT UniqSecProd.Section, UniqSecProd.Product, Concatenate("SELECT SEQ1
FROM ProductTest2-1 WHERE Section =" & [Section] & " AND Product=" &
[Product]) AS Pages
FROM UniqSecProd;
Still getting runtime error highlighting this piece of the code...
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
I know that your code talks about ADO vs. DAO. Completely unfamiliar. I
can tell you that I am running Microsoft Access 2000 (9.0.6926 SP-3) on a
fairly recently purchased Dell desktop with plenty of memory running
Windows
2000 Professional.
Hope some of this helps. I feel like I am right on the edge of getting
this
to work and really appreciate you helping me. Wish I had a better
understanding of code that goes into building these functions so that I
wouldn't have to be such a pain in trying to give you details.
Would it be easier if I sent you a quick db with some sample records since
I
am not doing a good job at describing my tables/queries?
c-
Duane,
Sorry if it wasn't clear. Let me try again.
Here is an extract from query ProductTest2-1. I hope that it translates
well when I send.
Section is Text 50
Product is Text 255
SEQ1 is Text 255
Section Product SEQ1
1.1 Aluminum Electrolytic Capacitors 11 - 19
1.1 Aluminum Electrolytic Capacitors 112
1.1 Aluminum Electrolytic Capacitors 63 - 72
1.1 Aluminum Electrolytic Capacitors 74 - 84
1.1 Aluminum Electrolytic Capacitors 96
1.1 Aluminum Electrolytic Capacitors 98 - 100
1.1 Film Capacitors 105
1.1 Film Capacitors 108
1.1 Film Capacitors 110 - 114
1.1 Film Capacitors 28 - 35
1.1 Film Capacitors 87 - 93
1.1 Film Capacitors 97
1.2 Dials 143
1.2 Dials 184
1.2 Dials 194 - 195
1.2 Dials 227 - 228
1.2 Encoders 140
1.2 Encoders 227
1.3 SMD Chip Inductors 249 - 252
1.3 SMD Chip Inductors 255 - 257
1.3 SMD Chip Inductors 261 - 262
Would want the concatenate results to show as...
Section Product SEQ1
1.1 Aluminum Electrolytic Capacitors 11 - 19, 112, 63 - 72, 74 -
84,
96, 98 - 100
1.1 Film Capacitors 105, 108, 110 - 114, 28 - 35, 87 - 93, 97
1.2 Dials 143, 184, 194 - 195, 227 - 228
1.2 Encoders 140, 227
1.3 SMD Chip Inductors 249 - 252, 255 - 257, 261 - 262
Almost like I want a query that says Group by Section and Product then
Concatenate SEQ1 (separated by commas).
Sorry this is taking so much of your time. I always think of myself
as a
decent Access user until I find myself dealing with problems that Access
MVP's know like the back of their hand.
Thanks again for the help,
c-
It isn't clear what your table and field names are and data types.
I would place []s around your table name. The follow returns all the
SEQ1
values from the table where Section matches Section in your query
and
assumes Section is numeric.
Pages: Concatenate("SELECT SEQ1 FROM [ProductTest2-1] WHERE Section ="
&
[Section])
If Section is test, try:
Pages: Concatenate("SELECT SEQ1 FROM [ProductTest2-1] WHERE Section
="""
&
[Section] & """")
--
Duane Hookom
MS Access MVP
--
Thanks for the direction Duane.
Got your Concat2k.mdb and am trying to get it to work in my database
(copied
basConcatenate module to my DB)
I am still having some problems. I have all my data in one select
statement
that has 3 fields. Statement named ProductTest2-1
Section, Product, SEQ1
As example I have 6 records for Section 1.1/Product Aluminum (6
different
page numbers).
I tried to query this original query pulling fields Section, Product
and
creating field with formula of Pages: Concatenate("SELECT SEQ1
FROM
ProductTest2-1 WHERE Section =" & [Section])
In your examples you have data in two tables and are doing your
select
in
the concatenate formula to the table NOT in the query. Is that my
problem?
When I try the concatenate as shown above I get an error in the
Concatenate
module at...
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
With my data, each section will have multiple products. Section 1.1
has
the
6 records for Aluminum above and also has another 5 for Kits, 15 for
Capacitors, 18 for Film, and 3 for Hardware before it moves to
Section
1.2
which again will have multiple Products and each product will have
multiple
page records.
Do I need to find a way to split my data into two separate queries to
make
this work? I am sure your function is the answer to my question,
just
not
using it correctly yet.
c-
There are several "concatenate" function available that will do this
without
passing through a crosstab. My favorite solution is found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
--
Duane Hookom
MS Access MVP
--
Just the concatenate. I used a crosstab as the only way I
could
think
to
take multiple records and lay left to right so that later they
could
be
strung together.
Hard to explain but imagine a table with two fields. Product and
Page.
Product "Tool" would be listed 10 times for 10 different pages (in
a
catalog). Then product "Chip" may be listed 20 times for 20
different
pages. I am trying to take each unique Product and show all
the
pages
it
is
on separated by commas (for an index page in the back of a
catalog).
Maybe a crosstab and then string is not the best way to do
this,
but
it
was
the only idea I came up with.
Wholesale different ideas are welcomed with an open mind, I don't
claim
that
it is the best or only way for this to work.
Thanks again to the group for any ideas.
c-
message
Is the crosstab a requirement or just the "concatenate
together
all
the
fields into 1 string"?
--
Duane Hookom
MS Access MVP
I have a crosstab query that changes in the number of fields
that
will
populate every time it is used. Sometimes it is 20 fields,
sometimes
it
is
150 fields. This causes 2 problems. First, its not
predictable
the
layout
of the table, and 2nd my next step is to try to concatenate
together
all
the
fields into 1 string (cant do when gets large count because
expression
builder has text limit).
For example crosstab will have Section and Product in as Row
Heading
then
a
SEQ Number as Column Heading (this is the count of fields that
keeps
changing). My value is First of SEQ Number. What I am
trying
to
do
is
turn
this...
Section Product 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
18
19
20
21
22 23 24 25
1.1 Aluminum Electrolytic Capacitors 11 - 19 63 - 72 74 -
84
96
98 -
100 112
1.1 Aluminum Organic Capacitors 45
1.1 Capacitor Kits 28 45 59 85 - 86 117 -
120
1.1 Ceramic Capacitors 20 - 23
25 -
27
30
45 -
46 48 51 - 52 61 - 62 72 - 73 85 - 86 100 - 105 107 109 -
110
116
into...
Section Product SEQLine
1.1 Aluminum Electrolytic Capacitors 11 - 19, 63 - 72,
74 -
84,
96,
98 - 100, 112, , , , , , , , , , , , , , , , , , , ,
1.1 Aluminum Organic Capacitors , , , , , , 45, , , , , ,
,
,
,
,
,
,
, , , , , , ,
1.1 Capacitor Kits , , , , , , , 28, 45, 59, 85 - 86,
117 -
120,
,
,
,
, , , , , , , , , ,
1.1 Ceramic Capacitors , , , , , , , , , , , , 20 - 23,
25 -
27,
30,
45 - 46, 48, 51 - 52, 61 - 62, 72 - 73, 85 - 86, 100 - 105,
107,
109 -
110,
116,
Only way I know how is to do a query with expression builder
saying...
SEQLine: [ProductTest2_Crosstab]![1] & ", " &
[ProductTest2_Crosstab]![2]
&
", " & [ProductTest2_Crosstab]![3] & ", " &
[ProductTest2_Crosstab]![4]
&
",
" & [ProductTest2_Crosstab]![5] & ", " &
[ProductTest2_Crosstab]![6]
Problem is that I can only build so far before I run into
"String
returned
by the builder is to long".
Any help would be greatly appreciated. Running out of
ideas.
Thanks,
c-
