One to One Relationship

[BH]

I have set up a db in which one of the areas has too many fields for one table, in fact way way to many, so I have made 8 tables all on a one to one relationship using ID as the primary key.

What is the best and easiest way of populating all the tables with the ID number when I set up a new record. This is needed to relate the tables together.

To give you more information, I have set up one main form and the table information is on 8 subforms, by selecting cmd buttons you can choose which subform is open. As one subform is visible the others are not.

Hope this makes sense and someone out there can help me out,

Thanks for your help

BH

 

[oharab]

If your subforms are set up with linked child/master fields and your relationships are set to auto update, then they should be updated automatically.

Ben ----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[WildHare]

BH, the field limit for a Jet MDB is very generous. I just have this sinking feeling that your design is incorrect, and that you shouldn't need eight tables all joined together in a 1 to 1.

I've been designing database applications for 20+ years for clients from Dun & Bradstreet to the FAA, and I bet my 1-1 relationships number less than four in all that time. It's often a design flaw if you find yourself with that many 1-1 relationships.

Can you give us a hint of your specs and maybe we can turn you in the right direction.

Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[BH]

Wildhare

The reason for the 8 tables is that the part of the DB is based on information from a long application form. The Application form is in 8 sections so that is why I have split it into 8 tables. In total I would say there are about 1000 fields. Please tell me if there is a more correct way!

Oharab

I will check out what you have wrote and tell you the result

Thankyou both for replying to my post

BH

 

[WildHare]

This summer I did an app for the county that tracked and maintained their job readiness programs - so I'm familiar with 8-page application forms!

When I sat down to really look at the data, I found that I had a few one-many relationships that necessitated a redesign from the original.

For example, the person applying could have several "job history" records, and several "education" records. The old table had multiple repeating fields like Job1, Job2, Job3 etc etc etc - once I took a GOOD look at what the data really looked like, I was able to split out the logical application-form into a more appropriate structure.

If you're interested, I have a screen shot of the 'old' form that was about 5 screens deep, and the way it was redesigned, using overlapping subforms and command buttons on my site -

http://www.jmhare.com/screencap.htm

I think if we look at this problem, we'll be able to structure your system a little better.

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[oharab]

Jim,
had a look at your form & just have to say how much I like it! Clear, easy to follow & easy on the eye!

I also like the big drop down arrows. Are they a custom controls?

Ben ----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[BH]

WildHare

Had a look on your website as suggested, tried sending a reply in the guest book but would not work for some reason!

Anyway, yes do like the form layout.

I have a similar layout on mine, is there a way I can show you?

Must explain a bit more about mine so you get a better picture of what I am trying to do.

I have to input data from a application form that is 63 pages long, holds over a 1000 fields, this is not including any field that have contact details in it (explain this later). When the form is filled in not all the pages are used but I must have provision for them. The 63 pages fall into 8 sections, this is why I have split into 8 tables.

I have one form that opens up like the front page of the application, I then have 8 command buttons that open up the 8 different subforms, each section is on one of these subforms. When one subform is opened (by a click on the cmdbutton) the other subforms close. Each subform has the relevant pages on for the desired section, so for example if I click on the Part A button it opens the subform for the Part A section. In this section there are 12 pages so the subform has 12 tab pages. Basically the form design is identical to the application form.

There is quite a lot of name and address sections, for these I have used my contact records, instead of typing out all the details I just put in the relevant contact ID and all the other information fills out automatically.

I know it is hard to picture what I have done by just reading my text but I hope it puts you in the picture a bit more.

So back to the original question, when I start a new record in the application form and call it say "APP10" I would like all the 8 other tables to have this reference automatically rather than having to to open each section up and manually putting the reference in.

Does this make sense????????

BH

 

[BH]

Oharab

Yes the subforms are set up with linked child/master fields and the relationships are set to auto update, they will update automatically but it is the initial giving them the same reference that I am trying to automate.

If I go into each table through the form and manually give it the the same reference number then it works ok.

This is what I am trying to do automatically.

If you read what I have wrote back to WildHare it might give you a better picture of what I am doing. So hard to try and write it down in words.

Thanks to you guys for your help, i really appreciate it.

BH