Creating a survey form in Base

My first post here, so please free to correct my posting etiquette! :slight_smile:

I’m new to databases, and my big objective is to create a Project Database that I can use in my one-person carpentry business (I design and build fitted furniture to install in people’s houses). I’ll eventually want my Project Database to do a number of things, but I’m trying to break it down into manageable pieces so that I don’t get lost.

The first piece I would like to figure out is how to reproduce in database form the functionality of my ‘Survey worksheet’ Survey worksheet.ods (38.0 KB) that I print off and fill in by hand, and then clip to my desk while I’m designing the job so as to have relevant details in front of me. I separately have a print-out that lists job-specific notes and queries that I write by hand. I’d like to replace both of these procedures with a database solution that is more reliable and easier to use.

There are many issues with doing it the way I’m currently doing it, and I’m very open to any suggestions that anyone has, but what I think I’d like the database to do in terms of goals is to generate a report I can print out to have in front of me that:

  • Lists all the questions and answers related to that particular job_id, where the answer is not “N/A” (i.e. only relevant questions and answers)
  • List all the job_note(s) associated with the job_id
  • Details a ‘chase-up date’ to follow up with my client if I haven’t heard from them (probably survey_date + seven days)

Some parameters:

  • Every job has the same set of questions, many of which are not relevant. That’s OK, I’d rather take a couple of minutes to say ‘N/A’ to a bunch of questions than potentially miss something which might be critical.
  • I’ll be filling in this form on a laptop while at my client’s house
  • I’d like to have a long form with all the questions in one long column - is this possible in Base?
  • My first thought is that I’d like to have a list box next to each question with only the possible answers, but I’ve read that this might be difficult
  • Macros scare me! :slight_smile:

I’ve started putting together a database
Survey.odb (5.2 KB)
just to get the concepts in my head, and although I tried some forms with filtered list boxes I got completely stuck and deleted them. Any comments on the structure of the database are welcome.

I’ve been looking at Can I limit a list box based on previous list box data and Apache OpenOffice Community Forum - [Example #1] Filter/Search with Forms (leveraging SubForms) - (View topic), but not quite sure if this is where I should keep digging.

I can only apologise that this is such an open-ended question, I’d love it if anyone had specific points for me, but I’m also really happy to just be directed to materials where I can try and learn how to do what I’m trying to do. The whole topic of databases has been daunting for me, I’ve worked through Mariano Casanova’s tutorial, which was really helpful, and got partway through The Frugal Computer Guy’s video tutorial on Youtube. I can upload a database I’ve already built if it’s helpful to get a sense of the (low!) level that I’m operating at.

Thank you for the help!

Multiple_Choice.odb (84.1 KB)

A database of multiple choice tests with some forms.
Folder “Preparation”:

  1. Q&A where you define questions and answers
  2. Participants is a list of persons
  3. “Tests” where you bundle questions and participants to scheduled tests.

Then there is one form for the personal test and “Resolve” where completed tests are evaluated.

Thanks so much for this Villeroy, it looks like it has a lot of parallels to what I’m trying to do. I will look it over in detail and try to understand what is happening! :slight_smile:

okay, maybe at this point, you should already invest a bit (and probably a significant amount) of time in Calc, so that it actually does provide some functionality

(currently it’s not more than a plain text file)

Once you get a sense of what the columns can provide as functionality – i.e. implenting some logic in simple formulaes – you can start thinking of more advanced ideas;
but Base is definitely not what you need for now.

well, no need to for apologies :wink: it’s actually impressive to see the motivation you put in this.
maybe you can find some sort of local IT community around you to discuss general ideas and have a glimpse at formalizing and programming.
best wishes !

Hi B,
A database has tables, each table has fields that make sense being together.

A customers name and address would be a perfect example of such a table and have approximately 15 fields or less.
Fields: prefix, firstName, lastName, street/boxNumber, street, city, zip, activeDate, lastPurchase, Etc.

The only reason you go through the effort of making this one simple table is to answer future questions about hundreds if not thousands of customers in that table .

What customers purchased something in the xxxxx zip code area in the past year.

For it to work dates and zip codes would have to follow strict rules of data entry. Eg no letters in zip field.

Your survey has hundreds of pieces of random data, but is interesting.

Hope this helps