Indeterminate Relationship

[Ollie_3670]

Hello All!

Just started to make a database to register students and their applications to a university and already I've managed to hit a brick wall.

Apparently my ApplicationHeader and my ApplicationLine table cannot be joined because the relationship is indeterminate. One Application Header can hold several "Application Lines" (one for each course applied to) so the relationship should in fact be a 1:M.

I'm going to go ahead and state I believe it's a problem with the primary keys, but I don't know how to work around it as I believe the primary keys are correct.

(A student can only submit one applicationHeader per year)

The error message received is "No unique reference found for the referenced field in the primary table"

Thanks for your help in advance!

 

[vbaInet]

What field are you using to join both?

 

[Ollie_3670]

Application_ID was my guess?

 

[vbaInet]

That guess is correct. It worked for me. Are you linking via the Relationships or via Lookup Wizard?

 

[Ollie_3670]

I've clicked on:

Database Tools -> relationships

Then highlighted Application_ID in TblApplicationHeader then dragged over to Applicaton_ID in TblApplicationLine

The box popped up, I tick "enforce ref. integ." then try to click OK

---

Is there an alternative way?

 

[vbaInet]

Using the table on the right of your relationships diagram, go to design view of the form. In the ApplicationID field, under data type select Lookup Wizard, just follow the prompts. Try that and tell me the outcome

 

[vbaInet]

In TblApplicationHeader, go to design view, select Application_ID and look at the Indexed property. Set that to Yes(No Duplicates). That should solve your referential integrity problem.

 

[Ollie_3670]

Just tried it and it simply creates another indeterminate relationship!

Oh wait, didn't see your second post, I'll just try that now

 

[Ollie_3670]

YES! It worked, fantastic!

Any reason why it was set to "Duplicates OK"? Was that me clicking something by mistake or just something I have to be aware of whilst using compound PK's?

Thanks again!

 

[vbaInet]

Great job. One piece of advice though, I think Application_LineID should be autonumber. As for ApplicationID it should also be autonumber unless you have specific numbers which you wish to input into that field?

 

[Ollie_3670]

I'd agree entirely, just waiting for my client to make a decision on whether she wants information from her current system brought over, or left there!

Going off topic slightly, but it only needs a straightforward answer. Is there a simple way to be able to use Auto number generating a random number? Instead of a sequential number

Thanks for everything!

 

[vbaInet]

Not really. Autonumber only produces its numbers in sequence. It's not adviceable using a random number as your unique idea because there's a possibity of it generating that same number at some point causing a duplicate record or an error (if your table structure doesn't permit this). If you're still interested, here's a link with examples of creating a random number using functions:

http://www.martin2k.co.uk/vb6/tips/vb_27.php