A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old October 23rd, 2009, 12:34 AM posted to microsoft.public.access.tablesdbdesign
ryguy7272
external usenet poster
 
Posts: 1,593
Default Developing a new DB; Organization, Relationships, Cascade Update..

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
  #2  
Old October 23rd, 2009, 04:13 AM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Developing a new DB; Organization, Relationships, Cascade Update..

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.

--
Build a little, test a little.


"ryguy7272" wrote:

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

  #3  
Old October 23rd, 2009, 02:01 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Developing a new DB; Organization, Relationships, Cascade Upda

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.

  #4  
Old October 23rd, 2009, 05:01 PM posted to microsoft.public.access.tablesdbdesign
ryguy7272
external usenet poster
 
Posts: 1,593
Default Developing a new DB; Organization, Relationships, Cascade Upda

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---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Fred" wrote:

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.

  #5  
Old October 23rd, 2009, 05:18 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Developing a new DB; Organization, Relationships, Cascade Upda

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.

--
Build a little, test a little.


"ryguy7272" wrote:

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---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Fred" wrote:

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.

  #6  
Old October 23rd, 2009, 05:58 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Developing a new DB; Organization, Relationships, Cascade Upda

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

"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.




  #7  
Old October 26th, 2009, 09:15 AM posted to microsoft.public.access.tablesdbdesign
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Developing a new DB; Organization, Relationships, Cascade Upda

Fred wrote:

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/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.