One to One - Multilple tables for large field qty

[Brahio]

HI,
I recently posted a question, but would like to resend in a different light.
The responses were only about my data not being normalized, but I just need
some guidance regardless.

I have to use one-to-one joins as our users have four different form types
needing to go into Access. Each form has some similar info in the header
(where I created a single table) such as created by, Date, Form ID#. I
created at a minimum one table for each of the four forms because the data
will be approaching the 255 limit. Two of the forms, I had to add another
table as to again not approach 255. There are no entities to break up (like
customers to orders, to details, converting to 3N, etc which I have more
experience in)

So here is my question. On the header table there is a form ID (primary key
with autonumber) which is joined with a one-to-one to another table2 (has
about 230 fields – questionnaire types, etc). I created a third table
joining the same FormID.

I created a form from a query and entered a new record. When I look at the
header table, it created formID#99, fine. When I look at table 2, I can see
that the record is created with the FormID #99 as well. Fine. But on the
third one-to-one join, it never creates a record, even when I have some
information populated. I was hoping it would create a recordset with only
the FormID #99 filled in.

Thanks for any feedback
Brahio

 

[Jeff Boyce]

Brahio

If you were advised to normalize your data, there's a reason.

Access is a relational database. Its features/functions are designed to
work with well-normalized data.

If you insist on feeding it 'sheet data (wide, many fields), both you and
Access will have to struggle to find ways to do what is simple if only your
data was well-normalized.

If you insist on using a spreadsheet-like data structure, you might not get
many folks offering ideas -- after all, I can drive nails with my chainsaw,
but it's neither safe nor easy.

Is there a reason you aren't just doing this in a spreadsheet application,
since it sounds like you've decided that's how your data will be organized?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Keith Wilby]

I have to use one-to-one joins as our users have four different form types
needing to go into Access.

Sorry, I've read that numerous times now and I still can't make any sense of
it.

Keith.

 

[BruceM]

If you describe your table structure and the general purpose of the project
it may be possible for somebody to suggest something, but as things stand
there may be little that anyone can offer. You say there are no entities to
break up, so the thing that needs explanation is the nature of the entity
that has several hundred unique attributes.

 

[John W. Vinson]

You say there are no entities to
break up, so the thing that needs explanation is the nature of the entity
that has several hundred unique attributes.

I gather that it's a questionnaire, and Brahio has fallen into the very common
trap of "one field per question". I believe he has been offered the normalized
approach in Duane Hookum's "At Your Survey" and chosen to reject it. I don't
know what else to suggest either, if he chooses to continue to struggle with
an incorrect structure.

 

[BruceM]

I wondered about that. About the only thing to say to the OP, then, is
"Good luck".

 

[Brahio]

Jeff,
Thanks for th reply. As I mentioned this was the second pass at having to
explain a reason why our business scenario need for this, I just need to
look for a technical answer. This database (something I did not mentioned is
this db is normalized with an abundance of one to many table surrounding it..
it is in 3N form...it just so happens that a few forms have too many fields
that needed to be broke up, but have a cenrally controlled autonumer ID
generated)

So, just a technical question outside of right and wrong: What is the VBA/
query process for getting the primary key of hdr table into the third table
of a three-table one to one join? In other words, when I join my hdr table
(one to one) with each form table, the creation of a record work fine (it
added the PK to both tables). If I add the third one-to-one, the primary key
does not copy to the third table (have to key in manually in order for the
join to work)

Thanks for any technical advice.
Brahio

 

[John W. Vinson]

So, just a technical question outside of right and wrong: What is the VBA/
query process for getting the primary key of hdr table into the third table
of a three-table one to one join?

The most typical approach would be to use a Form based on the parent table
(even one to one relationships have directionality, of course) and Subforms
for the child tables. Use the autonumber field as the master link field and
the matching foreign key as the child link field.

For a purely query based approach you would need an Append query to append one
record containing only the one ID.

 

[Brahio]

thanks John.

I think I know what you mean. My form is based on a query with all three
one-to-one tables. Are you indicating to create another query from the
header table to the third table and create a subform in order to get those
field to join?

 

[John W. Vinson]

thanks John.

I think I know what you mean. My form is based on a query with all three
one-to-one tables. Are you indicating to create another query from the
header table to the third table and create a subform in order to get those
field to join?

No. I'm suggesting that you base a Form on table1, a Subform on table2, and
another Subform on table3. No joined table queries are required or
appropriate.

 

[Brahio]

Hi John,

Regarding your comment below on the append query, I did create the append
query and it works fine when I run it manually. Thanks.

Can you tell me how to add the execution of the query to a to a beforeupdate
event on the form field? That is, when I create a new record, the autonumber
field populates the mainID field. When this happens, I want the append query
to exectute. Since I am not a VBA programmer, I cannot figure what the
coding should be in the beforeupdate event module of the field. Any help
would be great, and thanks again.

Brahio