Relationships and where to build them?

[Ron]

Hi All,

I have relationships on a database built in the back end, but I'm wondering
if that's where they should be. Can they be built in the front end but
apply to all the back end tables? Can someone advise?

Here's the scenario. User has a choice upon first entry of the program to
go into one of 10 separate databases, all with the same tables but with
different data stored in each... let's say "office". They go into "Office
1" and they use/alter/report on, etc the data for that office. When done
with that, go to the menu of offices, select another and do work in that
office, etc. 22 tables in each "office".

Within each database I have a table for zip codes, cities and states. The
user selects the zip code and it pops in the city, state on the form (only
storing zipID in the table). The user, upon finding a new zip code,
however, would need to enter it and save it into the zipcode table in each
of the different databases if they want to access it within the different
databases. Cumbersome to say the least. And certainly uncool.

It'd probably be better, to have the zipcode table in another database and
just link it into the tables when linking the others. But then, how do I
enforce referential integrity? I can't do the relationships to the main
back end tables since the zipcode table will be in another database. Right?
Do I link it into my back end database, have the relationships there, and
then link into my front end?

Is my question clear? ::smile:: (heck, I'm confused by it!!!)

Because of ownership of the "unique" tables within each database, I need to
keep the databases separate like this, but certainly that wouldn't apply to
the zipcode table, nor about 3 other tables that COULD be set up like this
if I can figure out how to do it.

TIA
ron

[Arvin Meyer]

Relationships are on tables, so they must be on the back-end The front-end
can only put relationships on local tables.

It may look like those linked tables have a relationship on the front-end,
but you'll notice that you cannot enforce referential integrity, so all you
are really doing is defining joins for queries.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Ron" <ro************ ********@verizo n.netwrote in message
news:bnRQh.8413 $P84.4209@trndd c07...

Hi All,

I have relationships on a database built in the back end, but I'm
wondering if that's where they should be. Can they be built in the front
end but apply to all the back end tables? Can someone advise?

Here's the scenario. User has a choice upon first entry of the program to
go into one of 10 separate databases, all with the same tables but with
different data stored in each... let's say "office". They go into "Office
1" and they use/alter/report on, etc the data for that office. When done
with that, go to the menu of offices, select another and do work in that
office, etc. 22 tables in each "office".

Within each database I have a table for zip codes, cities and states. The
user selects the zip code and it pops in the city, state on the form (only
storing zipID in the table). The user, upon finding a new zip code,
however, would need to enter it and save it into the zipcode table in each
of the different databases if they want to access it within the different
databases. Cumbersome to say the least. And certainly uncool.

It'd probably be better, to have the zipcode table in another database and
just link it into the tables when linking the others. But then, how do I
enforce referential integrity? I can't do the relationships to the main
back end tables since the zipcode table will be in another database.
Right? Do I link it into my back end database, have the relationships
there, and then link into my front end?

Is my question clear? ::smile:: (heck, I'm confused by it!!!)

Because of ownership of the "unique" tables within each database, I need
to keep the databases separate like this, but certainly that wouldn't
apply to the zipcode table, nor about 3 other tables that COULD be set up
like this if I can figure out how to do it.

TIA
ron