Linking tables

[clobug1]

Having some real problems creating a new database to hold information on candidates studying at our 'college'.
So far I have created all the tables but am having real problems linking them and am not sure how viable it is to have several links flying around from each table.

Tables are:
Candidate Details - personal stuff, only once per candidate
Programme Details - the course they are on and who it's funded by, could be more than once per candidate
Registration Details - the actual qualification(s) they are registered for, more than once per candidate and additionally more than one per programme

Here comes the problem:
tbl:Units completed - the units they have completed towards a particular qualification.

Am currently thinking of linking:
CDetails to PDetails to RegDetails to UnitComp but this involves having a unique ID for each table, is it vital that these unique IDs mean something or can I just bung in an autonumber?

Hope this makes a vague amount of sense - bear in mind we have about 1000 candidates so I need to get this right before I start!

 

[neileg]

If you already have a meaningful unique ID for your table, you can use that, eg a unit number. Otherwise, just bung in those autonumbers! The worst that can happen is that you have used one redundant field, but your db will still work. It's much harder to backtrack when you find the field you used as a key ends up with duplicate values!

 

[clobug1]

That's great, however if I need to relate these units back to the candidate in some way can I also put a relationship in directly between the units and the candidate ie using the unique id from CDetails in Units so that I can later pull off a report of all the units signed off for that candidate, irrespective of what programme it was achieved on, or what award it was for?

And, bearing in mind that if there is no entry in the units table for a particular candidate, as soon as use this table in a query you lose the candidate from your results, is there any way you can automatically get Access to put a record into the Units table when you create a record in the other 3 tables?

 

[neileg]

That's great, however if I need to relate these units back to the candidate in some way can I also put a relationship in directly between the units and the candidate ie using the unique id from CDetails in Units so that I can later pull off a report of all the units signed off for that candidate, irrespective of what programme it was achieved on, or what award it was for?

If there is already a link from candidates to units via the other table, and the relationships are properly set up, you don't need, and shouldn't have, another direct link.

And, bearing in mind that if there is no entry in the units table for a particular candidate, as soon as use this table in a query you lose the candidate from your results

Why do you think this is so? It depends on the relationships.

is there any way you can automatically get Access to put a record into the Units table when you create a record in the other 3 tables?

Things like this need to be dealt with in forms, usually based on queries constructed from the underlying tables. Access handles the linking based on the relationships you have defined.

However, your info suggests that you don't just have a simple cascading structure. It's not clear how the Programme and the Registration relate to each other. Are the units determined by the qualification, or the programme, or both, or not strictly related to either?

If you can be more explicit with the way the structure of all this works, it would help.

I think you are worrying too much about the way Access works, and not enough about how your data hangs togeher!

 

[clobug1]

I've tried the bit about linking back from Units to candidate and excellent, it works without the direct link!

But I've tried to do a query to show all the candidates who are on a current programme and what they are registered for - as soon as I add the UnitComp table to the query I lose the person whom has just started and therefore hasn't completed any work yet. All the relationships are 1-many as this was what I was told should be used predominantly. Currently I've not enforced referential integrity etc. as I'm just testing - so what's wrong with the relationships, is this staring me in the face?

However, your info suggests that you don't just have a simple cascading structure. It's not clear how the Programme and the Registration relate to each other. Are the units determined by the qualification, or the programme, or both, or not strictly related to either?

With regards to this the registration related to the programme via the Programme Code, each candidate might be registered for 2 or more awards within a 1 yr course, for instance 1)maths and 2)IT, within a 'life skills' course. Next yr they might re-enrol on an 'advanced life skills course' with a different programme reference and register for 1)IT advanced and 2)communication skills.

The units will be related to each award eg IT might be split into 3 units, 1)MS Word, 2)MS Excel, 3)MS Powerpoint and these units will be linked to the award using the Registration Ref. The reason I asked about the link back to the candidate is so that at the end of 2 or 3 yrs the candidate could have a print out of all the individual units they have achieved, irrespective of the award so that they could perhaps put them on their CV.

With regards to your comment about worrying about how the info. hangs together before sorting out the Access problems I think I've got this sorted after long investigations into what people want from this database - it's all about the reports they can produce. The problem is trying to explain it without having to give you a guided tour of how NVQs work!

Thanks v.much
Catherine

 

[neileg]

OK

But I've tried to do a query to show all the candidates who are on a current programme and what they are registered for - as soon as I add the UnitComp table to the query I lose the person whom has just started and therefore hasn't completed any work yet. All the relationships are 1-many as this was what I was told should be used predominantly. Currently I've not enforced referential integrity etc. as I'm just testing - so what's wrong with the relationships, is this staring me in the face?

The problem with adding the units table to your query sounds like the join type is wrong. If you click on the lines in the relationship diagram, and select join type, you will see there are three. I suspect you will have type 1. You need to change this to one where you include all the records from Registration and those records from Units where the joined fields are equal. I think this is called a left join, but I'm not a programmer, just a dirty hacker.

With regards to this the registration related to the programme via the Programme Code, each candidate might be registered for 2 or more awards within a 1 yr course, for instance 1)maths and 2)IT, within a 'life skills' course. Next yr they might re-enrol on an 'advanced life skills course' with a different programme reference and register for 1)IT advanced and 2)communication skills.

OK I think I get it now. One problem seems to be that the Life Skills course does not uniquely identify the awards the candidate is taking and perhaps the award does not uniquely identify the units. Thus, you can't say with certainty that a person doing a Life Skills course will be doing an MS Excel unit.
This means that your data is going to be relatively unstructured.

The problem is trying to explain it without having to give you a guided tour of how NVQs work!

Quite. It's hard to advise on database design if you don't have all the facts at hand. Trying to edit down the information you provide might lead to poor advice!

Plus, I lalready know more about NVQ's work than I ever really wanted to!

 

[clobug1]

Is it worth me posting a screenshot of the relationships? and if so how do I do this?

Have tried the bit about changing the joins but I get an error pop up - "The SQL statement could not be executed because it contains ambiguous outer joins"

 

[neileg]

Probably better if you zip the database (taking out unneccesary or sensitive data) and post it here. There's an attach file box at the botton of the posting screen.

 

[clobug1]

Thanks v. much, What I've done is attached the dbase in which I've been practising the links we've been discussing. It's v. small and doesn't contain all the other data in the tables such as address, DOB etc. which at this stage aren't important - I just want to get the tables linking correctly. All the data is made up as luckily I'm working on it blank at the mo.

If you require it there is a 2nd database which is hopefully going to become 'the real thing' which holds a lot more data in each table. I did try to attach this as well but it all got too big. If you need it I can probably send this separately.

Hope this doesn't look too much like double dutch to you - it's starting to look like it to me!

Catherine

PS The programme code is just made up of information about the programme eg who it's funded by and when it started, the rest of the references are autonumbers I think.

 

[neileg]

You have some problems here, one of which is the fact that I have to leave work shortly!

The structure of your database is wrong. Part of this is down to the lack of coherence in the real situation you are modelling, and partly I think it is just wrong!

Firstly, when you add the Units Completed table to you query, you come up with too many relationships. If you delete the relationship from Units Completed to Candidate Details, it seem to pull back valid data.

But that doesn't fix everything.

Because you haven't explained how everything links, some of this is speculation. However, I think you need to consider the following:
I think that the Candidate should be your key table. Include in the Candidate the references to the programme and the courses they are doing. Don't try and hold candidate details in the other tables.
Create a table that has an entry for each unit the candidate is undertaking with a flag that indicates if it is complete or not. Hold the Candidate Number against each record in this table. From this you will be able to produce the query you want.
There is no firm relationship between the programme, course and units so you can't cascade these as you have tried to do.

Sorry, but that's all I've got time for today. Have a nice weekend!


General Housekeeping
Don't use spaces in object names
When you use a primary key, it helps if you name it consitently eg CandidateID, UnitID etc
Prefix your objects so you know what they are, eg: tblCandidate, frmInputDoc, rptCourseComplete