Queries--concatenate or merge records (not fields)

[Steven G.]

I have a table with one field named "Item Number". This is an alpha numeric
field. I am running a report in Oracle, then importing to MS Access to
create my table. The potential field values for this Item Number field are:

record #1 "abcd"
record #2 "abcd-1234"
record #3 "abcd-1234-efgh"

In some instances, the item number (as viewed in the Oracle report, due to
field length limitations) comes in two or three different records, as shown
below:

record #1 "abcd" (there is no problem with this record)
record #2 "abcd-"
record #2a "1234"
record #3 "abcd-"
record #3a "1234-"
record #3b "efgh"

I need a way to merge the #2 and #2a and then the #3, #3a and #3b records
into one record/field in one table. For instance, I need to pull the "1234"
from record #2a into the record #2 field value of "abcd-" to get "abcd-1234".

I assume I need to use a query to do this. Can you give me the SQL that
makes this happen, please? Thanks.

 

[[MVP] S.Clark]

I think you're assumption is incorrect. There is too much biz logic
involved to solve with just a query. Through a series of queries, strung
together with some VBA code, I think you can solve.

Maybe post the exact table structure, as this will reveal how the data is
really stored(since 2a probably doesn't really exist that way.)