Duane,
Actually Elizabeth has not said that she is storing the primary key from Plans in Safe_Harbor. In fact, it seems it is the other way round.
I think doing what you have said would solve the problem: Store PlanID in Safe_Harbor and adjust the subform accordingly.
Abraham
--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
> There should be no reason to store the primary key from the Safe_Harbor table in the Plans table since you already are storing the primary key from Plans in Safe_Harbor.
>
> Having attributes as fields kinda raises a red flag since this suggests you are storing data in field names. I could be wrong and it might be the best solution. My concern is when your pointy-haired-boss wants to add more attributes. In your current structure, this would require adding fields and modifying dependent objects such as forms, reports, and queries.
>
> Duane Hookom
> MS Access MVP
>
>
> > To: MS_Access_Professionals@yahoogroups.com
> > From: glcass58@...
> > Date: Fri, 28 Sep 2012 04:06:42 +0000
> > Subject: [MS_AccessPros] database design and data input forms
> >
> > Hi there-
> > I have a table called Plans (the loose definition of a "plan" is a "contract"). The fields in Plans are attributes of the plans. I have another table that has a 1 to 1 relationship with Plans, called Safe_Harbors. The fields in Safe_Harbors are attributes of a small, not often used subset of plans. SH_ID is the PK of Safe_harbors (autonumber) and the FK (long integer) in Plans. If Plans.Sh_id is null then I know it's not a Safe Harbor plan. I guess having this one to one relationship could be my problem since this isn't truly following relational db rules.
> >
> > I am having trouble with setting up a form for data input for the users. To build a form for users to input data on the attributes of a plan, I can make Plans the recordsource for the main form and create a subform with the fields from Safe_Harbor and have the child/master link be SH_id. If a user is editing data for a plan that already has a SH_id I'm good. If the plan does not have a SH_id and a user starts entering data in the subform a Safe_Harbor.SH_id will be created, but how do I get that into Plans.SH_id? Do I create a vba event for the subform's SH_id control that will insert the value in Plans.SH_id? Have I just answered why I should have one table with all the attributes rather than a one to one relationship?
> >
> > I think I get confused because in Access I get some data from other systems, I have users doing data input (not transaction processing, but input of these plan attributes and input of dates), I have reports showing different aspects of the plans, and I use the data to create automation mailings and emails. I try to have my tables/relationships serve all of these purposes, but is that correct??
> >
> > I would appreciate any advice, suggestions, suggested reading material, etc!
> > Thanks,
> > Elizabeth
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar