Combine multiple rows into one row/column

[boats02]

Good evening:
I have an Access query that lists customer subscriptions to our products for
the current month and the 7 preceeding months (One column for each month). A customer can subscribe to more than one product,
therefore the subscriptions are listed multiple times each month. (one record for each
subscription).
I need to combine the records for each customer, for their current
product(s).

Example:

ABC COMPANY__________SUBSCRIPTION 1
ABC COMPANY__________SUBSCRIPTION 2
ABC COMPANY__________SUBSCRIPTION 3

How do I combine the subscriptions into one column instead or multiple rows?
(see below) Possibly, but not necessarily, comma delimited.

Like this...
ABC COMPANY_________SUBSCRIPTION 1, SUBSCRIPTION 2, SUBSCRIPTION 3

 

[TravelerOn]

In your query, go to a blank column and in the FieldName row you are going to "create" a new "field" that combines the three.

I can best show you this by example...in my table I have a field for First Name, Last Name and Trial ID. In my query, I go to my blank column and type in the first row:

FullName:[Last Name] & ", " & [First Name] & " " [Trial ID]

The brackets are only necessary because I have spaces in my field names....you MUST use the ampersands or it won't work. The quote-comma-space-quote is a bit of vanity...it will make the end result of John Tiber 1341 look like: Tiber, John 1341. The second quote-space-quote is for coherence...if you don't tell access to insert a space, it won't happen.

The query will now treat "FullName" as a new row, but won't allow you to edit it or add it to the table. An update query will.

You can now call this field into reports, forms, whatever you need...

Looking back, I think you'd want a field like this

FullSubscription:[ABC Company] & " " & [Subscription 1] & ", " & [Subscription 2] & ", " & [Subscription 3]

Try that...
Kate

 

[TravelerOn]

One more thing...you may need to include the actual fields in your query before you can condense them, but I won't swear to it. In other words, all subscription fields will need to be in there and then you can go to the blank and fire away!

Kate

 

[boats02]

Thank you for taking the time to reply.
In your example, the query will concatenate different fields into one field.
What I really need is to concatenate different records into one record. This would be similar to a crosstab query except that I would need to muliple column headers and Access only allows you one.

Try to visualize this. I have a query viewed in "datasheet view". My left hand column contains account id's. Following from left to right I have columns representing different months. (sort of like a crosstab) The values in each of the month columns contain the respective accounts subscription for that month.

Problem: Because an account can have more than one subscription to more than one product each month, I end up with account numbers listed more than one time.

I know I'm not doing the best job of descriping this, but if it would help, I'd be happy to send you a screen shot via email.

Thanks again for you help. Let me know what you think.

 

[David R]

You're probably going to have to use a report, not a query. Look up 'snaking columns' in the MS Access help.

And as Pat H warned me, this ONLY applies to Reports, not queries or forms. You'll find a few topics on snaking columns in these Forums as well (search all Forums, I forget which it's in).

HTH,
David R

 

[Rich]

Where have the seven columns come from, is this a crosstab query or are there 7 columns in your table?

 

[boats02]

To look at this results of my current query in datasheet view, you would think that it is a crosstab query. However, in reality, it's 7 different sub-queries that are brought together in the current query.

Each sub-query returns the subscription that the customer had for a given month. Thus, I have 7 sub-queries that pull data for their respective month. Now I just need to combine the data for each month for each account. It's currently returning 1 - 3 rows for each accounts. To use my existing query with anything else, I end up with a many-to-many relationship. I need a one-to-many.

I could email you a screenshot of the data. It might make more sense than my explanation.

 

[ChrisLL]

Howdy,
I am trying to do the exact same thing. I think concatenating the fields is a good idea but not in a query. I just started to create a s routine that will step thru the recordeset and for each customer(id), it will create a string of (variable length)the subscriptions in one row.

The other (easiest)way would be thru a report that is grouped. Ihave to look into the snaking thing.

Chris

 

[ChrisLL]

I have done this in code to fill a form in datasheet view and it looks pretty good. The code basically just concat. the many side for each of the one on the one side. All of this is put into one row(record)on the datasheet. Email me if you would like to look at the code. It is not the only way to get it done but it's the way I figured out.
Chris