Crosstab Query Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChrisR - 27 Jun 2005 18:33 GMT

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-

Reply to this Message

Martin J - 27 Jun 2005 22:01 GMT

Did you try taking out extra spaces when using dashes and then using replace
space with comma space?

Signature

HTH
Martin J

> 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
[quoted text clipped - 44 lines]
>
> c-

Reply to this Message

Duane Hookom - 28 Jun 2005 03:46 GMT

Is the crosstab a requirement or just the "concatenate together all the
fields into 1 string"?

Signature

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
[quoted text clipped - 54 lines]
>
> c-

Reply to this Message

ChrisR - 28 Jun 2005 15:18 GMT

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-

> Is the crosstab a requirement or just the "concatenate together all the
> fields into 1 string"?
[quoted text clipped - 57 lines]
> >
> > c-

Reply to this Message

Duane Hookom - 28 Jun 2005 15:55 GMT

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.

Signature

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
[quoted text clipped - 92 lines]
>> >
>> > c-

Reply to this Message

ChrisR - 28 Jun 2005 16:41 GMT

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
[quoted text clipped - 96 lines]
> >> >
> >> > c-

Reply to this Message

Duane Hookom - 28 Jun 2005 17:32 GMT

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] & """")

Signature

Duane Hookom
MS Access MVP
--

> Thanks for the direction Duane.
>
[quoted text clipped - 158 lines]
>> >> >
>> >> > c-

Reply to this Message

ChrisR - 28 Jun 2005 19:09 GMT

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
[quoted text clipped - 170 lines]
> >> >> >
> >> >> > c-

Reply to this Message

ChrisR - 28 Jun 2005 21:53 GMT

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,
>
[quoted text clipped - 248 lines]
> > >> >> >
> > >> >> > c-

Reply to this Message

Duane Hookom - 28 Jun 2005 23:19 GMT

I had a typo and you missed my modification if the fields are text. You must
properly delimit the text fields.

Try (carriage returns added for readability):

SELECT UniqSecProd.Section, UniqSecProd.Product,
Concatenate("SELECT SEQ1
FROM [ProductTest2-1]
WHERE Section =""" & [Section] & """ AND Product=" &
[Product] & """") AS Pages
FROM UniqSecProd;

Signature

Duane Hookom
MS Access MVP
--

> Duane,
>
[quoted text clipped - 323 lines]
>> > >> >> >
>> > >> >> > c-

Reply to this Message

ChrisR - 29 Jun 2005 16:09 GMT

Duane,

Tried again and still not having any luck. Because the DB is so small I
shot a copy to your hotmail account to see if you had time to see what I am
doing wrong. More details in that message.

Thanks again for all of your time and help with this.

c-

> I had a typo and you missed my modification if the fields are text. You must
> properly delimit the text fields.
[quoted text clipped - 183 lines]
> >> > > without
> >> > >> passing through a crosstab. My favorite solution is found at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

> >> > >> > Just the concatenate. I used a crosstab as the only way I could
> >> think
[quoted text clipped - 146 lines]
> >> > >> >> >
> >> > >> >> > c-

Reply to this Message

Duane Hookom - 29 Jun 2005 19:20 GMT

Two issues. Again, you missed my typo since there should have been
AND Product = """
Also, Section is a reserved word. Try to fix my mistakes better in the
future ;-)

SELECT UniqSecProd.Section, UniqSecProd.Product,
Concatenate("SELECT SEQ1
FROM [ProductTest2-1]
WHERE [Section] =""" & [Section] & """ AND Product=""" &
[Product] & """") AS Pages
FROM UniqSecProd;

Signature

Duane Hookom
MS Access MVP

> Duane,
>
[quoted text clipped - 385 lines]
>> >> > >> >> >
>> >> > >> >> > c-

Reply to this Message

ChrisR - 29 Jun 2005 22:19 GMT

Success!

I swear when the query ran I heard bells and saw balloons!

Thank you so much for all of your time and help with this. Sorry it took so
long to get to the end of the line.

c-

> Two issues. Again, you missed my typo since there should have been
> AND Product = """
[quoted text clipped - 397 lines]
> >> >> > >> >> >
> >> >> > >> >> > c-

Reply to this Message