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
|
|||
|
|||
Designing a client database
Hello all,
I am trying to design a database to store the information of our clients. I would like to get some inputs from you guys on the design. Also whether anyone knows of a web site that I can go to download a sample client database. I am facing some difficulties on the design. I will discuss my difficulties below. Before I share with you my difficulties, the way we have our client number is different from most firm. Our client number is not automatically generated by Access. Instead we come up with a new client # whenever we add a new client. The structure of our client # is, for example, "1234.01". The first 4-digit represents the family and the last 2-digit represents the clients in the family. For example, if we do a job for both father and son, the client # for the father is 1234.01 and the son is 1234.02. In the past, the database was wide-open, and people would go into the table to add a new client. For example, if it is a new family, people would look up the whole table and come up with a new client # by picking the unused client #. That whole process created big problems because there were no controls on the database. Now I am trying to re-design the database where people use the forms to add new clients or modify existing clients. The difficulties that I am having is how do I design it so that: 1. the user can easily come up with a new client # for a new client 2. the user does not end up creating the same client with a different client # Thanks in advance for your help. Any suggestings or comments are greatly appreciated. |
#2
|
|||
|
|||
Designing a client database
The way this family relationship is handled in a normalized schema is with a
self referencing table. An autonumber is used as the primary key. A field defined as long integer is used to hold the FamilyID. When one record is related to another, the primary key value of the parent record is placed in the FamilyID field of the child record. The default for the FamilyID field should be null rather than 0 so just delete the 0 from the default field and do not replace it with anything. To build this relationship in the relationship window, add the table to the window two times. The second instance of the table will have its name suffixed with "_1" to differentiate it from the first instance. You would then draw a join line from the primary key of the first instance to the FamilyID field of the second instance. Select enforce referential integrity. On your forms, use a combobox to select the FamilyID field. As the RowSource use a query similar to: Select PersonID, LastName, FirstName From YourTable Where FamilyID Is Null Order By LastName, FirstName; This selects only people who have no FamilyID and so are presumed to be the head of a household. "AccessHelp" wrote in message ... Hello all, I am trying to design a database to store the information of our clients. I would like to get some inputs from you guys on the design. Also whether anyone knows of a web site that I can go to download a sample client database. I am facing some difficulties on the design. I will discuss my difficulties below. Before I share with you my difficulties, the way we have our client number is different from most firm. Our client number is not automatically generated by Access. Instead we come up with a new client # whenever we add a new client. The structure of our client # is, for example, "1234.01". The first 4-digit represents the family and the last 2-digit represents the clients in the family. For example, if we do a job for both father and son, the client # for the father is 1234.01 and the son is 1234.02. In the past, the database was wide-open, and people would go into the table to add a new client. For example, if it is a new family, people would look up the whole table and come up with a new client # by picking the unused client #. That whole process created big problems because there were no controls on the database. Now I am trying to re-design the database where people use the forms to add new clients or modify existing clients. The difficulties that I am having is how do I design it so that: 1. the user can easily come up with a new client # for a new client 2. the user does not end up creating the same client with a different client # Thanks in advance for your help. Any suggestings or comments are greatly appreciated. |
#3
|
|||
|
|||
Designing a client database
Several sample databases from the Microsoft site. Both can give you some
ideas on how to put together your database. This is a Contact Management database template: http://office.microsoft.com/en-us/te...3.aspx?Categor yID=CT011366681033 There is also a Membership database template: http://office.microsoft.com/en-us/te...CT011366681033 "AccessHelp" wrote: Hello all, I am trying to design a database to store the information of our clients. I would like to get some inputs from you guys on the design. Also whether anyone knows of a web site that I can go to download a sample client database. I am facing some difficulties on the design. I will discuss my difficulties below. Before I share with you my difficulties, the way we have our client number is different from most firm. Our client number is not automatically generated by Access. Instead we come up with a new client # whenever we add a new client. The structure of our client # is, for example, "1234.01". The first 4-digit represents the family and the last 2-digit represents the clients in the family. For example, if we do a job for both father and son, the client # for the father is 1234.01 and the son is 1234.02. In the past, the database was wide-open, and people would go into the table to add a new client. For example, if it is a new family, people would look up the whole table and come up with a new client # by picking the unused client #. That whole process created big problems because there were no controls on the database. Now I am trying to re-design the database where people use the forms to add new clients or modify existing clients. The difficulties that I am having is how do I design it so that: 1. the user can easily come up with a new client # for a new client 2. the user does not end up creating the same client with a different client # Thanks in advance for your help. Any suggestings or comments are greatly appreciated. |
#4
|
|||
|
|||
Designing a client database
Thanks guys.
"mnature" wrote: Several sample databases from the Microsoft site. Both can give you some ideas on how to put together your database. This is a Contact Management database template: http://office.microsoft.com/en-us/te...3.aspx?Categor yID=CT011366681033 There is also a Membership database template: http://office.microsoft.com/en-us/te...CT011366681033 "AccessHelp" wrote: Hello all, I am trying to design a database to store the information of our clients. I would like to get some inputs from you guys on the design. Also whether anyone knows of a web site that I can go to download a sample client database. I am facing some difficulties on the design. I will discuss my difficulties below. Before I share with you my difficulties, the way we have our client number is different from most firm. Our client number is not automatically generated by Access. Instead we come up with a new client # whenever we add a new client. The structure of our client # is, for example, "1234.01". The first 4-digit represents the family and the last 2-digit represents the clients in the family. For example, if we do a job for both father and son, the client # for the father is 1234.01 and the son is 1234.02. In the past, the database was wide-open, and people would go into the table to add a new client. For example, if it is a new family, people would look up the whole table and come up with a new client # by picking the unused client #. That whole process created big problems because there were no controls on the database. Now I am trying to re-design the database where people use the forms to add new clients or modify existing clients. The difficulties that I am having is how do I design it so that: 1. the user can easily come up with a new client # for a new client 2. the user does not end up creating the same client with a different client # Thanks in advance for your help. Any suggestings or comments are greatly appreciated. |
#5
|
|||
|
|||
Designing a client database
Hello all. I also have a similar design problem. We are a
theater/performing arts center. I am trying to track clients that attend our events, classes and also donate. We would like to be able to identify households which may inlude one or two heads as well as children, and be able to customize class brochure mailings, theater show mailings etc. The problem is that we do not want to mail flyers/brochures to children for things that are not appropriate for children. Also, we don't want to mail requests for donations to children. In addition, we need to be able to track donation amounts and services that our clients use. The other issue is how to track the ages of the children over time - calculating age. I don't understand how linking a table to itself works and if it will fit my needs. Pat Hartman(MVP) wrote: The way this family relationship is handled in a normalized schema is with a self referencing table. An autonumber is used as the primary key. A field defined as long integer is used to hold the FamilyID. When one record is related to another, the primary key value of the parent record is placed in the FamilyID field of the child record. The default for the FamilyID field should be null rather than 0 so just delete the 0 from the default field and do not replace it with anything. To build this relationship in the relationship window, add the table to the window two times. The second instance of the table will have its name suffixed with "_1" to differentiate it from the first instance. You would then draw a join line from the primary key of the first instance to the FamilyID field of the second instance. Select enforce referential integrity. On your forms, use a combobox to select the FamilyID field. As the RowSource use a query similar to: Select PersonID, LastName, FirstName From YourTable Where FamilyID Is Null Order By LastName, FirstName; This selects only people who have no FamilyID and so are presumed to be the head of a household. Hello all, [quoted text clipped - 39 lines] Thanks in advance for your help. Any suggestings or comments are greatly appreciated. -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
Designing a client database
I have now posted my question as a new thread - don't want to confuse my
request with the original. lhpac wrote: Hello all. I also have a similar design problem. We are a theater/performing arts center. I am trying to track clients that attend our events, classes and also donate. We would like to be able to identify households which may inlude one or two heads as well as children, and be able to customize class brochure mailings, theater show mailings etc. The problem is that we do not want to mail flyers/brochures to children for things that are not appropriate for children. Also, we don't want to mail requests for donations to children. In addition, we need to be able to track donation amounts and services that our clients use. The other issue is how to track the ages of the children over time - calculating age. I don't understand how linking a table to itself works and if it will fit my needs. The way this family relationship is handled in a normalized schema is with a self referencing table. An autonumber is used as the primary key. A field [quoted text clipped - 26 lines] Thanks in advance for your help. Any suggestings or comments are greatly appreciated. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200603/1 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
I need help populating a field based upon another table | Landywednak | General Discussion | 11 | January 14th, 2006 10:27 PM |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
How do I link a client database in Access, to Outlook's contacts? | Shine | Contacts | 1 | June 12th, 2005 11:29 PM |
Networking Database | Gary | General Discussion | 1 | June 9th, 2005 04:53 PM |
Database Window Gone | DaveB | General Discussion | 2 | July 29th, 2004 12:24 AM |