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]

Well, I guess I have a parent with eight children...

Jerry, do you disagree with the quote I posted? I haven't gotten around to getting the db to you, but it's on my to-do list. I appreciate the offer, by the way.

khwaja, I'm probably not the one to talk to with regards to the user interface. After having read Mr. Hernandez's book I feel quite competent in designing tables and relationships between those tables, but with regards to the interface or platform, I'm as much a newbie as the next guy, although I'm learning a lot.

Onwards,

Q-

 

[JerryDennison]

I cannot disagree with the quote. But, I cannot agree that the quote is pertinent to your situation either. All I can do is give you my experience. With few exceptions, I've yet to find a case where I would need to use a one-to-one relationship simply to split up an unwieldy (i.e. very large) table. In fact, I've yet to find any entity that would require 70 attributes that describe it and only it.

 

[Quintios]

See, and that's the thing. It doesn't take 70 attributes to describe one entity. It takes about 15 attributes. About 8 of them are common between all entities, and the rest are specific to only one of the entities. But if you put them all together in one row in a spreadsheet, you'd get about, oh, it goes from 'A' to about 'BV' (column names).

I'll get the spreadsheet to you one of these days. I appreciate the offer for help!!


Onwards,

Q-

 

[petrosky]

Just popping my head in to say that this is a good learning point for me as well.

Please keep us up to date with the outcome/s such as they may be.


Peter Remember- It's nice to be important,
but it's important to be nice

 

[pdldavis]

Quintios, I believe I know exactly what you're talking about- probably the raw data dumped from an engineering design firms instrument or I/O database into an Excel Spreadsheet. I'm on the recieving end of the data and have to make it trackable in the field. What I do is import the spreadsheet as it is, leave the data alone - except for some formatting changes and use an automated set of update, delete and append queries when new revisions are issued to take keep the db current. At one time I tried splitting the tables too and found it to be more of a hassle than what it was worth. I also got rid of the autonumber and just used the tagname as the primary key - as a method of controlling duplication.
Tag databases are a pain...

 

[Quintios]

Hmmm, you seem to know very well what I'm working on. I had considered leaving it as a flat file database and to be truthful, with disk space and memory as cheap as it is it wouldn't have made a difference. But whenever I do something I want to do it "right", and following a book I read this would not have been "right". So far things are working well within the database. The cool thing about it is that not only will it hold all the 'tags', but it will hold all the engineering design and discussion that was created during the project. See, I'm on both ends, the design and the maintenance.

The control systems we use are propritary but completely programmable and we implement total automation. With the way things are set up, you can conceviably program these computers from within my database. It's a big step in the right direction, I think. The only thing that will determine the success of my pet project is the user interface, namely, how usable is this thing? People like working in Excel because it is fast, people who review the work hate Excel because the spreadsheet ends up being really really wide.

I got rid of the Autonumber last week and replaced it with the TagName. It took some doing as there's a certain character that all our tags begin with and Access didn't like it very much. Took a while to figure out what to do with it. "'" & TagName & "'"

Onwards,

Q-

 

[Philly44]

OK I'm not expert on access and not on forum protocol either but I read through this particular thread and can't seem to find any actual "Answers" to khwaja's initial problem. I have read and received help by reading many of the threads that you all have posted in the past but I really think that the majority of the dicussion that went on in here should have been done in a different thread. Sorry if I sound like an upset parent but I just don't see how someone can get help to their problem this way.

Now I will add a little tidbit to the initial problem posted. One of the databases that I work on has a very similar relationship setup to the one you posted about. The way I got around the Primary key thing was I placed an invisible textbox on the form accessing the second table and set its value to the textbox containing the primary key of the first table on the initial form(its also an nvisible textbox).

I have this one-to-one setup because not all, in fact very few records require this information.

I hope that this helps and also that I have no offended anyone.

 

[JerryDennison]

Philly44, there is a very good reason why the initial question wasn't exactly answered in a fashion that you think it may should have been. The tack the question was following seemed inappropriate. That is, the use of multiple tables in a one-to-one relationship is not appropriate for his situation. In other words, he was causing more work for himself both now and down the road than was necessary. Having said all that, there are no hard and fast rules governing database design. One of Access's greatest strengths is the developer's ability to do pretty much whatever they want. One of Access's greatest weaknesses is the developer's ability to do whatever they want. Access doesn't care if your database is designed with the Forms of Data Normalization in mind. The Forms themselves are not hard and fast rules that you must follow.

BUT, the more I study and adhere to the Forms the more I come to respect their power to give you a robust and virtually error free database.

 

[pdldavis]

Hmm, would that be Bailey, Siemens or Plant 4D? That's a good idea - reference design review / correspondance within the same db. Might I ask how, in general terms, you do that?

 

[Quintios]

Email me. Not for public discussion.

Onwards,

Q-