One to One Relationship - Automatic addition of records in the other t 3

[khwaja]

I have a simple problem but it has knocked me down. Trust someone can help.

I have a table which has quite a few fields. So I split this table into two as some fields were to be used less frequently. Main table contains store details and the other table contains areas. I created primary keys of same names in both these tables and also created a one to one relationship in the Relationship screen. Primiary key in Areas table is looking up Stores Table Primary key of the same name but that is in auto number format.

I am able to link two forms for pre existing records. I supplied user a command button on the main form to access Areas info, when required. But when it comes to adding a new record in the main table, it does not create a corresponding new record in the other table.

I am sure I am making a silly mistake some where and wonder if some one could help.

Regards

 

[Quintios]

I had a similar difficulty when I designed my database. In fact I have an ongoing conversation with another board member here about the proper way around this. I'm curious, did you read the book 'Database Design for Mere Mortals'?

Anyway, the way you get the same primary key into the second table is to use a form/subform combination when entering data. The subform, which represents the record in the subset table, relates back to the main table by way of the primary key. Access is smart enough to stuff the same number into the subset table when you start entering data in that subform.

I myself had a very very large table that split into nine different tables, so I have eight different subforms that will selectively become invisible when a given radio button is selected from an option group. That was a nifty trick in and of itself. Not hard, but I'm such a newbie.

Anyway, that's the only way I know how to do it. I hope someone else can post with a different method, as I'm sure I'll need to use it at some point in time.

Let me know if that was helpful.
Onwards,

Q-

 

[khwaja]

Much appreciate your response mate. Unfortunately it does not seem to be working in my case even though there are two forms I designed and linked with each other based on same primary keys. It might just work as sub form but when link child relationship are established, as you indicated but not when you are filtering records to link to the right form.

Hope someone can help us.

Cheers

 

[WildHare]

There is no good reason to split up a table just because it has 'a lot' of fields (assuming the design is correct, of course).

Record-source your forms with queries as opposed to base tables. Each query can pull out whatever fields you find necessary for the form. As long as the primary key(s) is/are also included on the query, you can do just fine. There's no reason to split things up, mess with duplicate primary keys or anything.



Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[randusoleis]

In access you can not create a Form/SubForm with a one-one relationship or a Intermediate Relationship.
In your stores table you will need a field that is numeric/long integer that will match the Primary field (AutoNumber) in your Areas table thus creating a many-2-many relationship. This will do what you require and will allow you to build Form/Subform Randusoleis.....

 

[Quintios]

randusolis,

Maybe I'm misunderstanding you, but I have a Form/Subform with a one to one relationship. The main table, 'Tags', has a one to one relationship with eight subset tables. I select which subset table appears in the subform by way of an option group. To ensure I don't have duplicate records, when the user changes the selected radio button in the option group, Access deletes the previous record. In this manner the main table will always only correspond to one subset table.

You could call it a one to many relationship as there are many subset tables, but because the main table only corresponds to one subset table at any time, it's a one to one relationship.

The tables were split out because this database is the result of converting a flat-file Excel spreadsheet with darn near 100 columns. Keeping it in that form would be incredibly inefficient.


Onwards,

Q-

 

[randusoleis]

I just did some reading up on 1-2-1 relationships as I don't use them.
As you said before you split the tables because they had too many columns....so your subform groups only show a piece of information that was originally grouped together.
Here is my question.....If you need to change information in any of those "sub-tables", how do you keep track of it. Literaly someone could change info in your tables and you would have no record. All you would have is the latest data.
Randusoleis.....

 

[JerryDennison]

In my 20 years or so of working with databases I've never seen an entity with enough attributes to warrant having as many fields as you seem to be needing. I suspect you really need to examine your database structure to insure it meets the Forms of Data Normalization. For example, do you have any fields that seem repetitious? i.e. share a common root name but are numbered in some fashion, name1, name2, ... nameN?

 

[randusoleis]

I would also agree with Jerry. You may want to re-examine your structure.
Stay away from 1-2-1's. 1-2-Many and Many-2-Many is the way for total relaxation. Randusoleis.....

 

[Quintios]

