Need help colapsing multiple rows into a memo field

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I get a data source regularly that I pull into Access as a temporary
table then manipulate. This table has multiple rows for each item on a
order but in truth 85% of the columns are repeat data. What I care
about is the order itself, only for informational purposes do I want
the model types and quantities of the items in the order but that is
it.

I would like to find a way to stuff this data into a memo field with a
caridge return after every item so I would still have a single row for
every order and then a memo with the packing list.

I can remember doing this in the past with foxpro utilizing a variable
in the select statement but I have no idea how to accomplish this in a
access query.

Any help would be appreciated.

Thanks
 
Does your data have something that says each of the multiple rows belong
together like an order number in each?
 
Yes the table has data like

ID Order Company Model Qty
1 1112 ABC 31 1
2 1112 ABC 17 4
3 1112 ABC 25 8
4 1113 ACME 31 3

I want it to look like this

ID Order Company Packing List
1 1112 ABC 31 1
17 4
25 8
2 1113 ACME 31 3
 
Yes the table has data like

ID Order Company Model Qty
1 1112 ABC 31 1
2 1112 ABC 17 4
3 1112 ABC 25 8
4 1113 ACME 31 3

I want it to look like this

ID Order Company Packing List
1 1112 ABC 31 1
17 4
25 8
2 1113 ACME 31 3
Click to expand...

Then do so in a Report. Copying this data into a non-atomic, redundant
Memo field would be the height of bad design!

John W. Vinson[MVP]
 
Hum, I would think repeating so much data and creating a level of
detail I do not need to manage would be bad design. In this case the
data in the memo field is like a glorified "special handling
instructions" field. To complicate things this data gets fed to yet
another ticketing system and my interface is very limited. So having
this bunched together as a chunk of text is very benificial for that.

We could argue data normalization endlessly and I am sure you are
likely to be correct especially since my days of focusing on code are
long past. That said I can not see the value of wasting bits on this
and I am optimistic that you or someone else can help me conquer the
problem as I oulined it.

Thanks in advance.
 
Hum, I would think repeating so much data and creating a level of
detail I do not need to manage would be bad design.
Click to expand...

No repeats needed. I'd suggest two tables in a one to many
relationship.
In this case the
data in the memo field is like a glorified "special handling
instructions" field. To complicate things this data gets fed to yet
another ticketing system and my interface is very limited. So having
this bunched together as a chunk of text is very benificial for that.
Click to expand...

That "bunch of text" can and should be generated on the fly in a
Query.
We could argue data normalization endlessly and I am sure you are
likely to be correct especially since my days of focusing on code are
long past. That said I can not see the value of wasting bits on this
and I am optimistic that you or someone else can help me conquer the
problem as I oulined it.
Click to expand...

Sure, if you insist on wasting database space and storing data
redundantly, where the data in the memo field is INTENDED to be
identical to the data in the table (but either one can be edited so
you have no guarantee that it will be). Adapt the VBA code at

http://www.mvps.org/access/modules/mdl0004.htm

using vbCrLf instead of ", " to separate the values.

John W. Vinson[MVP]
 
You will need to have the Order field datatype number. You need to download
a module created by Duane Hookom to do the concatenation.

http://www.rogersaccesslibrary.com/...neric Function To Concatenate Child Records'e

Then use these two queries.
Scott_1 --
SELECT Scott.ID, Scott.Order, Scott.Company, [Model] & " " & [Qty] &
Chr(13) & Chr(10) AS [Packing List]
FROM Scott;

Scott_2 --
SELECT Scott.Order, Scott.Company, Concatenate("SELECT [Packing List] FROM
Scott_1
WHERE [Order] =" & [Order]) AS Packing
FROM Scott
GROUP BY Scott.Order, Scott.Company, Concatenate("SELECT [Packing List] FROM
Scott_1
WHERE [Order] =" & [Order]);
 
Back
Top