I think I may have over-normalised..

[shepHeard]

I think I may have done somthing fundamentally wrong here, but I'm not sure, and I've spent a long time on trying to sort a work-around so I want to see if I can solve this...

Basically my table relationships are thus:

[table A]
|
one-to-many
|
[table B]--one-to-many--[table C]
|
many-to-one
|
[table D]

Basically, table D is a list of journal references ralating to items in Table B. A single journal artical may cover several items in table B.

This all works fine, except when a user needs to edit (via a form) the reference details for an item in table B. For example, if the reference is unknown or unpublished at the point of data input, there will be an "Unpublished" or "Unkown" entry in Table D linking to all such items in table B. If this information is later found, we will need to be able to update this reference for this item only. If you update the reference field from "Unknown" to xyz this will change all the "Unknown" references to xyz.

I've tried to write a work-a-round, whereby after updating the reference, there is a 'save event' which checks if the new reference already exists, and if not it adds it to Table D via SQL, and gives it an ID. The problem is, that when I try and point the item in table B to this ref via this ID I get an error basically saying the ID doesn't exist. If I use me.refresh or doCmd.RunCommand acCmdRefresh, it realises the new ID does exist, but has already commited the changes to the "Unkown" record.

I really feel I'm going way off course with this - it seems to involve a disproportionate amount of coding to try and solve it. Should I just cut my loses and merge Table B and Table D? Or is there a more appropriate way to handle this?

As allways - thanks in advance.

 

[wazz]

assuming a reference can only come from one place, it looks to me like you just need: tblJournal and tblReference (1:M). tblReference will be your main table and tblJournal a lookup table.

one entry in tblJournal can be 'Unknown'.

if a reference or quote can come from many places then that's different.

 

[shepHeard]

Thanks for the reply.

Please excuse my naïvety (I'm trying to wrap a very tired brain around VB and other logical things!), are you suggesting that table B should link in a one-to-one relationship with tblJournal (via maybe the Table B unique ID) and tblJournal then links to tblRef in a one-to-many relationship?

tblJournal = Record_ID | Journal | Ref_ID
tblRef = Ref_ID | Name | Year


Would I not still get the same problem where a user edits the reference values. e.g. user creates entries for item "Unknown" in the user form:

user changes the journal name from "Unknown" to "xyz";

as "xyz" doesn't exist already;

create an entry in tblRef for "xzy";

we're then beck to where we were before - Access won't let me change the Ref_ID to the details for "xyz" as it thinks it doesn't exist yet, so I get the "the current field must match the join key "?" in the table that serves as the "one" side of the one-to-many realtionship"-type error.

The only way to make access realise the new record does exist in the table is to refresh it, but this also commits changes to the record it currently links to, in this case "Unknown"..

I hope this makes sense. I'm feeling a bit like the Gumby man.

Please find attached screen-shots of my relationships and my user form...

 

[Pat Hartman]

Sounds like you are changing the wrong field. You need to change the value of the field in the many-side table. I think you are changing the value of the field from the one-side table. Check your query to see which side of the relation the field you are updating comes from.

 

[shepHeard]

Ahh. Good point. I'll check, but the code I'm using is

Form_sfmLocus.[Locus.Ref_ID] = n

Could it be to do with the fact that both fields have the same name in each table? I though I was getting around that by specifying [Locus.Ref_ID] rather than Form_sfmLocus.Ref_ID which would be ambiguous..

subform sfmLocus is based on an SQL that returns Locus (table B above) and Reference details.

Thanks for your reply,

Chris