There are no repeated fields. The original flat file spreadsheet covered elements used in special programming for the computers we use at our company (they're not IBM or Mac, they were internally developed.)

These programming elements (we call them 'tags') have attributes that are specific to the type of element, if it's digital or analog, constant or variable, an input or an output, a calculation, etc. Thus there are many columns (in the spreadsheet) that don't apply to some of the tags. According to the database design book, the attributes that were specific only to one particular type of tag were split out. There is a main table with attributes that apply to ALL tags, and then the other types of tags are represented by extended information in the subset tables. For every tag, the main table is populated and one of the subset tables, and the other seven tables are blank, as they should be. Thus, there's no waste in memory or disk space (even though memory and disk space are darn cheap these days.)

I did have one area where I would have had a Many-To-Many relationship, but having read and understood that was split out and a linking table now exists between the two tables.

I'm not familiar with correlating the properties of a database with the description of the normal forms, but like I said, there's no repeated data, and everything is hooked up by keys. I think it's hard to explain online because of the unique nature of the data and how we use it.

I'd be willing to send someone a copy of it as I don't believe anything propritary would be revealed, but I hesitate. I have a spreadsheet showing the tables and the primary keys/foreign keys, which I wouldn't mind sending.

I'm not sure what you mean by 1-2-1, but if you mean One-To-One then yeah, that's what I've got.

If information needs to change in the subtables, then it changes. Keeping a record of what the information used to be is not important as this database has a set life. When all the information is collected and reviewed, we'll dump the database to a text file and bring it over to the VAX where it will be used to program the aformentioned computers, and then we'll zip up the database and archive it somewhere. Once the code goes live the database is pretty much null and void as the project is over and the code changes through routine maintenance.

If you're more curious about this I'd be happy to explain it better. Email me at quintios@yahoo.com

And by the way, in a previous post my email address was deleted. I guess they do that around here to prevent spam?

Just as a test I'll put my email address here again:

quintios@yahoo.com

Just to see. Quintios at yahoo.com

Just testing.







Onwards,

Q-

 

[JerryDennison]

If you have a field for each tag then your structure is definitely not normalized.

 

[Quintios]

Jerry,

Where, then, would I hold the data? Data goes in fields, fields go in records, one record represents one "tag", as we call it.

Let's drop the term tag and call it employee records. Some employees are hourly, and some are salaried. The information that only pertains to hourly employees is split off into a subset table, and the information that only pertains to salaried employees is split off into a second subset table.

It's the same thing with my database, only on a much broader scale.

You make the statement 'it's not normalized', but don't mention what form or normalization rule it violates. Please elaborate.

Onwards,

Q-

 

[JerryDennison]

I can't elaborate without seeing the actual fields, their names, and the type of data they hold. But I do know from experience that what you describe would not meet the Forms. If you'll send me a sample of your database (you can strip the records out) I can be more specific.

jerry@athree.com

 

[Quintios]

OK, I'll send you a copy. It's in Access 2000 format.


Onwards,

Q-

 

[khwaja]

Thank you all for contributing. I realise that there may be a need for review of the database design. I have a question though. Q might be able to answer.

Is you sub form accessed as embedded sub form or as a stand alone form. I have noticed that when you have a sub form embedded within the main form, there is no issue in updating the PK. However, when the sub form is to be accessed seperately using a command button, that is when problem arises. Are your forms embedded in the main form? If not then I would be keen to find out how you could update PK of the sub form when accessed by a command button.

If I don't much like on this, I might just drop an invisible sub form into the main form for the purpose of updating the PK and then providing a seperate form via command button. I have not tried this but I am sure it would work. If not then perhaps combining tables will be a way to go.

Cheers

 

[Craig0201]

Just a point.....

If there is a one to one then the two items are actually the same entity and the data should be in a single table....

Craig

 

[Quintios]

khwaja- Yep, they are embedded. I knew/know of no other way to get the PK into both tables.

Craig0201- You've got the right idea! However, because ALL of the subset tables COULD apply to the main table (but only one WILL apply, once the enduser has made his choice) I can't keep them all in the same table. The result would be very very unwieldly. I'd have a table with 72+ columns, most of which would be blank for a given record.

A quote from 'Database Design for Mere Mortals', written by Mike Hernandez:

"As you refine the structures of your tables, you may find that some of the fields in a particular table do not always contain values. Although no data or information retrieval problems will occur in a table where there are fields that contain no values, such a table might need some further refinement... In this scenario...the values of several fields in many of the records will be blank... To solve this problem, you'll create subsets of the main table."

This is why I have subset tables.


Onwards,

Q-

 

[JerryDennison]

A large number of empty fields usually indicates a poorly Normalized db structure. I'm not saying this is the case for you, but it could be. The only reason I'm belaboring this point is to save you the potential for grief later. If your structure is fully normalized (within reason) then I will give you some alternatives for synchronizing your tables.

 

[khwaja]

Thanks Q. Do you agree with the handling of issue using a hidden sub form?

I am a newbie and I don't fully understand the normalisation process. Could someone explain a bit?

Cheers

 

[JerryDennison]

The Forms of Data Normalization are a set of guidelines for defining the structure and relationships of relational databases. They are not hard and fast "rules" but they are very useful guidelines that help you insure a sound and efficient database. Basically, the primary guidelines are:

1) ...all attributes be atomic (that is, there should be no repeating groups. Either within a field or a table).

2) Every attribute should be fully functionally dependent on the entire primary key, no subset of the key should determine an attribute's value.

3) Every non-key attribute should be fully funcionally dependent on the entire primary key, and not on any other non-key attribute (no transitive dependencies must exist among attributes).

What this boils down to is that for every entity (key) the attributes must be a descriptor of the key, the whole key, and nothing but the key. A good example is parents and children. While a child cannot exist without a parent, the parent is not dependent on the child. Therefore a child is not an attribute of his/her parent. This means if you have a table for the parent entity you would not include fields for children. They should be in their own table (in this case you would put them in the same table with a recursive join back to itself) linked to the parent record via a foreign key to the parent's primary key.