Strange relationship changes (1 Viewer)

[wiklendt]

Hey everyone,

we have this strange behaviour of our relationships in relationship view.

the scenario is that we are adding a second instance of some tables and trying to add relationships between these and another table.

what access is doing is changing the relationships we have made back to their 'origins', so to speak. we don't know if we're setting this up incorrectly, so advice on that front is also appreciated...

please see the two attached images. image 1 is how we had set it up. image 2 is what access does to it. this behaviour occurs automatically and even after only closing and reopening the relationship view.

we are using second instances because we have a separate collection that needs to be stored in the same boxes as another collection, so thought it best to not 'circular reference' the relationships between the two...

if you could have a look at the images and let us know if we can just use the one instance or if there is a better way to do this we'd be much obliged

IMAGE ONE (how we want the relationships)

IMAGE TWO (how access changes them)

 

[stopher]

The aliases ("second instances") are entirely unnecessary here. Creating a relationship to an alias will have the same effect as creating a relationship to the original table. Access is recognising that and simplifying the relationship view.

You don't need to worry about "circular references" here.

So remove all the aliases and then just move the tables around in the relationship view to look a bit tidier.

As an aside, tblIsolateStorage looks extremely similar to tblSampleStorage. I would have both of these in a single table.

By the same reasoning I would also consider tblIsolates and tblSamples as a single table. To be fair I have no idea about your data but is the data you are storing is the same kind of thing then it's a good candidate for storing in a single table e.g. tblDoctors and tblNurses could both be stored in a single table called tblStaff.

hth
Chris

 

[wiklendt]

thanks Chris,

as for normalisation, tell me about it! i'm still working heavily on my supervisor (who is the main guy putting this together) to combine these. he wants to keep them separate for political purposes, so it's hard to make him 'see the light'...

i'll keep trying though.

gotta think of a way to have the species etc from isolates not redundant for samples, though...

 

[wiklendt]

i think we have a victory (though i won't count my chickens until they hatch) - my supervisor just said "don't kill me, but - can't we just have them in one table?" (gaaaa! that's what i said to him 2 days ago and he ARGUED about keeping the data separate blah blah blah... sheesh).

at least we'll have a well designed db ;P

 

[wiklendt]

false alarm. we have spent all this time arguing, though. hopefully keeping it all separate won't cause issues down the track... *sigh*. if it's any consolation, isolates and samples are two very different things. samples are components from people, whilst isolates are bacteria grown from infections (yes, of the same people, but it's a different kettle of fish - no so much apples vs oranges, more like apples vs ipods)...

 

[wiklendt]

ok, so. if we did want to put the two storage tables together as one, how would we deal with the repeated IDs for the two different source tables? that is, there will be an isolate with ID of, say, "2", but there will also be a sample with ID of "2"... so if the storage table simply has a foreign key "2", how do we tell which primary table it belongs to?

is this a situation where you'd use a composite key? if we DID apply a composite key, would be be forever stuck with using a composite key (with all its complexities) whenever we wanted to use any of the records from the isolates or the samples table (or is the composite key generated in the storage table, in this case?)

sorry, i've just never delved into composite keys - only gleaned from my time in the forum that they are problematic and should be avoided if possible...