PK made up of two fields

[Cory_nz]

Hi people

Not sure if this is in the right forum topic, but not sure where it should be - but thought this was a better place then no where.

I'm in the middle of developing a student assessment marking database. I have a "UNIT" table, with a PK name '_pk_unit_id'. Within this table is the name of the 'unit', and also the 'attempt'. There is also a mark_time (mins) included which is calculated on the unit and the attempt. Example:

_pk_unit_id: UNT00001

unit: 2780

attempt: 1

mark_time: 20

_pk_unit_id: UNT00002

unit: 2780

attempt: 2

mark_time: 15

etc...

At the moment I have this information being displayed in a portal, where the teacher clicks the _fk_unit_id (linked to an ASSESSMENT table) and in the drop down box I have a value list combination of "unit + attempt" displaying as "2780 - Attempt 1". The issue is I have atleast 50 different units, and a maximum of 6 attempts per unit, so the drop down list is HUGE and messy. Mark_time is not displayed in the portal, but is used for other reports.

I've tried thinking this one through and I think my mind is a bit fried. How can I get it to be easy to select the unit and the attempt, and not get it to screw up the mark_time functionality I've set up in the other reports?

What I would like to be able to do, is have the teacher have a drop down box just for 'unit', and then another box for 'attempt' - which then links itself back to the '_pk_unit_id' and thus allowing me to run reports on mark_time. Can't for the life of me figure out how though.

Not sure if I've explained this very well...so please let me if you need any more information.

But ANY ideas will be greatly appreciated.

thanks

Cory

Edited August 12, 2009 by Guest

[Cory_nz]

OK - I've thought about this some more, and have hopefully come up with something that you might actually be able to help with - instead of the confused ramblings I've given you above.

How can I (within a portal) enter in a 'unit' - drop down list with selection possibilities populated from 'unit_id' within the UNIT table - and also 'attempt' - again from UNIT table, 'attempt'. And then have the _fk_unit_id (within the ASSESSMENT table) recognise that those two combinations make up a '_pk_unit_id' record, and inserts that unique code in?

As it's a portal this will need to be able to be done as many times as necessary.

Is this even possible?

/Cory

[bruceR]

Try explaining again. So far it doesn't make much sense. What is it that you are actually trying to accomplish? Forget field names; what are you trying to accomplish?

[Cory_nz]

Yeah - didn't think made much sense...

ok - what I want...

What I would like to be able to do is have a drop down box with all the different unit names in there. Another drop down box where I can select the attempt number. From there have the system recognize the combination of the two, and enter in the correct _pk_id from UNIT, into the _fk_id field within ASSESSMENT.

Like a find - but I've tried doing a find combination and it didn't seem to work.

Does that make any more sense?

[comment]

What I would like to be able to do is have a drop down box with all the different unit names in there. Another drop down box where I can select the attempt number.

That's exactly what you should do - then define the relationship to match BOTH fields (you don't need the third field that concatenates the two values).

[Cory_nz]

There is no concatenated field though. There's the _pk_unit_id which is a unique code, which I guess you could say owns the combination of 'unit' and 'attempt':

_pk_unit_id: UNT0001

unit: 2780

attempt: 1

so if I put in UNT0001 anywhere, it's really talking about 2780 - 1.

What I need is to be able to have the portal recognise that the combination of '2780' and '1' relates to 'UNT0001', and then enter THAT into the field '_fk_unit_id', which is the link from the ASSESSMENT table to the UNIT table.

EDIT: I think one of the issues here is I don't quite know how to explain myself. I know what I want in my head, but can't put it into effective words. Would it be easier if I sent the file to you to see exactly what I'm on about?

Edited August 12, 2009 by Guest

[comment]

I am not sure where the difficulty is. You said you want to enter the unit name and the attempt, instead of the ID. I said go ahead and make your relationship match on those two fields, if that's what you prefer.

[Cory_nz]

Sorry - what I meant is I want the USER to be able to enter in the 'unit' and the 'attempt' but have the DB enter the 'ID' into the backend.

[comment]

You can lookup the ID, if you need it for something else (I am not sure where you are in the bigger picture).

You don't need it for the relationship we have discussing until now - since the other two fields already take care of that.

[Cory_nz]

So you're saying that if I replace the old relationship to something new:

old

UNIT - ASSESSMENT

_pk_unit_id = _fk_unit_id

to new:

UNIT - ASSESSMENT

unit_id = _fk_unit_id

AND attempt = _fk_unit_id

then that should work?

But what information will now be stored in the _fk_unit_id? For all my other linked reports to work, I need the _pk_unit_id in there, and from the sounds of it, with the new relationship, I'm not going to get that.

[comment]

No, I am saying that the relationship should be:

ASSESSMENT::unit = UNIT::unit

AND

ASSESSMENT::attempt = UNIT::attempt

This should work the same as:

ASSESSMENT::_fk_unit_id = UNIT::pk_unit_id

provided that (a) the combination of unit and attempt is unique in the UNIT table, and ( that the values of unit and attempt in the UNIT table will not be changed at some later time.

You can define the _fk_unit_id field in ASSESSMENT to lookup the value from the field pk_unit_id in UNIT.

[Cory_nz]

MAGIC!! Does exactly what I need it to do!

Thank you SO much for your patience!