Table Relationships - Baffled

[xyba]

Hi

I'm trying to create a new database to record documentation that is received and processed for students but I'm confused as to the relationships of my tables.

I've already created many separate tables, including one for each type of document (DocumentT) and each student (LearnerT).

We could receive many of the different documents for each student, as well as more than one of the same document, and I think this is where I'm stumbling. How should these two tables be related?

I think my main concern is if there are going to be multiple documents for each student do I create separate fields on LearnerT related to each document on DocumentT?

I previously had a non-normalised database for these records but I'm now starting a new normalised db to store the info and I'm confused so any help would be appreciated. (Hope all the above makes sense).

 

[CJ_London]

I think you still need to get to grips with normalisation

I've already created many separate tables, including one for each type of document (DocumentT) and each student (LearnerT).

this should be two tables only one for documents and one for students - perhaps something like

tblDocs
DocPK autonumber
DocType text
DocDesc text

tblStudents
StudentPK autonumber
StudentName text
...
...

then you need a third table to join the two together

tblProcessed
ProcessPK autonumber
StudentFK long (link to tblStudents)
DocFK long (link to tblDocs)
DateProcessed Date
...
...

you might need a 4th table for DocTypes

tblDocTyoes
DocTypePK autonumber
DocTypeDesc text

in which case in tblDocs you would replace

DocType text

with

DocTypeFK long (link to tblDocTypes)

 

[Minty]

You need a third table DocumentRecs. This is is a junction table.

This would store your document types and your student id's along with its own unique DocRecID

edit - CJ beat me with a much more detailed answer

 

[xyba]

I think you still need to get to grips with normalisation

this should be two tables only one for documents and one for students - perhaps something like

I didn't explain that one very well. I've created multiple tables one of which is for documents and another for students (not a separate one for each document and each student).

Thanks for your suggestion of the extra table(s). I'll give that a go and may come back if I get stuck.

 

[CJ_London]

good luck!

 

[xyba]

Okay, a little bit stuck already.

Each document could, potentially, be referred to different departments up to 6 times before it's processed. Should I have a separate table for Referral1, Referral2...or add the fields to tblProcessed?

I need to record the referral department, date and administrator.

 

[Minty]

Again you need a Referral Table that has the following fields
ReferralID - PK AutoNumber
Document ID - FK
Dept - FK
ReferralDate
AdministratorID - FK
ReferralText - etc

This gives you as many referrals per document as required 1 or 50 or 500.

 

[xyba]

Again you need a Referral Table that has the following fields
ReferralID - PK AutoNumber
Document ID - FK
Dept - FK
ReferralDate
AdministratorID - FK
ReferralText - etc

This gives you as many referrals per document as required 1 or 50 or 500.

So how would this be handled in a form?

In my previous non-normalised db I had a form that had 6 separate fields for each referral. I need to have a historical record of each referral for traceability and reporting.


Sent from my iPhone using Tapatalk

 

[Minty]

I would simply add a sub form to the main form with the new referral table linked by Document ID. You can s see and add new records as required.
The same technique would go for reports, use a sub report to list the referrals.

 

[xyba]

I would simply add a sub form to the main form with the new referral table linked by Document ID. You can s see and add new records as required.
The same technique would go for reports, use a sub report to list the referrals.

I keep getting errors stating "You cannot add or change a record because a related record is required in table ..." when using the Referral subform in Apprentices main form.

In summary, the purpose of this db is to record receipt of documents relating to students, and record their "journey" through different departments to final disposal/processing. What I want is to be able to bring up a student record on Apprentice form and view a list of documents received for that student along with their status/location.

I've attached my db if anyone is able to advise on how to fix the errors and how to achieve what I'm after, I would be grateful.

Thanks.

 

[Minty]

I've not got time right now to look at your db , but that sounds like you are trying to add records to the referrals before there is relevant parent record. I'll try and have a look a bit later for you.

 

[Minty]

Okay - quick glance. You need to include the learner ID in the sub form and set up the Master / Child link for the sub form(the build button will assist you) . When you have a record in the apprentice (Learner) table you will then be able to add an automatically linked referral.

 

[CJ_London]

I'm not sure how your form is intended to work but you have not specified in the linkchild and linkmaster fields in the subform control properties.

You also need to include LearnerID in the recordsource for the subform. It does not need to have a control on the subform unless you want to include it whilst testing. It will populate automatically once you have completed the linkchild (learnerID) and linkmaster (AppID) properties.

