t1/fm1 and t2/fm2 -- how coordinate?

[worldsci]

table1(t1) and form 1(fm1) have
field1 (fd1)
field2 (fd2)
field3 (fd3)

table2(t2) and form (fm2) have
fd1(same data as in t1-fd1)
fd2 (ditto)
fd3 (ditto)
fd4
fd5
fd6
fds 7-59

fd1 is a unique-identifying-code
there are 20k record in fm1
accordingly, there will be 20k in fm2

you can enter t2/fm2 by clicking a button on fm1 or by entering t2/fm2 directly
Q1: when you call up fm2, I want fd1, fd2, and fd3 to be populated with the data that is in fm1 -- how do I do this?
Q2: in fm 2, as you scroll through fd1, fd2, or fd3, I want data associated in fds 4-59 with the entity characterized by 1,2,and 3 to scroll also and change appropriately -- how do I do this?

this is a scientific, natural history database, not the usual suppliers/orders db, tho there are analogies

 

[jrbarnett]

I would base fm1 on tm2 but not put the unnecessary fields on it. Saves duplication which is regarded as a bad thing in database development.

If this is not feasible I would use an SQL insert query to copy the data over, you can use the fact that fm1 is a primary key in each to match the two up.

John

 

[worldsci]

you're right, I gave the impression t2 duplicates the data in t1, then has additional data in additional fields .. I didn't mean to give this impression, however .. I want to avoid duplication

let's take it as granted, though, for historical reasons, that I need two tables (t1 actually has more fields than I indicated, by the way)

accordingly, the goal is for t2 to contain many additional fields of data about the entities in t1, but not to duplicate any of the data (except the unique ID code?) which is already in t1

Q1a: how coordinate the two tables?

now, at the form level, we want the user to be able to open fm2, see the data from t1/fm1 at the top of the form and then see all the additional data about the entity in the remainder of the form ... also, when the users scrolls through the top part (changing from entity to entity), we want the additional data to change appropriately as well.

Q2a: how achieve this?

I hope this clarifies the situation.

 

[jrbarnett]

Looking at your message you clearly contradict yourself by saying that you want to avoid duplication, but also say that keeping the two tables is necessary. What you propose is duplication between tables which is still duplication. If you have any onsite I.T. staff with database design or development experience, I would consult them if you do.

However, to answer your questions:

1a. Based upon what I said before but extending it, the easiest way is to merge the two tables into one by adding fields from one into another then transferring the data using update queries.

For example:
update t2 set t2.fd25 = t1.fd25 where t1.fd1 = t2.fd1
(the above is SQL code to copy the contents of fd25 in t1 to fd25 in t2, where t1.fd1 and t2.fd1 match. This one query will pull across all 20000 data items).

Keeping the tables separate would prove an administrative nightmare to keep them in sync especially if updated frequently.

Should you decide that merging the tables is a bad idea, let me give you some food for thought:
It would entail writing code using the events in one form to do the equivalent in the other table (I do mean that; remember that forms just take data from tables and queries).
This causes a whole host of problems which need to be resolved.

For example:
a) What happens if a record from t1 or t2 gets deleted? Should it be allowed to remove the equivalent from the other table?

b) What happens if key fields from t1 or t2 get updated? Should they propagate across to the other?
c) Should a new record in one add a corresponding entry in the other table?

My preferred solution is to merge the two tables which removes all the problems above, because there is only one source of data. It may take a little longer to set up at first, but will be far easier for you and anybody who you employ to design, develop and maintain your systems.

2a. I have already covered this in basic level.
Basically, to keep the two table system you would need to write VBA code to reflect data updates/inserts and deletes in the other table to which the form relates.
If you decide to merge the tables into one, you remove the need to write code to keep the two in sync, as well as simplifying the design.

John

 

[worldsci]

maybe this will help .. what I am saying is that t1 has a short set of fields containing basic data characterizing an entity .. t2 has a very long set of fields containing additional data about this same entity, but it does not duplicate any data in t1 (except for the ID code)

that's the legacy table structure which, for a variety of reasons, I'm working with .. how is that for a structure?

I will take your suggestion of putting all in one table and discuss it with our group; that is a larger redesign issue which looks right now like a long road to slog through .. though sometimes it is worth doing some slogging

more immediately, what we want is a form which contains the fields from t1 and the top and then the fields from t2 at the bottom .. when you scroll through the top part, we want the bottom part to be coordinated with the top part and to scroll appropriately as well

your thoughts are very much appreciated