1db find/collate/summarize data from 3 other dbs?

[cwms]

Hello -

At my museum, I have created three identical databases (A1, A2, A3) that collect different data (about specific artifacts) in the same format from different users in different locations (three different conservation labs). These three databases are connected (one-to many) to another database (, which has information about exhibits (pulled into each artifact record in A1,A2,A3 by a pop-up and simple lookups).

I need to create a fifth database © for the manager which collates and summarizes the information from all three labs (A1,A2,A3). If possible, I’d like to perform finds from C across all three dbs (A1,A2,A3), showing summary information and detail. I’d like to control the find possibilities for the user of db C via pop-ups (e.g. exhibit, lab, entered by,… .

For utmost clarity, here are a couple of examples of finds I’d like in db C (all with detail artifact records, subsummaries by lab, and total record count):

# of artifacts for one (user-defined) exhibit in all three labs; # of artifacts in each lab between a certain (user-defined) date range; # of artifacts entered by a (user-defined) single person for a specific (user-defined) exhibit,...

My gut instinct says “portals!”, but I need to show records based on finds and/or filters which can be user-defined. So far, my (rudimentary) experimentation with portals has been frustrating. I’m not willing to give up yet, but I’m also not convinced they are the way to go here.

All suggestions and help will be embraced,

thanks.

cathern

[LiveOak]

The best approach is not to have three files, you are seeing some of the problems this causes. I would import records from all three files into a single file and report from that combined file. All other approaches are MUCH more complicated and less satisfactory.

-bd

[cjaeger]

Since those databases seem to be related to file B I assume you are on an network. Just include a simple login procedure which sets the username/labname in your combined database (using startup scripts).

Or if you are keeping your 3 databases: give your records unique serialnumbers (using autoenter) and an identifyer field . At report time, delete all records in Database C, do a scripted import from file A1, A2,A3 and do your calcs.