Ahh - beaten to it!

 

[xyba]

Okay - quick glance. You need to include the learner ID in the sub form and set up the Master / Child link for the sub form(the build button will assist you) . When you have a record in the apprentice (Learner) table you will then be able to add an automatically linked referral.

I've added LearnerID and set up the links but I'm still experiencing the same error messages. I've attached the updated version of the db.

CJ_London - The purpose of the form is to search for the student/learner, show the basic learner details at the top section and in the subform I need to show documents that have been received for that learner and update the department the document has been referred to. However, in the current format I can see an issue. When a document moves from one department to another a new document record would need to be created in the subform whereas I'd prefer to have a single record for each document showing each department it has been referred to.

I'm open to ideas/suggestions.

 

[CJ_London]

I'd prefer to have a single record for each document showing each department it has been referred to.

that does not sound like normalised data. But providing you don't want to input, you can use a crosstab query - documents for row, departments for column and (first) date referred as value

 

[xyba]

that does not sound like normalised data. But providing you don't want to input, you can use a crosstab query - documents for row, departments for column and (first) date referred as value

OK, I think I'm a bit confused so may not have explained correctly.

I have a table of unique students and a table of unique documents. The purpose of the database is to track and record documents received for each student.

For a single student we could receive Document A. Document A could be referred to more than one department. If I'm right in my thinking, the way the sub form is currently set up means if Document A is referred to a second department the user would have to input a separate record in the sub form to show it has been sent to another department, or am I wrong? (The current form and subform is what has previously been suggested to me but is there a better way of achieving what I want?

A point of note, we can receive more than one of any particular document for a student so maybe this causes more issues?

 

[jdraw]

I think you are correct

OK, I think I'm a bit confused so may not have explained correctly.

Before getting into forms, and subforms --get a clear description of the "business" you are trying to support with this database.

Just reading your post #16, I see

Student
Department
Document

Get the Business described in simple plain English
Get the Business rules/facts identified
Get your tables and attributes defined
Get some sample values(test data) for the things you have defined
Build a starting data model
Create some test scenarios based on your test data and model.
?? Does the model work -no issues- Fantastic!
If not, identify what's wrong --bad data, missing table, bad relationship,......adjust the model or data as necessary and repeat until it works. You now have a model that seems to support your basic design. Now start building your database.
Now is the time to work on user interface --forms/subforms

Good luck.

 

[xyba]

I think you are correct


Before getting into forms, and subforms --get a clear description of the "business" you are trying to support with this database.

Just reading your post #16, I see

Student
Department
Document

Get the Business described in simple plain English
Get the Business rules/facts identified
Get your tables and attributes defined
Get some sample values(test data) for the things you have defined
Build a starting data model
Create some test scenarios based on your test data and model.
?? Does the model work -no issues- Fantastic!
If not, identify what's wrong --bad data, missing table, bad relationship,......adjust the model or data as necessary and repeat until it works. You now have a model that seems to support your basic design. Now start building your database.
Now is the time to work on user interface --forms/subforms

Good luck.

I pretty much have done what you've stated above but got stuck and asked here for advice on specific issues. I've been running an unnormalised db for 6 months and realised this wasn't effective.

I planned a normalised db but got stuck and asked here for advice. I was advised to use a sub-form earlier in this thread which I did so but encountered more issues and error messages so asked for more help.

The purpose of the db is as I described in my last post but I'm stuck as how to progress so was hoping for specific advise and help as I don't know how to progress.

Watched videos on YouTube and read advice elsewhere but always find the help given here invaluable and forum members knowledgeable and helpful.



Sent from my iPhone using Tapatalk

 

[Minty]

Hi - I had a quick look at your later database. It would really help if you populated the basic tables with some demo data. I know it takes time but it's time we don't generally have especially as if you do it once - 10 people that look here won't be doing it 10 times.

I think I know where you are trying to head with it, perhaps an easier route for you would be to mock up your desired final data and maybe a report or two in a couple of spreadsheets. Then we can show you how to split that back into normalised tables.

 

[xyba]

Hi - I had a quick look at your later database. It would really help if you populated the basic tables with some demo data. I know it takes time but it's time we don't generally have especially as if you do it once - 10 people that look here won't be doing it 10 times.

I think I know where you are trying to head with it, perhaps an easier route for you would be to mock up your desired final data and maybe a report or two in a couple of spreadsheets. Then we can show you how to split that back into normalised tables.

Thanks Minty. I've populated some test data on the relevant tables (attached).