Re: strategy for data entry in multiple tables

I think you're misusing or misinstanding keys. You should have a PK in
Event, another PK in Subject, and 2 FK (EventID and SubjectID) in the
junction. You shouldn't be putting an FK in either of the master tables.
Example

Event
1 Gala
2 Dinner

Subject
1 Funding
2 Purpose

Junction
Event Subject
1 1
1 2
2 2

In the example above, I've omitted one of the possible combinations.
Nevertheless, we can see for the Gala the subject will be Funding and
Purpose, whereas for the Dinner the only subject will be purpose. If you
have a subform for Junction on the Event form and on the Subject form, you
can see what subjects will be discussed at the event or at what event the
subject will be discussed.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


"LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E136D420-8C1D-4B6E-AEC4-68EA6E0C1D97@xxxxxxxxxxxxxxxx
> Hi Paul,
>
> Thanks for the advice. Let us assume that there is one to many
> relationship
> between a master table and the junction table, for each master table.
> There
> is not necessarily one to one between the two master tables.
>
> In principle then, it appears that the best strategy is to combine data
> entry and validation for each master table separately, and then to copy
> the
> primary field to the data entry form for the junction table. I assume
> that
> there would be no problem with referential integrity to have a new record
> in
> a master field with no foreign key, yet, put in a new record in the
> junction
> table. Is this correct?
>
> Thanks,
>
> LAF
>
> "Paul Overway" wrote:
>
>> Comments in-line
>>
>> "LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:62C2C75F-0B93-47EE-8130-2728711D1134@xxxxxxxxxxxxxxxx
>> >A great strength of Access is the ability to base a form on a
>> >multi-table
>> > query and use the form for data entry. However, there are some
>> > important
>> > issues. Let us assume a simple database with two master tables and a
>> > junction table. One master table (subjects) has a primary key that is
>> > a
>> > text
>> > variable. The other master table is an event table with an
>> > autonumbered
>> > EventID as primary key. Assume for this example that tblEventID has
>> > fields
>> > EventID, Site, Date, Time.
>> >
>> > Issue 1. Should there be a single bound data entry form that has all
>> > fields
>> > from all 3 tables? For this to occur, code or macro must be written to
>> > run
>> > after the SubjectID is entered in the SubjectID field from tblSubjecst,
>> > to
>> > determine if a new record should be written in the subjects table. If
>> > a
>> > new
>> > record does not need to be made, then the SubjectsID field should be
>> > cleared,
>> > but the SubjectsID value placed in the SubjectsID field from
>> > tblJunction.
>>
>> Maybe? What is the relationship between Subject and Event? If 1 to 1,
>> ok.
>> But otherwise, it seems to me subject and event should be separate forms
>> with data from the junction table in a subform (if it makes sense to show
>> the related data in both places). You seem to be concerned about
>> duplicate
>> data in subject. Ok, so, do some validation before update/insert for
>> subject records....and if there is a duplicate, cancel the update and go
>> to
>> the preexisting record or prompt the user to do so.
>>
>> >
>> > Issue 2. A bigger problem occurs when data are entered for tblEvent on
>> > the
>> > single bound entry form. Code or macro must be written to determine if
>> > the
>> > non-primary key fields are already present in tblEvents. If they are,
>> > then
>> > the EventID from the matching record should be copied into EventID
>> > field
>> > on
>> > the data entry form for tblEvents. If not, then a new record must be
>> > written
>> > into tblEvent and the autonumber copied into the data transfer form.
>> >
>>
>> This seems very similar to issue 1, and the solution is as well.
>>
>> > The big question is: should there be separate data entry forms for the
>> > two
>> > master tables, possibly on unbound forms that pass appropriate values
>> > to
>> > the
>> > relevant data entry forms, or to an error event? The tblJunction data
>> > entry
>> > form would start out with the passed values in the foreign key fields.
>> > Is
>> > there a better strategy for dealing data entry with referential
>> > integrity
>> > and
>> > cascade updating.
>>
>> Yes 2 forms....with a subform for the junction data. Whether bound or
>> unbound, you're going to need to do validation to ensure that any new
>> record
>> is not a duplicate of an existing record. I'm not sure why you'd have
>> the
>> junction data on yet another form, if it is related to the master
>> tables....just use a subform if possible....then the foreign key for
>> either
>> subject or event would be passed automatically.
>>
>> >
>> > LAF
>>
>>
>>


.

Relevant Pages

  • Re: strategy for data entry in multiple tables
    ... The data entry form is not to look at records, ... I don't see how a subform for the junction table can ... be used appropriately with a form for just one of the master tables. ... If both master tables could be used in the same data entry form, ...
    (microsoft.public.access.forms)
  • Re: strategy for data entry in multiple tables
    ... between a master table and the junction table, ... primary field to the data entry form for the junction table. ... >> form would start out with the passed values in the foreign key fields. ...
    (microsoft.public.access.forms)
  • Re: strategy for data entry in multiple tables
    ... or both master tables. ... need to be entered into appropriate fields of the junction ... table data entry form. ... foreign key field on the junction data entry form. ...
    (microsoft.public.access.forms)
  • Re: Still Struggling...
    ... long, if it hasn't already happened, the need for junction tables, and how ... of junction tables, and a situation that involved keys, locks, people to ... doesn't belong in tblLocations and doesn't belong in tblEmployees so I ... Create Master Key table to account for the special attributes of Master ...
    (microsoft.public.access.gettingstarted)
  • Re: strategy for data entry in multiple tables
    ... include a "New" button for Birds and one for Events. ... display only the junction data and use the not in list event to add new ... While you do need data entry forms for Birds and Events, ... > foreign key field on the junction data entry form. ...
    (microsoft.public.access.forms)
Loading