Indexing Database of many subtables with One to One relationship

[vbastrangledpython]

Hello,

I have created a table base in Access 2010. This consists of a "master" table and ten other "sub" tables linked to this with one to one relationships.

The reason I have set it up like this is because:

• Each Entry contains roughly 200 columns (fields). These fields may change in the future (most likely new ones will need to be added), meaning that to make it easier to update I've separated these into multiple tables.
• IDO NOT have many entries in any of the sub-tables for a single entry in the master table. For every single table there is only one entry each time.

What I want in the end is

• A Navigational User Form that forces the user (the person adding an entry) to navigate through the forms one at a time.
• For the values input by the user to correspond only to a single entry. I.E. I want an interface whereby the user navigates through the forms, going back to make corrections if necessary, then has a button (or similar) that says "add to database" (or whatever), adding all the data in the forms to the tables as a single entry then clearing the forms.

Unfortunately what I currently have is not working like this. The entry numbers are not matched (i.e. I got through the forms and for one table the ID is 2 whereas for another its 3, and sometimes more than one entry has been added).

I'm guessing this has something to do with how I've set up the table relationships. However I don't see how else to do this as I don't want a one-to-many relationship, having only one entry in each sub-table for each entry in the master table.

I've tried to attach my file but it just won't upload. Happy to try and explain if people have questions though.

Does anyone know what I should be doing?

Apologies if the question is unclear, happy to edit it if so.

Many thanks in advance.

[June7]

I have a similar situation. It is a db for tracking laboratory samples. Each sample can have many different tests. Each test has its own data table.

I use form/subform arrangement. Subforms are on Tab control. VBA manages visibility of tab pages according to tests associated with the sample when it is logged in. Forms are bound so data entry/edit feeds directly to the tables. Records are committed when user moves between subforms and when form closes.

Form is not 'cleared', it is closed. You could instead move to new record.

However, structure of the tables rarely changes. If you are routinely adding fields to tables, then 1-to-1 might not be optimal structure for your requirements.

Did you run Compact & Repair and then zip the db for upload? Still over 2MB? Can upload to fileshare site such as Box.com and post link to the file.

[vbastrangledpython]

TPD_TEST_20140415.zip

File attached.

Hi June7,

"If you are routinely adding fields to tables, then 1-to-1 might not be optimal structure for your requirements."

I'm a bit confused. My understanding was that 1-to-many was for when you have multiple entries in one table corresponding to a single entry in another, for example the "one" table may be a list of customers, whereas the "many" table might be things customers have bought. So because one customer may have bought many thing, one-to-many is needed.

However I believe my needs are different. I am recording quotes received. For each quote there is upwards of 200 fields, but there is only one entry for each field for each quote. I may have to add more fields in the future, but there will still only be one entry per field per quote. So my understanding is that I either need everything in one table (which may eventually not be possible as there will be over 256 fields) or many tables with 1-to-1 relationships. Is this not correct?

Many thanks for your help.

[June7]

Depends why you have so many fields, 200 is a lot.

If the structure has multiple similar fields (like a field for each year or month) which REQUIRE periodically adding a new field, then that is not optimal. If there are multiple similar fields for other reasons, that can be dealt with in non-normalized structure.

I use the 1-to-1 relationship because the tests are so varied and each sample can be associated with any combination of tests. This means the sample will not have related record in every test table.

In your case, will each quote always have a record in each and every table?

I can take a look at your db later.

[vbastrangledpython]

Periodic updates are not required, but may well happen.

There is a large number of fields for a few reasons. There's about 10-20 "standard"-ish entries such as what the quotes for, how many of the item, the cost, the manufacturer, etc. The numbers start getting large after this. There's about fifteen "Options", i.e. things that may be included in the quote. Currently there's two fields for each of these, one binary "Y" or "N" to say whether the option is included, and a space for its cost to be written (e.g. an option may not be included but the manufacturer states how much this would be).

The numbers get seriously big when taking into account the maintenance and service packages. For each of these there is two costs per year up (one fixed price and one variable price). However the manufacturer may list these prices for a five year, ten year and fifteen year package. So that's 5*2 + 10*2 + 15*2 = 60.

A big limitation I'm facing is that because of the type of analysis I need to run long-term it must be possible to dump the entire dataset into Excel as one columnated dataset. My current plan is to create and populate the data base with multiple tables, then create a query that "flattens" these to create one data base that can be exported.

However, I am more than happy to approach this in a different way. My knowledge of Access is basic (to be generous) so I'm not sure that I'm approaching this in the best way.

"In your case, will each quote always have a record in each and every table?"

No, that's an issue I'm currently facing when inputting data via the navigational form. In particular for the maintenance and service packages, the user may only have quotes for five and ten years, meaning they have no need to enter values into the fifteen years form. However this then means that an entry isn't created in the corresponding table and thus the indexes don't match (having set up the 1-to-1 relationships hasn't prevented this).

Many thanks for your help.

[June7]

This troubles me "indexes don't match". Even if this is really intended to represent one record, one of the tables must take the lead and be 'parent'. This table will have unique ID field set as primary key - can be autonumber. The other tables must have a foreign key field that will save the primary key of the 'parent'. The foreign key field CANNOT be autonumber.

[vbastrangledpython]

Hello June7,

Apologies for the late reply. I can't find which one of my foreign key fields is set to autonumber, nor can I reproduce the error "indexes don't match". Can you let me know what you did to incur this error?

As a side-note, my current plan is to continue with the many tables of one to one relationships, using default values for whereever a particular quote does not have that information. I realise Access can handle some entries have information and other not, but I'm thinking this is the best way to proceed as I need to be able to export a "flattened" version of all the tables to excel once finished. Do you think this is the best way to proceed with that requirement in mind?

Many thanks for your help,

Nick

[June7]

It's not an error I encountered, it is a quote from the last paragraph of your post 5.

[vbastrangledpython]

Wow that's embarrasing. Apologies for my idiocy. I'd forgotten that was the problem that first caused me to post here.

[June7]

I finally took a look at your database (lost track of this thread). I think the relationships of PK/FK fields are correct and if by indexes you mean the autonumber ID fields, they won't and shouldn't match. In fact, the autonumber ID fields in the related tables aren't even required because these values are not used as FK in other tables. Unique identifiers can be useful in some advanced queries so it doesn't hurt to have them there, just ignore them until a need for them arises.

If by index you are referring to the PDID fields, then the issue is your form design. Your form is not set with main form bound to parent table and subforms bound to related tables. Without this association of forms, there is no way to automatically record PK as FK in the related records. This is probably the root of issue. Review http://office.microsoft.com/en-us/ac...010098674.aspx

I don't like the navigation form/control and never use it. I don't know how it manages record synchronization between main and sub forms.

Only one of the relationships shows as 1-to-1, all others are 1-to-many.