Design questions

[Bunky]

We are in the process of designing a new database that will be utilized by
several areas of the company that are housed in multiple states. We are
going to have an Access Database that has 6 different tables. We wanted to
use a Web page that will feed a query to update these 6 tables accordingly.
However, in preliminary testing, it will only update these tables IF all the
tables have the same KEY. We have tried have the same key with foreign keys
but that did not work at all. Any ideas how we can get this to work?

Bunky

 

[Pat Hartman]

I think we're going to need to know why you need one query to update 6
tables with the same data. If the data is duplicated for each state, you
need to look into replication.

 

[Jeff Boyce]

As Pat points out, you are describing a "how", as in how you are trying to
do something.

But we don't have a very clear picture of "why", as in "what will having six
tables all updated the same allow you to do?".

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bunky]

Okay, sorry to be vague.

We have 6 tables that contain different data. One is for Issue or Problem
data, One is for Person data (the person who was assigned the problem), one
is for the Owner information, one is for the Resolution (type, amount of
compensation, etc.), one is for Legal entities (BBB or Law offices), and the
last one is for notes ( all kinds of notes from when the issue started to the
final resolution. There will be several areas over several states entering
the data into this data base. A common key, if you will, will be the Issue
number. We thought this would be a foreign key on all the tables except the
Issue table where it would be the primary key. The other tables have primary
keys that are pertainate to that table.

Now the problem we are having is having a query to enter the data on the
individual tables without getting key violations. We went though several
iterations of the relationships and how the tables should interact with each
other. Every option we tried gave us a key violation when running the query.
We even deleted all of the relationships and tried to do it with just the
Joins in the query; still no luck.

Ideas?

 

[Jeff Boyce]

I don't believe it is a matter of "vague-"ness ... if we don't understand
what you are doing (and why), it's hard to offer specific suggestions...

It sounds like you are describing a situation in which one "Issue" can have
many "Persons", "Owners", "Resolutions", "Legal Entities" and "Notes". At
least, using the [IssueNumber] as a "common key" implies that to me.

Does that fairly depict your situation? If not, please describe more
specifically what the relationships are among all these "entities". It may
be that you are running into problems getting the updates done because of
the data model you are using.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bunky]

You are very close
one to many
Issue - Persons
Issue - Notes
Owner - Issue

One to One
Issue - Resolution
Issue - Legal

Thanks for your time and assistance!

I don't believe it is a matter of "vague-"ness ... if we don't understand
what you are doing (and why), it's hard to offer specific suggestions...

It sounds like you are describing a situation in which one "Issue" can have
many "Persons", "Owners", "Resolutions", "Legal Entities" and "Notes". At
least, using the [IssueNumber] as a "common key" implies that to me.

Does that fairly depict your situation? If not, please describe more
specifically what the relationships are among all these "entities". It may
be that you are running into problems getting the updates done because of
the data model you are using.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay, sorry to be vague.

We have 6 tables that contain different data. One is for Issue or Problem
data, One is for Person data (the person who was assigned the problem),
one
is for the Owner information, one is for the Resolution (type, amount of
compensation, etc.), one is for Legal entities (BBB or Law offices), and
the
last one is for notes ( all kinds of notes from when the issue started to
the
final resolution. There will be several areas over several states
entering
the data into this data base. A common key, if you will, will be the Issue
number. We thought this would be a foreign key on all the tables except
the
Issue table where it would be the primary key. The other tables have
primary
keys that are pertainate to that table.

Now the problem we are having is having a query to enter the data on the
individual tables without getting key violations. We went though several
iterations of the relationships and how the tables should interact with
each
other. Every option we tried gave us a key violation when running the
query.
We even deleted all of the relationships and tried to do it with just the
Joins in the query; still no luck.

Ideas?

 

[Jeff Boyce]

Right off the top of my head, if a single Issue can have only a single
Resolution (and a single Legal), why do you have separate tables for those?

Next, using a relational database like Access, if you have Issues, and you
have People, and one Issue can have multiple People, you need THREE tables,
not two. Ditto for Issue and Owner. Three tables for each relationship
assumes that one Person could show up associated with many Issues, as could
one Owner.

For Notes, a Notes table with a field that points back to "its" Issue would
suffice.

Does this give you more to go on?

Regards

Jeff Boyce
Microsoft Office/Access MVP

You are very close
one to many
Issue - Persons
Issue - Notes
Owner - Issue

One to One
Issue - Resolution
Issue - Legal

Thanks for your time and assistance!

I don't believe it is a matter of "vague-"ness ... if we don't understand
what you are doing (and why), it's hard to offer specific suggestions...

It sounds like you are describing a situation in which one "Issue" can
have
many "Persons", "Owners", "Resolutions", "Legal Entities" and "Notes".
At
least, using the [IssueNumber] as a "common key" implies that to me.

Does that fairly depict your situation? If not, please describe more
specifically what the relationships are among all these "entities". It
may
be that you are running into problems getting the updates done because of
the data model you are using.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay, sorry to be vague.

We have 6 tables that contain different data. One is for Issue or
Problem
data, One is for Person data (the person who was assigned the problem),
one
is for the Owner information, one is for the Resolution (type, amount
of
compensation, etc.), one is for Legal entities (BBB or Law offices),
and
the
last one is for notes ( all kinds of notes from when the issue started
to
the
final resolution. There will be several areas over several states
entering
the data into this data base. A common key, if you will, will be the
Issue
number. We thought this would be a foreign key on all the tables
except
the
Issue table where it would be the primary key. The other tables have
primary
keys that are pertainate to that table.

Now the problem we are having is having a query to enter the data on
the
individual tables without getting key violations. We went though
several
iterations of the relationships and how the tables should interact with
each
other. Every option we tried gave us a key violation when running the
query.
We even deleted all of the relationships and tried to do it with just
the
Joins in the query; still no luck.

Ideas?

:

As Pat points out, you are describing a "how", as in how you are
trying
to
do something.

But we don't have a very clear picture of "why", as in "what will
having
six
tables all updated the same allow you to do?".

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


We are in the process of designing a new database that will be
utilized
by
several areas of the company that are housed in multiple states. We
are
going to have an Access Database that has 6 different tables. We
wanted
to
use a Web page that will feed a query to update these 6 tables
accordingly.
However, in preliminary testing, it will only update these tables IF
all
the
tables have the same KEY. We have tried have the same key with
foreign
keys
but that did not work at all. Any ideas how we can get this to
work?

Bunky