Transferring Explicit Data from Table to Table

[Jim Ory]

WinXP and Access2K2

I have two tables with a one-to-many relationship. The first table contains
"Operator" data. The second table contains "Operation Location Data". So an
Operator may be operating at more than one location, but his main location
(the one) is in the First table. This first table contains where his "main"
location is and the Second table contains information on his/her other
locations (the many).

There are 5 fields of data (I'm concerned with) in the main table of the
operator's location. The majority of the time, the operators operate from the
main location. Occasionally, the operator is operating away from the main
location.

Because reports are developed from the "many" table, I would like to program
a command button to copy the 5 data fields from the Main Form (First Table)
to corresponding fields in the "SubForm" for the "many" side table.

The only data that would need to be copied would be for that person only and
only the five data fields. The fields are City, State, Country, Latitude, and
Longitude.

I realize that this is duplicating data, the five fields, but to try and
separate them for a report is beyond my scope at this time. An example would
be that this operator made 6 operations from the main location and 3 from a
remote location. All locations would be in the report, but not all operators
could be included in that scenario. Another could have 2 contacts from his
main location and 16 from a remote location.

I’m fairly sure that was confusing; but, any ideas on how to go about this
procedure?

 

[Larry Linson]

Yes, but you may not like it.

Add a Field to the "Operation Location Data" table, indicating that the
Record is the Operator's default/primary location, and remove the location
data from the Operator Table. Same kind of information should be in Records
in a Table, not arbitrarily split between two Tables.

This really won't be any more trouble than doing what you were asking
about... data doesn't get "fed" to Reports from Forms, so you'd have to save
it to the Table in any case.

And, the normalized Table design will save you time, energy, and frustration
in the future.

Larry Linson
Microsoft Access MVP