Merging multiple fields on one row into a single field in a 2nd ta

E

efandango

I am trying to merge the contents of multiple fields on one row, as per the
example below, where i want the data from Run_Point_Venue_X and
Run_Point_Address_X to populate just two fields in a new table.

The two new field names will be: Run_Point_Venue_All and Run_Point_Address_All

Example Fields:

Run_point_List_ID Run_No Run_point_Venue_A Run_point_Address_A
Run_point_Venue_B Run_point_Address_B Run_point_Venue_C
Run_point_Address_C Run_point_Venue_D Run_point_Address_D

To put another way, I were using Excel as an example, I want to take pairs
of cell contents from multiple rows and place them all in just two columns.

I have tried using multiple queries via a macro, but the macro just loops
endlessly, generating duplicates of the fields.

Can this be done in Access?
 
K

Ken Snell \(MVP\)

I believe you want to use a UNION query; it allows you to combine data from
different fields/tables into a single output. Hard to tell from your post
what the field names all are, but here is a generic example of a UNION
query:

SELECT VenueFieldName1 AS Run_Point_Venue_All,
AddressFieldName1 AS Run_Point_Address_All
FROM TableName
UNION ALL
SELECT VenueFieldName2, AddressFieldName2
FROM TableName
UNION ALL
SELECT VenueFieldName3, AddressFieldName3
FROM TableName;
 
J

John Vinson

I am trying to merge the contents of multiple fields on one row, as per the
example below, where i want the data from Run_Point_Venue_X and
Run_Point_Address_X to populate just two fields in a new table.

The two new field names will be: Run_Point_Venue_All and Run_Point_Address_All

Example Fields:

Run_point_List_ID Run_No Run_point_Venue_A Run_point_Address_A
Run_point_Venue_B Run_point_Address_B Run_point_Venue_C
Run_point_Address_C Run_point_Venue_D Run_point_Address_D

To put another way, I were using Excel as an example, I want to take pairs
of cell contents from multiple rows and place them all in just two columns.

I have tried using multiple queries via a macro, but the macro just loops
endlessly, generating duplicates of the fields.

Can this be done in Access?
Click to expand...

It can. It shouldn't.

Your table is misdesigned to start with - you're embedding a
one-to-many relationship in each record.

You're trying to make it even WORSE from a design standpoint - storing
multiple items of information in a single field. Fields should be
"atomic", having only one value. You're trying to create multivalue
fields.

Note that Text fields are limited to 255 bytes; presumably your venues
and addresses are larger than 10 bytes each, so you'll run out of room
trying to jam 23 of them into a single text field.

A better design would have THREE tables to model the one to many
relationship:

