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  

Designing a client database



 
 
Thread Tools Display Modes
  #1  
Old March 20th, 2006, 09:26 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 20th, 2006, 10:12 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 20th, 2006, 10:30 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 26th, 2006, 11:58 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 28th, 2006, 09:38 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old March 28th, 2006, 10:21 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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

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

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


All times are GMT +1. The time now is 01:23 AM.


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