Please Help - Maintaing relationships in multiple tables after append (1 Viewer)

[plantfinder]

I have two tables, Plants and Photos in my database.
I have an autonumber index ID field for each table.
Photo records are linked to a specific plant record by the Plant ID # contained in a field in the photo record.
I have data being gathered and entered on a computer in 2 remote location into these tables in identical Access Databases. I now need to put any new information gathered in these locations and enter it into a master Database.
I have no problems appending data into the master database but I cannot figure out how to maintain the relationships between specifc records so that the same plant records in the master database are associated with same photos, as the were in the remote databases.

Please help. It would be greatly appreciated

 

[glynch]

I think what you are saying is that in one db you have a record in the plant table for a geranium with a plantID of say, 114. In the photo table of the same db you have 3 photos of geraniums each with a plantID value of of 114. However in the other db the geranium record in the plant table has a plantID of 237 along with it's associated photo records. And how do you get the 114 lined up with the 237?

If this is the case you need to have another field that uniquely identifies a plant (scientific name?). Then link on that common field to first create your plant records in the master db, and then the dependant photo records. If you don't have a field or series of fields common to the plant tables in both db's that can uniquely identify a record, then you have a lot of manual work to combine the data.

Then quit using the two remote db's and use just the one master. You can set up multiple front end mdb's that link to a common master tables mdb.

Good Luck

 

[plantfinder]

Thanks Glynch,

A few questions:
Would the new Unique Field in the Plant Table be better off as a # or a # with a letter suffix?

In the new Master Database would the Photo table be linked to the Plant table by this new "Unique Field" and WOULD THIS FIELD THEN have to be designated the Primary Key Field as opposed to the
Auto# field that I assume would still be generated in the new table or is that not necessary?

Also, I'm unfamiliar with the concept of" multiple front end mdb's that link to a common master tables mdb" any enlightment would be appreicated. I did not see any reference to this in the Help File.
Thanks

 

[glynch]

I can't really answer the first question for you, unless it is a large table it probably makes no difference as far as performance. Number fields, when indexed, are generally searched faster than text fields, but again if this is relatively small (< 10,000 records), you may not notice much difference.

Personally I prefer to use autonumber fields as primary key fields so you can link to a long integer foreign key field in another table, but there are a lot of ways of doing it.

To create front and back end mdb's make a copy of your master mdb, then open a new empty database, go to File/Get External Data/Import. Find your master mdb, then select all of the queries, forms and modules from your existing (the master mdb you've just created), and import them into your new mdb. Go to that master mdb and delete all of those same objects. Then go into the new mdb, go to File/Get External Data/Link Tables, and find your master mdb and select all of the tables. Your master mdb is now your backend tables mdb, and the new mdb you just created is your front end. You can set up multiple front ends by copying your front end to different machines as long as they can all link to the backend wherever it is. this requires some kind of neteworking.

There are a lot of other issues involved involved in setting this up, security amount of use, number of simultaneous users, etc.

If you don't know what you are doing, keep it as simple as possible. Access is not very robust across multiple users and/or with large databases. If your needs are in that direction get some help in getting things set up. I am not a networking expert but I have set up and worked with Access on smaller networks with a limited number of users.

HTH