mass add the same prefix to all columns in a table

[trolleri]

My database has three tables with many columns. The three tables are identical in the names of their columns.

I want to copy all columns from all three tables together into one single table, giving the respective columns prefix table1-, table2-, table3- since the columns would otherwise be indistinguishable.

I already tried to search the board for "table columns add prefix".
I use Access 2010
I managed to copy all columns together into one table through design-view, but cannot figure out the "add prefix" step.

 

[plog]

I'm fairly certain you are going about this the wrong way. Let's say each of your tables has N fields. Your final datasource (this may or may not be a table) should have N + 1 fields, not N*3 fields.

Instead of prefixing each column with where it is from (table1-Field1, table1-Field2...) you should instead keep the same structure and add a new field which tells where that row's data is from. Like so:

Field1, Field2...TableSource
1, 1/13/2013..."Table1"
2, 1/14/2013..."Table2"

Now to determine if this is to be a table or a query: Is this a process you are going to need to repeat? If so, describe how it will go in the future--will you clear out old data and replace it all or will you append new data to existing data? What will you be using this one table for?

 

[trolleri]

Every row represents a specific person. Every person are going to answer different questionnaires at different times. The specific problem is that one questionnaire with 90 questions is to be answered three times, and in different weeks, hence the prefix-identifiable fieldnames.

When I have created all the columns in the table for all the different questionnaires (not only the same one x3), I am going to create a form to easy fill in the answers. As a specific request I am also going to create a search field for a persons ID. This is needed, since the questionnaires from one person are to be answered at different times, and thus recorded in the database at different times.

I just imagin it will be easier to maintain the complete overview, if all the fields for one person are to be kept in the same table, and in the same row, but with unique names of the columns of course.

 

[plog]

After your explanation, I am now certain my method is correct. You should not prefix your field names with the table they are from. You should store either a date with each questionnare or a number which identifies the iteration of it.

 

[trolleri]

And keep more than one table? I just ran into the problem of me wanting more than 255 fields (90 x 3) exceeding the limit for one table.

 

[plog]

Read my first reply. Instead of merging your 3 tables into 1 table with prefixed field names, you should have 1 table with a field to designate the difference between the rows (N + 1, not N*3).

Also, I am sure there are templates out there for questionare databases. I'd search for a few and see what you can learn about their table structures.

 

[Pat Hartman]

You have other problems also. You shouldn't have a column for each question. You should have a related table so you can have a row for each question. To do any analysis on your survey, you'll need a minimum of 90 queries!!!! If you normalize the table, you will only need 1

If you search here, you should find at least one survey example that provides a flexible structure.

 

[trolleri]

I find it very strange that there is no complete tutorial on the subject, and when I search, I find lots of different approaches!

Do you recommend somthing like this?
functionx.com/access2003/applications/survey.htm

 

[trolleri]

I also posted another question about the setup of a search-button on the form. This post explains a bit more about the database I am going to create:
access-programmers.co.uk/forums/showthread.php?t=240701

 

[trolleri]

For clarification:

--
My project:
--
Approximately 200 patients are going to answer more than 900 questions split out on different questionaries they get handed out as printed paper. They do not answer all the questionaries at once, but at seven different visits at the doctor, the last being six months after surgery. All these papers needs to be recorded into a database as data, and later exported as tables to an statistics program called SPSS.

With MS Access it is possible to make an navigational form with one pane or subpane for each of the different questionaries to be recorded about the patient. It has then been requested that all data is visible from one form only, and with the following structure:

To the left: An identification-area where the patient with his/hers inclusion number, and other static values like "gender" and "birthdate". The inclusion number -field needs an search function, so it is possible to find and continue recording on patients already in the records (already in one or mere tables). It is not practically possible to have all data about one patient ready at the same time.

To the right: Different navigational panes (one for each questionary). It is crucial that these panes are all connected to the identification-area to the left, so data from one patient stays connected to the excact same patient.
--
My design idea:
--
My idea is to first make one tabel for each questionary, with question names as field names, then an autonumber field (QuestionaryAnswerID), and then one identification field to connect records from all the different tabels to one patient (the patients inclusion number).

Then I make the navigation form with panes for each tabel, and finish the form-panes off by making some predefines answers as dropdown menues on those fields that allow predefined answers (all numerical/boolean).
--
My new questions:
--
How can the search-button be done?
Is it possible to export all the patient-connected data in one file e.g. Excel document (have a limit of 1,048,576 rows by 16,384 columns)

thanks in advance
T