Ok, I've asked similar questions to this in the past, but I need to re-vamp this department database once again, under a new set of criteria. Is there an easy way for a field on a form to be copied to multiple tables at once? For instance, on my "Graduate Student" form there's a field called "Address" which is a field in the "Grad Student Info" table, the "Information" table, as well as the "Mailing" table. At present, the record source for the form is from the "Information" table, is there any way that data entered to that field (adding a new address) can be entered not only to the information table, as well as the other two with like-named fields, preferably without adding a segment of VB code for each like-named field in the tables, (they just added 55 new ones! EEP!) I should add that all my tables are linked by a "MasterID" field, linked by one "Master" table.
Add contents of field to multiple tables
- Thread starter Naterial
- Start date
Jack Cowley
Registered User.
- Local time
- Today, 00:01
- Joined
- Aug 7, 2000
- Messages
- 2,639
You do NOT want to add that data to another table. The information already exist in your Information table so you can show an address at any time by useing a query as the forms Record Source. Be sure your tables are properly related and normalized and you should be good to go...
hth,
Jack
hth,
Jack
Yes, I know I don't want to add it, but my supervisor wants the database set up that way. So I either need to find a way to do it, or meekly try and convince them that it doesn't make sense, especially since I made a nice set of forms and switchboards that render those other tables pretty much unncecessary. This would have been a lot easier if I could have just written the database new from the ground up, but instead I was hired on and given the task of making a thousand changes to this old one.
Jack Cowley
Registered User.
- Local time
- Today, 00:01
- Joined
- Aug 7, 2000
- Messages
- 2,639
Hmmm. Well, grab some courage and tell him that you are the programmer hired to do the job and you know how to do it. Or ask him why he hired you when he could have done it himself and saved your salary. Stick to your guns....
Jack
Jack
Ok, I've spoke to my supervisor (she) told me that the request actually came from some higher-up Faculty members, as well as a related, separate office which will also use the database and the addition of all those redundant fields was their decision. So, I'm pretty much stuck. (Did I mention I work for a University?) Bascially, we have the preferences of the faculty, the preferences of another office, and I have to make everyone satisfied.
Jack Cowley
Registered User.
- Local time
- Today, 00:01
- Joined
- Aug 7, 2000
- Messages
- 2,639
I suppose you could do it the right way and then hide the tables and give them an .mde file... Would they every know?
Actually, you have to do what they want, but it is enough to make you grind your teeth....
Good luck...
Jack
PS. Never trust a professor....
Actually, you have to do what they want, but it is enough to make you grind your teeth....
Good luck...
Jack
PS. Never trust a professor....
R
Rich
Guest
Why not print off some of the articles on db normalization and let them read it
Yes, though I'm beyond the teeth-grinding stage. *banging head on desk* So, back to my original question, is there an easy way for a field on a form to be copied to multiple tables at once? It seems the faculty would like the "convenience" and "quick reference" of looking at one table that has nearly -all- the fields already present in three other tables. I'm not sure what's so convenient about looking at a table as wide as five football fields, while my new forms and switchboard display everything relevant for one person (each record is either an undergraduate student, graduate, or faculty member) from three tables at once, but it seems they want both my method, as well as their idea too. *continuing to bang head on desk*
Oh Rich, believe me, if I could explain it to them without getting them all offended, I would. When I came here, the database was set up so each table had it's own ID number. The faculty table had a "FacultyID" number assigned to each record, Graduate had "GraduateID", etc. I immediately set up a Master table with "MasterID" so every record would have it's own unique number, making the forms possible. Of course I couldn't get rid of the individual table "ID"s without raising holy hell from someone. Which is fine, I just used the smaller "ID"s to help order things. But I'm not high up enough in the food chain to do things the efficient way, so I need to find a simple way to copy fields entered into a form to multiple tables at once, never mind the fact the fields are already present in other tables and are more specific.
Oh, I should point out that I don't intend to use the other two tables (redundant) to provide any data to run my form, I'll keep that same design of just using the tables I originally intended to run it. Basically, when someone enters data into a field on the form, I need it to update the original tables (already done) but also copy the fields just updated to the two other (redundant) tables.
- Local time
- Yesterday, 19:01
- Joined
- Feb 19, 2002
- Messages
- 47,578
I'm sure we're flogging a dead horse here, but this is such an important part of good application design that I feel that one more try is in order. Users should Never be looking at raw tables and queries anyway, so ....
1. Get rid of the extraneous tables.
2. Make queries that are named with the names of the deleted tables. So for example, delete the GradStudent table since it is redundant but write a select query that essentially "recreates" it and give the query the name of the old GradStudent table.
3. Then, to wean them from the table and query window, create a form in datasheet view that is based on the new table. The datasheet view form will look and act very much like a table displayed in datasheet view.
You will end up with a more friendly interface since they can access the new "forms" from your switchboard and have the benefit of a fully normalized table structure.
To answer your question more directly, there is no easy way to duplicate data. There is no reason for there to be such a facility since doing so violates the principles of good database design. That means that you are left with writing code that is executed from your main form's AfterUpdate event to Add/Change/Delete the data in the related tables. You can do this with DAO or ADO. You will find more examples of DAO code here so you might want to go with that. Look in help for addnew, edit, and delete methods to find code samples.
PS, feel free to point your manager to this thread and we'll beat on her for you
1. Get rid of the extraneous tables.
2. Make queries that are named with the names of the deleted tables. So for example, delete the GradStudent table since it is redundant but write a select query that essentially "recreates" it and give the query the name of the old GradStudent table.
3. Then, to wean them from the table and query window, create a form in datasheet view that is based on the new table. The datasheet view form will look and act very much like a table displayed in datasheet view.
You will end up with a more friendly interface since they can access the new "forms" from your switchboard and have the benefit of a fully normalized table structure.
To answer your question more directly, there is no easy way to duplicate data. There is no reason for there to be such a facility since doing so violates the principles of good database design. That means that you are left with writing code that is executed from your main form's AfterUpdate event to Add/Change/Delete the data in the related tables. You can do this with DAO or ADO. You will find more examples of DAO code here so you might want to go with that. Look in help for addnew, edit, and delete methods to find code samples.
PS, feel free to point your manager to this thread and we'll beat on her for you
Last edited:
Showing my supervisor this thread probably won't help. The faculty always get the last word, and they're higher up the "food chain" than even her. Trust me, they want this in addition too. I don't know why they want it, but as the lowest-ranking member of the "chain", I get to do this -their- way. Though they like my design too. Rock and a hard place, etc. Any particularily good examples of DAO code leap to mind which may be applicable?
Working in a uni I know how difficult it is to make academics (mainly) understand good db design.
I have to put up with multiple copies of the same tables on local disks rather than sharing on the server
Forget trying to explain normalisation, been there, they just don't get it.
I had to normalise an old exam db that had 40 + fields in one table (1 for each Course Module!) and that was just 1 db. They created 4 dbs with this same design (1 for each student year)
Each had a 72 'page' vb function to calculate an exam mark.
And this was created by 'professional' academics!
To date, this is the worst 'de-normalised' db that I have come across and i've seen a few!
I have to put up with multiple copies of the same tables on local disks rather than sharing on the server
Forget trying to explain normalisation, been there, they just don't get it.
I had to normalise an old exam db that had 40 + fields in one table (1 for each Course Module!) and that was just 1 db. They created 4 dbs with this same design (1 for each student year)
Each had a 72 'page' vb function to calculate an exam mark.
And this was created by 'professional' academics!
To date, this is the worst 'de-normalised' db that I have come across and i've seen a few!
