New Base or keep Adding tables? (Newbie needs help)

[Tom_David]

Hello,

I’m comfortable with spreadsheets… not so much with databases.

So, here’s my dilemma:
I have a base with 49 records (names of people is the primary field) - 27 people exiting the program from last year and 22 returning to continue service. There are 18 tables and 31 fields.

This year (which begins August 2020) I also need to know monthly performance (a number) for each person in 5 categories.

Not being able to link tables across bases makes me reluctant to build a new base BUT it seems like I need a lot of new tables and/or fields… 22 (people) X 12 (months) X 5 (categories). And I will need ‘end of year’ performance reports.

I can easily do this in a spreadsheet but I would like to try to use AirTable. Suggestions please.

[Justin_Barrett]

Welcome to the community, @Tom_David! :grinning_face_with_big_eyes:

Tom_David:

I’m comfortable with spreadsheets… not so much with databases.

Airtable is an interesting mix of both concepts, but skews more towards the database side of things from my experience. It can definitely take some time to grasp certain differences between spreadsheets and databases, and we’ll do our best to help with that process. :slightly_smiling_face:

Tom_David:

I have a base with 49 records (names of people is the primary field) - 27 people exiting the program from last year and 22 returning to continue service. There are 18 tables and 31 fields.

Assuming that one of your tables is the primary place for tracking the people in the program, what are the other 17 tables for? Something tells me that there’s some duplication of data, but without knowing more details about the design of your base, it’s hard to know what to suggest. The more you’re willing to share, the easier it will be for us to help!

[Bill_French]

Tom, also a big welcome to the community!

Tom_David:

22 (people) X 12 (months) X 5 (categories)

There are many questions about your information, but it probably makes sense to start with the obvious.

Why 18 tables? If there are 31 fields across 18 tables, is there an average of just 1.89 fields per table?

The system you describe seems pretty simple and I suspect we’re dealing with some verbiage issues whose resolution will be critical for anyone to help you.

Blindly, I will offer these observations…

• Categories should be a data attribute of a record, not a field.
• Month of activity should be a data attribute of a record, not a field.
• People (or person) should be an attribute of a record, not a field.

Is it possible you are assuming there must be unique fields for each category?

[Tom_David]

Thank you.

• …data attribute of a record.
• Yeah, that’s not clear??- Is it possible you are assuming there must be unique fields for each category?
• Yes, I expected the record field would be linked to the table which would contain the 5 category choices.- The tables I created in this base contain variables like gender, age group, service locations, programs, events, etc.
• So, added a field to the records for each of these.

It might be useful to simply look at the spreadsheet I’m trying to add into the existing AirTable base - let me know and I’ll clear the personal data and send it…

[Celeste_Bancos]

I would keep everything in the same base. The nice thing about Airtable is it has easy ways of filtering out data that you don’t need to see. If you have a table with a lot of columns, you can create different views that hide different columns, depending on what’s relevant to the task at hand.

The real power comes in when you split up your spreadsheet into various tables that relate and link to each other.

Posting a version of your spreadsheet without the personal data is a good idea. That will help us figure out the best way to translate that spreadsheet into a database format.

[Tom_David]

Could not reply with attachment (spreadsheet workbook). How can I post it to the forum?

[Celeste_Bancos]

Could you upload to Dropbox or similar and post a link?

[Tom_David]

Sure. Here’s a link.

[Celeste_Bancos]

Here’s my attempt to replicate the functionality of the spreadsheet you shared. It uses pivot table blocks, which are a Pro plan feature of Airtable.

Airtable

Tom David Sample Base - Airtable

Explore the "Tom David Sample Base" base on Airtable.

You can click the button at the top right to create your own copy of the base so you can take a look at the inner workings.

Let me know if you have any questions about how any of it works!

[Tom_David]

Thank you. That will give me something to train with as I try to become more familiar with AT.