Simaltaneous updating of duplicate/truplicate data in several tables

[nardvark]

I'm wondering if anyone knows of a way to have identical data updated in several tables at the same time. The database work I do involves scientific information, and the layout implied by access does not lend itself to these applications. Basically, we've got 4 database tables, and we want to be able to have information in one table instantly updated in the other three for 10-15 fields to avoid having to type it all manually, and to avoid the increased chance of error caused by increased typing. I can get a pop-up list of all of the possible values, but I really want it to select a specific value based on specific criteria that will search and find a corresponding record. We can't have one single over-riding ID for all of these things, because in each table the fundamental identifier is different (in one case it's a test run number, in another a particle ID, in another a surface deformation ID, and the last is a sample preparation database).
I'd like to avoid dealing with creating forms/reports, and would prefer to simply deal with the data in the tables themselves, but I'm starting to get the impression that most of the more advanced data management features aren't available in tables (I'm a pretty new user, so I may be missing something terribly obvious).
Thanks in advance for any advice!
Dan Lundberg

 

[KristenCarr]

Is there any reason you have duplicate data to start with? There may be something I'm not understanding but one of the fundamentals in db design/normalization is to not store the same information more than once so as to avoid this kind of situation. I'm not sure how to fix it except to avoid the problem from the beginning - with your design. You should be able to remove the columns from secondary tables and just link to the primary table(s) via a foreign key.

Hope this helps!

 

[nardvark]

We have duplicate data because we aren't using the database as a means of storing data in the most logical fashion possible, we're using it so that we can isolate trends and easily compare all of the possibly important variables. So we list things next to eachother everywhere they are associated to make comparisons easier. The problem is that the relationship between the duplicate data and the data unique to each database is different for each database, so having one global list linked to all of the other tables wouldn't actually work, because the primary keys are all different...does this help explain the situation better?

 

[KristenCarr]

Ahhhh...so you need the least normalized! Sorry, I don't know how to help you and I can't think of a good resource since you are, in reality, breaking all the rules. I'll ask around to see if anyone can help.

Good luck!!! I'll keep my eye out for an answer for you.

 

[blarson0]

Kristen is right, you really do need to split up all the data. Most versions of access have a built-in table splitter that might be able to help you, but you will probably have to do it manually. Could you give the duplicate data it's own primary key (an autonumber), and just link it into the tables that way? or add 4 fields to the duplicate data table, and have the primary key for each of the other main tables?

If that doesn't help you enough, please try to describe your tables a little more, and how they are related to each other (and the duplicate data), and then we will go from there.

HTH!

-Brad

 

[nardvark]

Okay-I'm going to write a lengthy description, because it's dawned on me how unique (and perhaps poorly organized) our database system is.
The first thing that is modified is the "Tests" database. In this we place the test date, a prescan date, and a number of fields that become duplicate data, including gap size, voltage, electric field, current, etc...that are common to every event that occurs in a test. These are all organized under a spatial location system of Plate, SubPlate, and SubSubPlate, which determine a calculated field called "SpotID," which is just a conglomerate of the three spatial identifiers.
Then we enter data into a "Starbursts" database. Here we have all of the Starbursts that are created during a test. Along with all of the unique starburst characteristics, each starburst record has a copy of the above mentioned duplicate data that corresponds to the location of the Starburst. Currently our database is on an old version of Filemaker Pro, and we're trying to convert it into Access (to make it easier to integrate with excel and powerpoint). In Filemaker Pro, the SpotID is built up from the spatial locations and then the duplicate data is automatically looked up by matching the Starburst and Tests SpotID's. As far as I've been able to tell, I can't find a way to define a field like this in Access.
Then there is the "Studies" database, which follows a similar procedure to the Starbursts database in finding the duplicate information. It also procures some information from the Starbursts information by looking it up once a field called "Contained within which Starburst #?" is filled in.
The fourth database adds nothing new to this discussion, so I'll omit details.
So on to the big problem.
The problem with trying to link to a duplicate data table with a primary key is that the duplicate data itself isn't necessarily unique, it's the date and location it occurs at. So I suppose that could be our primary key, but I'm not sure how to link to the other tables. Each location will have 5-15 Starbursts associated with it, and each Starburst will have 5-10 Studies associated with it. I want to be able to retain the global information in a table next to the more specific information, because often we want to compare the global and specific information to find trends, not just compare the specific information against itself.
Thanks in advance for any comments or help, I realize this is lengthy, but it might provide some interesting thought for those of you out there who have never ventured beyond a purely business-oriented database system!