Struggling for design

[VBAhole22]

This problem has been keeping me awake at night for 3 days. Hope I can explain it well enough to get some help. I'm trying to design a database with the following characteristics:
Users will enter info at quarterly intervals into this database that has to store it and generate reports on the data. There are 5 sections that need to be completed. Each section has the same 4 text boxes that need to be completed (the BIG4) plus additional fields that depend on what section you are in. Some of these additional elements are text some are numeric. So the trouble I'm running into is this: If I store all of the fields (big4 and additional elements) in one table then I will have tons of null entries because the additional elements only apply to one of the 5 sections. The approach I took was to make a BIG4 table to store those answers for all sections and then make tables for each of the sections to store these answers in each section's own tables. Now I'm having issues rectifying these 6 tables so I can get them into a report and wondering if I took the wrong approach.

Is this making sense to anyone? Anybody ever had this problem. One solution posed to me involved making a login table for each entry made and use a unique entryID to join all of these disparate tables. I haven't been able to make that work either.
Arrrggg!
Honestly, any tips greatly appreciated

 

[dynamictiger]

Just a thought, I don't know how practical this would be.

Make a table of the big four with ID.

Make a table of Dates and relate the bigfourID to that. This then generates a uniqueID for the date and question.

Use the uniqueID as the relating table to one or two new tables for the text and or numeric data.

Off the top of my head without any thought or real knowledge of the outcomes, I think this will work.

BigFourID
BigFourStatement

DataID
EntryDate
BigFourID

NoteID
DataID
NoteEntered

NumberID
DataID
NumericEntered

 

[Oldsoftboss]

Go Bombers

 

[cogent1]

Why have more than 3 tables? The Big 4 can be lumped all together in one table. All subsidiary questions can be in another table. A third linking table can relate section IDs in the subsidiary table to Big4 IDs.

 

[VBAhole22]

cogent1,

In your strategy I have a flat table to hold all subsidiary answers for all sections. This means that when a user answers questions for section 2, say, then all other fields in that table are null for all other sections. That just seemed like a great waste of space to me, but amybe not since they are null. But what about generating a report based on this table scheme. I suppose I would know the section that answers were posted (based on the section id the user would enter) to so I could query out just those fields in the subsidiary table.

My current working solution (that I came up with in a nightmare last night) is this: One form that has 2 subforms. The main form is based on Login:

EntryID
AuthorID
Year
Quarter
SectionID

The first subform is for the BIG4:

EntryID
1
2
3
4

The second subform is actually a container that can hold 1 of 5 subforms based on SectionID:

EntryID
subsidiary questions (whatever they may be)

This gives me 2 tables(login and big4) + 5 tables (one per section). Trouble comes when trying to query this for a report.

 

[cogent1]

No, it's not a flat table, that's the whole point!



The Big 4 appear in EVERY section. Right?
The subsidiary questions may appear in ANY section, ie, they are not unique to a section. Right?

If this is so, you don't even need two tables for your questions. The questions are in a one to many relationship with their sections. So, create a table with ALL your questions in and give each row a unique QuestionID. Now, create a second table containing a unique AutoNumber Primary Key called LinkID, a Foreign key named QuestionID and a Section field. Join the QuestionID fields in the relationship window and enforce referential integrity.

Question1(a big4) has 5 entries in the Section table, corresponding to each of the sections.

Question 44 (subsidiary)has two entries, because it's only used in sections 1 and 5.

Nb-the table has just three short numeric entries, so it's not bloated. No table has any null fields at all in this scenario.

Queries are used to call up the data according to section.
A third table relating to the quarterly results will have to be set up, of course, but it doesn't affect this basic structure.

 

[VBAhole22]

Now I'm really confused.

<The Big 4 appear in EVERY section. Right?> True
<The subsidiary questions may appear in ANY section, ie, they are not unique to a section. Right? > False

The subsidiary questions are section specific. So when a user enters that they would like to make an entry for section 5 they get the big4 and then they get section 5 subsidiary questions. This is why I feel the need to make separate tables for each section. The questions need to be fields in a table because the entries (per quarter) will be records in that table and need to correspond to an author at a point in time.

Maybe we are both talking about the same thing?

If I lump all of the section-specific subsidiary questions in one table then there will be nulls across the board except for the section being addressed. If I put them in 5 different tables then I have one table in a relationship with 5,6 or 7 other tables.

Man this is frustrating. On top of it all I have to type these questions into labels on a form and a report? Even though I have them in a table already. I've thought about making a shell and loading in the questions but the data types are different in each section, some numerical some text. It's a quandry.

 

[cogent1]

OK, that means you don't need the second table, because there is a one-to-one correspondence between questions and their sections.

Try not to get frustrated, because you are getting needlessly tied up in complexities that don't exist. Answers to the questions don't go in the questions table, otherwise of course you'd get nulls. The questions are like a look-up table that is largely static, except for occasional changes.

You also need a results table listing the quarterly responses and other occasion-specific data, yes. That won't contain nulls because it's based on responses to the section questions, which never contain a null response.

This is not a problem which calls for a high level of complexity to solve. Put your questions in one table,put your results in another. Then all the complexity disappears. Data goes into your tables ONCE - you'll never need to type labels manually. Queries will handle the co-ordination of your data.