I want to add a column to my database

[tyaughton]

To start, I will say that my experience with databases is rudimentary at best, although I've worked with them, on and off, for some 30 years. My last program was Lotus Approach, but it did not work well with Windows 7, so I changed to OpenOffice. I managed to import my files, and can work with the queries and documents, but the one thing that has me stumped is adding a column to the database. I am tracking an annual club membership roster and would like to either add a column for 2017 or modify an old column to reflect the new year. Any attempt to do this crashes the program and while the recovered file shows all the fields, there is no data. I go back to the original and there is no added column. How do I do this without having a degree in programming?

Thanks!!!

[Villeroy]

With a proper database design you never have to add any columns for new categories, years, months etc. Databases grow vertically but not horizontally.

See https://support.microsoft.com/en-us/kb/283878

[UnklDonald418]

I am tracking an annual club membership roster and would like to either add a column for 2017 or modify an old column to reflect the new year.

One approach would be to add a new table something like

Code: Select all

CREATE TABLE “DuesPaid”(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
“MemberID” Integer,
"DuesPaidDate" DATE,
"DuesPaidAmount" DECIMAL (10, 2) NOT NULL
);

This table would then be linked via the MemberID to the rest of the member data.
This approach would allow you to log and reference multiple years of data for each member without needing to modify your tables annually.

[tyaughton]

My brain is getting too old to digest this stuff. Perhaps I would do better to move my information to a spreadsheet. The question then would be can I use the information in a spreadsheet the same way as the database and create letters and forms from that information?

[Villeroy]

This is the very same stuff since the 70ies when relational databases and SQL came up. No matter which software you use, you may get a hard time gathering information from unnormalized tables.

This is how you change the table layout in Base:
Right-click the table icon and choose "Edit".
Append a column, save the table and the database.

This is how you copy a database table into a sheet:
Open your database, copy a table icon (do not open the table) and paste into a spreadsheet.

[UnklDonald418]

There are numerous questions in the Calc Forum where someone is trying to coerce a spreadsheet to function like a database, usually with limited success.
There is some overlap in functionality between spreadsheets and databases but in your case I believe a database is the best tool.
I have uploaded a simple example database showing how the approach I detailed above might work.

Members.odb

(13.17 KiB) Downloaded 232 times