Possible problem with relationship integrity

[Futures_Bright]

First of all, I am not 100% sure this is a problem as I've not seen anything like this before. I'd also like to try and get a 'solution' (or answer) without uploading my database since it is quite large and the 'problem' is in a small section of it; so here goes:

The database is centred around clauses (paragraphs of text from documents) which are directly linked to Sub-Categories - which themselves are linked to Categories (1 category can have several sub-categories, 1 sub category can link to multiple clauses. All linked by an Autonumber ID field).

To make user entry easier, it helps to pick the category first to filter the sub-category combobox. I've done this with control sources being two fields within the clause table (Category and Sub-Category).

This all works fine and the form remembers precisely what was entered and updates whenever the user updates this. The interesting bit however came when I viewed the table. All records in my Clause table had equal values for Category and Sub-Category (values 1-9, there are almost 100 Sub-Categories); while when I look in the Sub-Category table, it shows all clauses linked to these sub-clauses correctly.


So in summary, it is all working fine now - however I am still developing the database, and am wary that when I split the database into a Front-End and Back-End that the data stored in the Clause table may take priority and all the categorisation be subsequently wrong. I tried deleting the Category field from my Clause table (as it shouldn't be necessary) and this led to all values being lost in the forms. Has anyone seen anything where a field appears to be different on each side of a relationship? Is this a problem? I have a solution in mind but would rather not do this if it won't be a problem as I already have 2000+ records that would all need re-categorising!


Thanks in advance for any help.

Kind regards,

Michael

 

[jdraw]

Show us a jpg of your tables and relationships. (I think you can post if you use a zip file)

Sounds like

Category--->SubCategory--->Clause

Please explain your concern with splitting the database - I'm not following what the issue is/might be.

 

[Futures_Bright]

JDraw, you are correct with the relationship, I've taken an extract screenshot if you still need confirmation?

My concern with splitting the database is because I've not split a database before and am not sure how exactly it is done other than basically Tables will be in the Back-end and all the Forms and reports etc. in the Front-end.

Currently the relationship appears correct on the Clause form and the sub-category table, however the data in the Clause table is inaccurate and I'm not sure whether or not this will take priority over the others after the split.

 

[jdraw]

however the data in the Clause table is inaccurate

Well, splitting or not splitting won't correct bad data.
Why is the data inaccurate? Better to solve that problem before you go forward.

With most Access issues -- bad table design and bad data - are major contributors.

 

[bob fitz]

Futures_Bright

Are you using any lookup fields in your tables?

 

[Futures_Bright]

@jdraw: I know and this is the basis of my fear, however I don't understand why the data appears inaccurate on the Clause table when it is correct on the form and on the other side of the relationship. To try and explain what appears in the Clause table a little more - it looks as though it also puts the [Category].[Category ID] in the [Clause].[Sub-Category] field as well as [Clause].[Category] (i.e. all records are 1, 1; 2, 2; etc.). Whereas in the Sub-Category table, when expanding each record (Sub-Category) to see all related clauses, they refer to the correct item.

@bob fitz: Yes, in the Clause form, there are comboboxes for entry of both Category and Sub-Category (control sources for fields in the Clause table) - these lookup the records from both tables. The user selection for the Category filters the visible options for Sub-Category. This was a little fiddly to work through however (and seems the most likely source of this issue), since the Clause links to the Sub-Category, but the User will know the Category before the Sub-Category meaning running through the relationship the wrong way around (and hence why I earlier tried removing the Category field from the Clause Table - since via relationship you would find the associated category, but this also made problems for data entry since it was now a reference field rather than a filtering field).


I have a solution in mind which should (in theory at least) work, however knowing the amount of time it will take to re-categorise all the current entries I'm reluctant to put this in place unless it will definitely cause problems and it is not a case of simply changing a property.