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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|