One DB for many projects?

[esegal]

I tried asking elsewhere, but this area seems more appropriate.

I have a simple DB setup where I have a separate DB for each project my office is working on (ProjectDB), all link to my ContactsDB. I would like to Merge all my projects into one DB.

Issue: I am thinking of setting up one master ProjectsDB whereas I can switch between different projects on the fly, so the DB only reflects info related to that particular project and only allows info to be entered for that project. This is just for my correspondence files (faxes, transmittals, memo's, shop drawings, logs, etc.) and I would like it to be idiot proof. My current ProjectDBs have a lot of auto-enter data on the pages, so essentially the DB would have to "become" the selected projects DB with no evidence of any other projects present.

So - Do I keep my existing DB's and make a new one that can change relationships between different project DB's, or do I work on the main file and somehow isolate each projects info within it. Keep in mind each current record contains a Global field for "Project Name" and "Project Number". This might be helpful in isolating their info in a new file.

I would prefer not to have too many of the same database because it makes tweaks and upgrades unbearable.

Thanks for any advice.

[Jacob22x]

I'm currently setting up a database for my company with this same idea - we had an access database for each project, now we're going to have one database (using FileMaker) with all the projects. I have mine set up where "project" is one table and each project is given a unique id, and it is linked to my other table (issues) which has the foreign key _kProjectID. That way every issue (the table with the most records, to which everything else is linked) is associated with a project.

When the database starts up it takes the user to a projects page - when the pick a project, it copies the projectID number into a global field and then takes them to another layout within that project. Then, anytime the user creates a new record I have a script run that auto-enters the global project ID into the projectID foreign key field in "issues." Also, anytime the user switches to another layout, the script does a find for that project, meaning that the found set is only records belonging to that project.

The way I did it has some problems, but it seems to work pretty well overall.

[esegal]

What you are doing sounds like exactly what I'm trying to do. By any chance would you be willing to send me a sample I can look at? Maybe my current DB will help you iron out your wrinkles.

Espen

[Jacob22x]

I'm posting an older version of what i'm working on, so you can see how I set it up. I figure it can at least give you some ideas. This version is definitely a work in progress so no promises on what works and what doesn't, etc. Sorry it took me so long to reply.

Databasev3.1.fp7.zip

[esegal]

Excellent. I'll let you know how it goes. Maybe I can offer some assistance, too.

Espen