Combine data

Autoeng

Autoeng

Why me?
Local time
Today, 01:55
Joined
Aug 13, 2002
Messages
1,302
Help! I'm being overrun by data!

In building my latest db I have run into a problem that I don't know how to solve. I have a table that stores part information related to engineering changes. A part can be changed by many engineering changes so a part can appear many times in the table. I want to take one field (Comments) from each record and combine it into one result.

For example

AutoNumberID.........Part Number......................Comments
12...............................100001.........................Implementation 5/03
1053...........................100001.........................Use up old stock first
2563...........................100001.........................Price increase 01/01

The return that I would like is...

100001......Implementation 05/03, Use up old stock first, Price increase 01/01


tblECNParts, PartNumber, Comments are the table and fields. I've read about using Union queries but this seems to be for multiple tables or queries. Can I use it on one table, same field, many times?
 
Interesting...

That's an interesting problem. I don't know of a query that will allow you to concatentate string data from different records (unlike summary-type queries that add numeric data).

The only solution I can think of is to use DAO or ADO to open the table, find a part number, find all recordswith that part number, concatenate the comments from those records into a string, write the result to another table, then go onto the next part number.
 
Thanks dcx693,

That's kind of what I was thinking as well. I think that it will probably take too long to perform the combination stepping through every record so I think I am going to have to find some other way around the issue (like not using the data!).

Thank you though for posting.
 
Depending of course on your setup, this type of program might actually run faster than you think. How many records do you have to go through?
 
15,000 from a linked table in another db. What do you think?
 
Do you have to have this in your table. Clearly you can display something like you want in forms or reports, so how essential is it to have it in a table?
 
Not sure I understand you Neileg. The comments field has to be stored for reference. I am using a query to fill a form, pulling data from several other tables.
 
Just thinking you can have a subform in continuous format that would show the comments you need. Perhaps not as neat as having them concatenated, but it's half way there.

Sorry if this is just mindless wanderings...
 
I think neileg is asking if you need to store the results of the combined comment fields for later use, or if you just want to view the results. Just viewing the results on a form or report would be easy to achieve, with the comment fields next to each other, but not concatenated.

All other things being equal, 15,000 rows is not that much, but it depends on how large the database is and how fast your machine is.
 
Yes Rich. I will have to fool around with it as it is about concatenating for a report not a query. I also don't know if it will work in A2K. Any ideas that you have would be appreciated. As far as why I don't use multiple fields in a continuous format I am using a Data Access Page and they don't support it.
 
I did find the same for A2K but have no idea of whether the event could apply to a DAP / query. If anyone has any ideas I would appreciate your input.
 
You must log in or register to reply here.

Similar threads

G
Filtering on Date fields problem
Replies
8
Views
674
azhar2006
A
J
Solved Column in Text Box
Replies
10
Views
503
JoséBob
J
G
Again, query where dealing with empty fields
Replies
13
Views
666
theDBguy
theDBguy
S
Getting total in one line of the query
Replies
13
Views
355
silentwolf
S
W
Solved Duplicate Items in Query
Replies
5
Views
1,152
wmix
W

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom