Using one front-end forms for two seperate states

[Alex Martinez]

Hello,

I made a claims database for my company which has several branches in New
York. It was a successful project. Now management want me to implement the
same database in our home state Washington which has several branches. I
splited the database in the same server in seperate folders call front-end
and back-end. I don't want to reinvent the wheel creating a new front-end
for our Washington branches. The problem is New York has there own set of
claims reviewers and Washington has there own set of claims reviewers. The
current front-end shows the names of all claims reviewers in New York and
the branches as well - number 1, 2, 3, 8. Washington branch numbers are
11,12,13, I don't want the user in Washington select the claims reiviewers
from New York or claims reviewer's from New York to select Washingtons
claims reviewer. Currently I have the branch numbers and claims reviewers
in seperate tables using combo boxes in the forms. Is there a easy way I
can use the same front-end forms for both states showing only the respective
branch and claims reviewers in the state they are in? All I want is the New
York people only populate the branches and claims reviewers name for there
state and for the Washington state users to see only there branches and
claims reviewers only. Is this possible? Any tips will be appreciated.
Thank you in advance.

 

[TC]

It's hard to give you detailed help, without knowing anything about how
you have structured the tables.

However, as a general rule, a properly designed solution would be able
to handle any number of different branches, *** without reprogramming
***. You'd just add some entries to some master tables. Indeed, a
properly designed solution should really be able to store *all* the
branches' records, simultaneously, without any confusion at all.

Unfortunately for you, these things are relatively easy to achieve when
you design the tables initially - but they are much more hard to add to
an existing system, after the event. You might have to rewrite a
significant portion of what you have done so far.

As an example of how to do it properly, lets say that the user needs to
be able to select a claims officer from a list of authorized claims
officers. An inexperienced developer would just do something like this:

tblOfficer
OfficerID (PK)
Forname
Surname
(etc.)

and then build a combo box on that table.

But that won't work if you use the system in other branches, which have
different officers! An experienced developer will see that coming a
mile away, and include two extra tables:

tblSite
SiteID (PK)
Location

tblSiteOfficer
SiteID ( composite )
OfficerID ( primary key )

Now the system allows for any number of different sites, each one with
its own list of authorized officers. So you could use the system
unchanged, at any site, just by putting approprate records in the
various tables. It would not need reprogramming.

Of course, that's just one example. Your database might contain dozens
of places where you'd need to enhance it, like the above.

If that is all too hard, you might just have to bite the bullet &
create a seperate front-end for each branch. But even with that, you
still wouldn't be able to accumulate /all/ the branches data in a
common database. Your table structures wouldn't allow that. If I were
you, I'd be asking very carefully as to whether they'll want to do
that, or not!

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[TC]

PS. Just in case it isn't clear: adding new tables to your existing
database structure, is only half of the story. You'd then have to
modify the program code, and/or write some queries, to actually /use/
the new tables.

TC

 

[Arvin Meyer [MVP]]

Hello,

I made a claims database for my company which has several branches in New
York. It was a successful project. Now management want me to implement the
same database in our home state Washington which has several branches. I
splited the database in the same server in seperate folders call front-end
and back-end. I don't want to reinvent the wheel creating a new front-end
for our Washington branches. The problem is New York has there own set of
claims reviewers and Washington has there own set of claims reviewers. The
current front-end shows the names of all claims reviewers in New York and
the branches as well - number 1, 2, 3, 8. Washington branch numbers are
11,12,13, I don't want the user in Washington select the claims reiviewers
from New York or claims reviewer's from New York to select Washingtons
claims reviewer. Currently I have the branch numbers and claims reviewers
in seperate tables using combo boxes in the forms. Is there a easy way I
can use the same front-end forms for both states showing only the respective
branch and claims reviewers in the state they are in? All I want is the New
York people only populate the branches and claims reviewers name for there
state and for the Washington state users to see only there branches and
claims reviewers only. Is this possible? Any tips will be appreciated.
Thank you in advance.

 

[Arvin Meyer [MVP]]

Hmm ... Double clicked on the last one when opening and sent it before
answering.

I'd add a table called tblDefaults to the database and a field to the
reviewer's table for state.

The tblDefaults table would have only one record and it would be unique to
the offices in that state. Join the state field in that Default table to the
state field in the reviewer's table and that will only show those reviewers
from the correct state. You could just as easilt modify it for the reviewrs
from a single office.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Marshall Barton]

I made a claims database for my company which has several branches in New
York. It was a successful project. Now management want me to implement the
same database in our home state Washington which has several branches. I
splited the database in the same server in seperate folders call front-end
and back-end. I don't want to reinvent the wheel creating a new front-end
for our Washington branches. The problem is New York has there own set of
claims reviewers and Washington has there own set of claims reviewers. The
current front-end shows the names of all claims reviewers in New York and
the branches as well - number 1, 2, 3, 8. Washington branch numbers are
11,12,13, I don't want the user in Washington select the claims reiviewers
from New York or claims reviewer's from New York to select Washingtons
claims reviewer. Currently I have the branch numbers and claims reviewers
in seperate tables using combo boxes in the forms. Is there a easy way I
can use the same front-end forms for both states showing only the respective
branch and claims reviewers in the state they are in? All I want is the New
York people only populate the branches and claims reviewers name for there
state and for the Washington state users to see only there branches and
claims reviewers only. Is this possible? Any tips will be appreciated.

You can certainly do this with a single frontend, BUT you
will have to modify the front end used for both locations.
Lacking the extensive changes that TC's complete solution
requires, you can just add a column to the officers table to
include a branch field, Then add something that allows the
frontend to identify the branch (e.g a one row frontend
table, a text file, etc.) that can be used as a parameter in
the combo box's row query to restrict the records to the
branch.

While not a rigorous solution, this requires a minimal and
simple change to the back end and a mostly localized set of
changes to the frontend.

Marsh

 

[TC]

Lacking the extensive changes that TC's complete solution requires,

Yes, after sending it, the word "overkill" did start to float through
my head !!

I do feel, however, that he should see if there is any possibility,
that they will want to accumulate all of the branch data into a single
database, for cross-reporting or other purposes. If so, he will still
have quite a problem on his hands.

Cheers,
TC (MVP Access)
http://tc2.atspace.com