More unique valuecombinations

[superfury]

I have a table that is linked to other tables:

tbl1:
ResultID (more records, linked to a table that stores unique results (can be used for seperate users, different dates at the time of input))
DVDID (Name of the dvd) -> linked to DVDs table, field ID
Score (Score given to the dvd) -> linked to Scores table, field ID

For each ResultID the user only has to be able to select one of each DVDID and one score. Like:

ResultID DVDID Score
1 1 1


2 2 2
3 3 3

Next:
1 1 4 isn't allowed: repeated DVDID
1 1 2 isn't allowed: repeated DVDID and Score
1 1 1 isn't allowed: repeated record.
1 4 1 isn't allowed: repeated Score.

Or short:
For each resultID, only one DVDID is allowed for that resultID.
For each resultID, only one Score is allowed for that resultID.






Possible solution i want to have:
resultID not unique
DVDID unique for each resultID
Score unique for each resultID

How do i get access to do this (table creation)?

[rpeare]

I wouldn't attempt to do this in your table design (validation rules) I would do this on the data entry side (forms). You can create rules that happen before a record is added or updated in your database to look up combination so of dvdid's and scores to either allow them or disallow them from being entered or updated.

I'm confused why you wouldn't allow the same score for a different ID though. Are you doing a survey of favorite movies where someone has to rank a dvd from 1 to 10 (10 dvds) so there can only be one occurance of each score?

[superfury]

I wouldn't attempt to do this in your table design (validation rules) I would do this on the data entry side (forms). You can create rules that happen before a record is added or updated in your database to look up combination so of dvdid's and scores to either allow them or disallow them from being entered or updated.

I'm confused why you wouldn't allow the same score for a different ID though. Are you doing a survey of favorite movies where someone has to rank a dvd from 1 to 10 (10 dvds) so there can only be one occurance of each score?

Correct. Each user is given an unique id (stored in a different table; unique value=field UniqueValue; field ID is linked to this table's resultID). Each resultID:
-> Maximum of 1 times each DVDID (unique in resultID)
-> Maximum of 1 times each Score (unique in resultID)

So you'll only can do this, by example (red=not executable when entering them all after the other):
1 1 1
1 2 2
1 3 5
1 4 3
1 5 4
1 5 6 Not allowed: DVDID 5 already entered.
1 6 1 Not allowed: Score 1 already entered.

(DVDID values query: all DVDID-source values not used in this resultID plus own DVDID)
(Score values query: all Scores-source values not used in this resultID plus own Score)

[rpeare]

Because you have so many checks you want to perform I would be tempted to say use an unbound form, this will absolutely prevent any information from being added to your database unless all the checks are passed correctly. Alternately, on a bound form you could have a series of 'warnings' pop up in unbound text boxes if someone enters a combination of data that is not allowed. As I understand it these are you rules:

1. No single person can score the same DVD more than one time
2. No single person can assign the same score to more than one dvd

Do you also need to set an upper bound to the number of dvd's they can rank?

If these are the only two rules you can probably do something with the DCOUNT function on a bound form to count records that and pop up with a warning before the record is added.