Combining 4 branches into one

[CD Tom]

I have the task of combining four branches into one database, that isn't the
problem. The problem is that each branch only wants to be able to see only
there perticular data except for the empolyee table. I was thinking about
just adding a branch code to the end of each table and then depending on the
log-in appending the code to the table name. I was also thinking about just
adding a branch code to each table, this would keep the tables the same, but
then with all the sub forms, queries and things in the program not to sure
how to get those to work right. I will also have to be sure I know which
branch the user is logging in from and keep that all the time that branch is
using the program.
Any ideas would be helpful, just looking for what's the best way to start
this project.

Thanks for any advice.

Tom

 

[RBear3]

Typically, you would add a "branch" field to your table (one table) and each
record would include the branch. You can limit the ability for users to
view the records by creating several queries and set them to run with
owner's permission. The users would be given no access to the tables or the
queries.

For more information, do a search and read some of the previous posts on
"run with owner permission".

I would probably not create multiple tables. Any time you add a new
division, you would have to create a new table, and new forms, and new
queries, and new reports. UGH! Not a normalized approach.

 

[Klatuu]

Create one master query that does the filtering as you planned. Then use
that where you are currently referencing the table itself.
Here is a link to a great little utility that you could use to find all the
references to it and make the appropriate changes. It is only the cost of
about two boxes of ammo. ($37.00)

http://www.rickworld.com/download.html

 

[CD Tom]

I thought that would be the better way to go for just the reasons you
mentioned. Using different queries would work except where the query is part
of the sub form and I don't know how to change that query depending on the
branch. I'm sure there is a way changing the record source through code, but
not to sure how. I didn't find much on running with owner's permission in
this but will look further using google.

 

[CD Tom]

Good to hear from you. If I create a single master query what would be the
best way to put the branch code into the query? Also what about where the
selection is in the sub form itself how do you change that on the fly.
On a personal note, seeing you're in Texas(?) are you going to get to EOT
some time, now that it's in NM it shouldn't be that far of a trip. I go
every year as I do all the scoring and posse assignments.

CD