Linked fields updating automatically to reflect data entered elsewhere?

[starbuckadale]

Hi, I'm a newly qualified teacher who's school laptop has a copy of Access 2007-10 on it. The last time I used Access, it was 15 years ago at school and I remember nothing. When I was training, my mentor recommended Access as a great way of tracking student progress and I've attempted to build a database for that purpose. I'm working as a total n00b though, so I need a little help.

The document I use to assess is called Development Matters. I teach age 4-5, so I'm mostly working with the age bands 30-50 and 40-60 months. Education in the UK (that's where I am) splits early years ed into 7 learning areas, each one subdivided into 2 or 3. For example, one of the 7 areas is PSED, split into MR, MFB and SCA (Don't mind the acronyms, you really don't need to know what they stand for). So my tables are set up like this:
PSED: MR 30-50 learning objectives
PSED: MR 40-60 learning objectives
PSED: MFB 30-50 learning objectives
PSED: MFB 40-60 learning objectives
PSED: SCA 30-50 learning objectives
PSED: SCA 40-60 learning objectives

where the columns are the learning objectives and each child has a row. And there are similar tables set up for the other 6 areas. There's a table with all the children's data on too. I used a lookup relationship to set that up - I think that's the correct thing to do, but confirmation would be good!

So when a child achieves a learning objective, I go to the relevant table, based on learning area, subsection and age band, find the corresponding field and enter that date. I have data validation set up so that only dates may be entered. Simple, right?

My problem is that some learning objectives appear twice. For example, 'Adding a storyline or narrative to play' appears in Communication and Language: Speaking 40-60 and also Expressive Arts and Design: Being Imaginative 40-60. Ideally, I would like it set up so that if I edited a child's data in the CL table, the EAD table would update with the same data, and vice versa, so that it didn't matter which table the data was entered, both would always be up to date. I am aware that this is the exact reason you're not supposed to duplicate fields in databases usually, so what's the best way around this? Is there code I can use, or do I need to adapt my database to better fit the data that I'm entering? And if that's the case, what do you suggest?

Thanks for reading, I know this is TL;DR and the computer ate my post already once so hopefully I've included all the useful info without waffling too much...I've read around the forums and I know you guys like specifics!

 

[spikepl]

Google many-to-many relationship OR junction table

 

[starbuckadale]

I've read about many to many relationships and junction tables. The primary key on each table is the children's numbers at the moment, because I want each child to have the same ID number on each table. (Does that make logical sense? I'm under the impression that that's necessary, but I could be wrong.) This being the case, would I have all the doubled learning objectives on the junction table and have them output to each age band table? And does it matter that I'm not linking the primary keys? And am I way off base with making the children's ref numbers the primary key?

Thanks.

 

[MarkK]

You should google "database normalization."

This . . .

where the columns are the learning objectives and each child has a row

. . . is probably an error. One row in a database table should describe a single instance of an object. If you have many columns where each is a different learning objective, then each of those should, most likely, be in a row.

 

[starbuckadale]

Hi Mark, thanks for your help.

I know what database normalisation is, in fact my original database attempted linked fields to avoid doubled info. I'm just ...doing it wrong. I'm really struggling to find instructions/explanations that I know are right for me, because I'm such a n00b I'm not even sure how to find what I'm looking for or if it's the best setup for my needs.

What makes you say the LOs should be given rows rather than columns? I need a date for each child for each LO, so as far as I am aware that means each child needs a row or column and each LO needs a row/column? Even if you think the explanation will go right over my head, it's probably worth posting; my goal here is to actually understand why my database does and does not work.

 

[MarkK]

A row represents one thing. There should be a child table, where each row represents a child. There should be an objectives table, where each objective occupies a row. Then, if a child achieves an objective on a date there is a third table, say tblAchievement, which links the child to the objective, and contains the date . . .

tblChild
ChildID (PK)
Firstname
Lastname

tblObjective
ObjectiveID (PK)
Name
Description

tblAchivement
AchivementID (PK)
ChildID (FK)
ObjectiveID (FK)
Date

. . . so that if you add children, or achievements, or objectives, or anything you add in a database, your design should allow you to add that data by adding rows, never by changing the design of your tables by adding columns.

One row represents one thing, so you should never have two columns in one table that describe the same type of data. Maybe you have Firstname and Lastname in one row of a person table. Maybe you have height, width, and depth in a single row, but you never have botany, geology, and astronomy in one row. You never have teacher, student, and school in one row.

Hope this helps,

 

[starbuckadale]

Wow, that really does. Thanks, Mark! I read about a million tutorials and although I'm not exactly a stranger to intractable texts, the info just wasn't translating into real life sense. I'll probably be back with more questions later but you've given me something to think about. So I will want one to many relationships, right? (I hope I at least understand that, let's find out.)

 

[MarkK]

Yeah, exactly, the power of a database is in how it models the relationships between the objects you are concerned about, and the fundamental relationship is one-to-many.