Developing a new DB; Organization, Relationships, Cascade Update..

[ryguy7272]

Here’s the scenario. I have four tables, all with client-related data, for
instance, first name, last name, phone, address, notes about conversations
and discussions with each client, and a couple other fields. I have a PK on
each Table, with AutoNumber for the Data Type. I added one more table, Names
Table. I am thinking of creating four queries, each including the client
data from each table and I will link each of these four tables to the Names
Table. This will be a Search Query, so I can search for client names, and
see related records. Then I am thinking of creating an Update Query so I an
add data to the Notes filed. Does this make sense? Is this the best way to
set up this kind of DB?

I do have one more specific question. I linked the Names Table to each of
the four client-related Tables. I enforced referential Integrity, but I was
not able to check Cascade Update and I wasn’t able to check Cascade Delete.
Why can I not check cascade update or cascade delete?

Basically, I want to organize what is now four Excel files. Also, I want to
be able to collect and update conversations and discussions and interactions
with a given client so my team and I can know more about what the clients
like and don’t like as we prepare to contact each client (and maybe make
updates based on the conversation with the client).

I welcome any/all advice.

Thanks!
Ryan---

 

[KARL DEWEY]

Why can I not check cascade update or cascade delete?
Because you are using Access like it was Excel! You need to use it as
intended, a relational database.
One table for first name, last name, phone, address, and other information
that normally does not change (DOB). Use autonumber as primary key.
The other table does not need to repeat first name, last name, phone, and
address but needs a number field (long integer) as foreign key matching
people key field.
When you set a relationship on these two fields you can use the Cascade
Update but I would not select the Cascade delete as it can be destructive.
Use a form/subform for people/notes data entry and display. Set the
Master/Child links for the form/subform using the autonumber key/number-long
integer.

 

[Fred]

If you need additional help beyond the above......

I would suggest starting with fundamental structure, and start by describing
the fundamental "entities" that you want to database and the relationships
between them. To avoid your description getting derailed and avoid
misconceptions that could result in a non-description, I would start by
describing the above without using any Access or Excel terminology.

 

[ryguy7272]

Thanks guys! Basically, I have four tables. They are for TV, Radio, Print,
and Internet; all advertising. All contain client specific data. Some
clients show up in more than one Table, but many are just in one Table.
Right now I use a Form to query for a client, by entering the client’s last
name. Results are displayed in a ListBox. I double-click the client that I
am looking for (several clients could have the same last name), and results
are transferred to another Form. Some client-specific info is displayed
here, as well as ‘notes’, which are basically entered by a person who has a
conversation, or some kind of discussion, with the client. That’s pretty
much it. This concept works fine on one Table now, and I’m just using two
Forms to query from that Table and write (updating the Notes) back to the
Table. I’m wondering if this is the best way to do this. I was thinking of
having four buttons on the Form, so I can pick and choose which Table I’m
querying from and writing to. Does it make sense? Is there a better way to
do this?

Thanks for everything!
Ryan---

 

[KARL DEWEY]

Do not have separate tables for TV, Radio, Print, and Internet - just add
field to indicate which.
As I said, one table for client information and second for stuff related to
the client.

 

[Fred]

My advice on a better way to do this would be as before:

"I would suggest starting with fundamental structure, and start by describing
the fundamental "entities" that you want to database and the relationships
between them. To avoid your description getting derailed and avoid
misconceptions that could result in a non-description, I would start by
describing the above without using any Access or Excel terminology."

So far you haven't done that.

 

[Tony Toews [MVP]]

If you need additional help beyond the above......

I would suggest starting with fundamental structure, and start by describing
the fundamental "entities" that you want to database and the relationships
between them. To avoid your description getting derailed and avoid
misconceptions that could result in a non-description, I would start by
describing the above without using any Access or Excel terminology.

But to someone who doesn't understand what a database is all about
your description of what needs to be done is way too general. Thus,
in my opinion, it's better to be more specific as to the suggestions.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/