Runs
Run_Point_List_ID <Primary Key>
Run_No (perhaps this should be the primary key? I don't know)

Venues
VenueID <Autonumber, Primary Key>
Venue <text, name of the venue>
Address1
Address2
City
State
Zip <<< or other locally appropriate atomic address fields

RunPoints
Run_Point_List_ID <link to Runs>
SeqNo <sequential step in the run, your A, B, C...>
VenueID <link to Venues>

You can DISPLAY the data all combined into one field if you wish,
using either table design; though I can't imagine that a text field
containing (say)

Blues BouquetStudent Activities BuildingBorders Books

would be particularly useful, even if the venue names were separated
by commas or spaces!

To answer your literal question: as bad an idea as this is, you can do
it using an Update query. Create your new table with two Memo fields
(which can hold 65536 characters, not just 255). Create an Append
query from your existing mess... umm... spreadsheet... ummm.... table,
using calculated fields

Run_Point_Venue_All: Run_Point_Venue_A & Run_Point_Venue_B &
Run_Point_Venue_C & Run_Point_Venue_D & <and so on and so on...>

and similarly for the addresses. You can use

& ", " &

in place of each ampersand to put commas between the venues, or

& Chr(13) & Chr(10) &

to put each venue on a new line.


John W. Vinson[MVP]
 
E

efandango

John, I think I mislead you (my bad explanation/english), I don't want to
merge the contents of all the fields into just two fields, I tried to explain
it easier by using the Excel sheet/cells example, but on the basis that a
picture paints a '000 words, i have posted an online example of my problem,
which i hope is a clearer definition of what i want to do. please take the
time to click here to see it.

http://homepage.ntlworld.com/ejay99/flat2/msacess_problem.htm

regards

Eric
 
T

Tom Wickerath

Hi Eric,

Your picture came through fine this time. One method of accomplishing this
goal is to first create a union query, and then base a make table query on
your union query. A union query cannot be represented in the familiar QBE
grid, so you might want to start with individual SELECT queries.

I didn't attempt to set up a test with an actual table and data, to verify
the SQL, so hopefully it will work the first time, but try the following SQL
statement. To use this statement, create a new query. Dismiss the Add Tables
dialog without adding any tables. In query design view, click on View > SQL
View. You should see the word SELECT highlighted. Copy the following SQL
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword:


SELECT Run_point_List_ID, Run_No,
Run_point_Venue_A AS Run_Point_Venue,
Run_point_Address_A AS Run_point_Address
WHERE Run_point_Venue_A IS NOT NULL AND
Run_point_Address_A IS NOT NULL
FROM [Run_Points]

UNION

SELECT Run_point_List_ID, Run_No,
Run_point_Venue_B AS Run_Point_Venue,
Run_point_Address_B AS Run_point_Address
WHERE Run_point_Venue_B IS NOT NULL AND
Run_point_Address_B IS NOT NULL
FROM [Run_Points]

UNION

SELECT Run_point_List_ID, Run_No,
Run_point_Venue_C AS Run_Point_Venue,
Run_point_Address_C AS Run_point_Address
WHERE Run_point_Venue_C IS NOT NULL AND
Run_point_Address_C IS NOT NULL
FROM [Run_Points]

.....etc.

ORDER BY Run_Point_Venue


Replace the "...etc." part shown above with similar SELECT statements. I
recommend that you test as you are building it up, to make sure that it
continues to work as expected. A union query has to include the same number
of fields in each SELECT statement, and the field names must be the same. We
accomplish the same field names by aliasing each column (ie. Select FieldName
AS AliasName).

After you get a union query that returns the desired results, then create a
make table query that uses your union query as it's source of data.

Good Luck!


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
E

efandango

Thanks Tom, that did the trick.

Tom Wickerath said:
Hi Eric,

Your picture came through fine this time. One method of accomplishing this
goal is to first create a union query, and then base a make table query on
your union query. A union query cannot be represented in the familiar QBE
grid, so you might want to start with individual SELECT queries.

I didn't attempt to set up a test with an actual table and data, to verify
the SQL, so hopefully it will work the first time, but try the following SQL
statement. To use this statement, create a new query. Dismiss the Add Tables
dialog without adding any tables. In query design view, click on View > SQL
View. You should see the word SELECT highlighted. Copy the following SQL
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword:


SELECT Run_point_List_ID, Run_No,
Run_point_Venue_A AS Run_Point_Venue,
Run_point_Address_A AS Run_point_Address
WHERE Run_point_Venue_A IS NOT NULL AND
Run_point_Address_A IS NOT NULL
FROM [Run_Points]

UNION

SELECT Run_point_List_ID, Run_No,
Run_point_Venue_B AS Run_Point_Venue,
Run_point_Address_B AS Run_point_Address
WHERE Run_point_Venue_B IS NOT NULL AND
Run_point_Address_B IS NOT NULL
FROM [Run_Points]

UNION

SELECT Run_point_List_ID, Run_No,
Run_point_Venue_C AS Run_Point_Venue,
Run_point_Address_C AS Run_point_Address
WHERE Run_point_Venue_C IS NOT NULL AND
Run_point_Address_C IS NOT NULL
FROM [Run_Points]

....etc.

ORDER BY Run_Point_Venue


Replace the "...etc." part shown above with similar SELECT statements. I
recommend that you test as you are building it up, to make sure that it
continues to work as expected. A union query has to include the same number
of fields in each SELECT statement, and the field names must be the same. We
accomplish the same field names by aliasing each column (ie. Select FieldName
AS AliasName).

After you get a union query that returns the desired results, then create a
make table query that uses your union query as it's source of data.

Good Luck!


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

efandango said:
Tom/John,

Cn you try this url please:

http://homepage.ntlworld.com/ejay99/flat2/index.html
hoprfullythis will work, otherwise can I mail you guys a JPG (or any other
image format you may prefer?)
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Merging multiple fields on one row into a single field in a 2nd ta 1
Union fields in the order they appear in the table 17
How do I express more than 2 SELECT FIELDS within the same table? 3
returning multiple records on a single line 3
Macro to break one row of data into multiple rows, with calcuation 1
Concatenating multiple values into a single field on a report 5
Using a macro to import field names into a spreadsheet and place them in different fields 0
Still more on loading a .CSV into a .XSLS 15

Top