Merging Columns of Data

[Guest]

In Access I have a large database with 7000+ records. This appears as columns
in Form format.

On the Form I want to combine the data from 6 of the columns into one (text)
column, there to appear neatly one above the other, as:

10/04/02 - Birmingham
12/12/03 - London
03/01/04 - Peterborough
17/10/05 - Norwich
02/02/06 - London
16/11/06 - Brighton

I then need to be able to add more data in the same format in the future.

I am using Vista Home Premium and Office 2007.

Can anybody please tell me - step by step so there will be no mistakes
(especially as I am not very experienced with Vista/Office 2007 as yet) - how
I can do this? Assuming it
is possible.

Many thanks.

 

[Guest]

You can likely use a Union query to help achieve this goal. Here is a
tutorial on creating Union queries:

http://home.comcast.net/~tutorme2/samples/unionqueries.zip

It was written several years ago, so the data is certainly not valid any
longer. The concept remains perfectly valid.

10/04/02 - Birmingham

I would have two fields: a date/type data type and a text data type. This
will make querying data a lot easier in the future.

Can anybody please tell me - step by step so there will be no mistakes...

Sorry, I'm not using Access 2007 yet. However, you should definately make a
back-up copy of your database before running any action queries.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[John W. Vinson]

In Access I have a large database with 7000+ records. This appears as columns
in Form format.

<chuckle> 7,000,000 records is a "large" database. 7000 is very modest.

What's the structure of your Table? Data is not stored in forms; it's stored
in tables, and just displayed and edited in forms.

On the Form I want to combine the data from 6 of the columns into one (text)
column, there to appear neatly one above the other, as:

10/04/02 - Birmingham
12/12/03 - London
03/01/04 - Peterborough
17/10/05 - Norwich
02/02/06 - London
16/11/06 - Brighton

I then need to be able to add more data in the same format in the future.

That sounds like a very good idea if you currently have six fields - but it
looks like you may have twelve. Storing both a date and a city name in the
same field would be a Very Bad Idea.

I am using Vista Home Premium and Office 2007.

Can anybody please tell me - step by step so there will be no mistakes
(especially as I am not very experienced with Vista/Office 2007 as yet) - how
I can do this? Assuming it
is possible.

It's quite possible, but it would be very helpful if you would post the
current structure of your table: tablename, fieldnames, sample contents.

John W. Vinson [MVP]

 

[Guest]

<chuckle> 7,000,000 records is a "large" database. 7000 is very modest.

What's the structure of your Table? Data is not stored in forms; it's stored
in tables, and just displayed and edited in forms.


That sounds like a very good idea if you currently have six fields - but it
looks like you may have twelve. Storing both a date and a city name in the
same field would be a Very Bad Idea.


It's quite possible, but it would be very helpful if you would post the
current structure of your table: tablename, fieldnames, sample contents.

John W. Vinson [MVP]

Hi John

The table is called simply Locomotives and is for recording sightings and
photographic data of same.

Most of the fields have no relevance here though I can copy the entire table
directly to you if you wish (it would take too much space here).

All fields are text fields.

Currently they would look something like (92001 being the locomotive number):

92001 | 10/04/02 - Birmingham | 12/12/03 - London | 03/01/04 - Peterborough
| and so on, taking up a total of 7 fields - which become 7 columns in my
Form format.

I wish to 'stack' fields 2 - 7 in one field, so the end result would look
like:

92001 | 10/04/02 - Birmingham
12/12/03 - London
03/01/04 - Peterborough
17/10/05 - Norwich
02/02/06 - London
16/11/06 - Brighton

so fields 2-7 are now all in one field, and I can add further entries below
16/11/06 in the future.

It has been suggested that I export the table to Excel and merge the
relevant columns there, and then re-import the table, but I don't have the
skills to do that and don't even know if it is possible.

Many thanks,

Brian (swiftsilver)

 

[Guest]

Hi Brian,

I wish to 'stack' fields 2 - 7 in one field, so the end result would look
like:

92001 | 10/04/02 - Birmingham
12/12/03 - London
03/01/04 - Peterborough
17/10/05 - Norwich
02/02/06 - London
16/11/06 - Brighton

so fields 2-7 are now all in one field, and I can add further entries below
16/11/06 in the future.

I suggest instead that fields 2-7 become two fields, something like this:

LocoNum | TripDate | City
92001 | 10/04/02 | Birmingham
92001 | 12/12/03 | London
92001 | 03/01/04 | Peterborough
92001 | 17/10/05 | Norwich
92001 | 02/02/06 | London
92001 | 16/11/06 | Brighton

The locomotive number, LocoNum, would be a foreign key in a many side table
that relates to the same LocoNum value in a one side table, that includes
attributes of the locomotive.

You should be able to use a union query to "stack" fields 2-7 into one
field, as you indicated, and then further use an update query to split this
data into two fields. As an alternative, you could export the stacked result
to Excel, and use Data > Text to Columns to help split the data, using the
hyphen as the delimiter.

The problem with keeping fields 2-7 in one field is that this is still not a
normalized design. For example, it would be very difficult to create a report
that was grouped by City. With a properly normalized design, such a report
becomes child's play. Here is a link to several database design papers that
are worth reading:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Make sure to read the paper titled "Understanding Normalization", by Michael
Hernandez.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________