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  

Normalization



 
 
Thread Tools Display Modes
  #1  
Old March 12th, 2010, 08:53 AM posted to microsoft.public.access.tablesdbdesign
sys_analyst47 via AccessMonster.com
external usenet poster
 
Posts: 11
Default Normalization

Hi,
I am developing a inter dept application which will help us out to track the
auto loan application receiving, status of application, daily activity of
staff & few more things beside that. The problem which i am facing to design
tables is this I have a contract which needs to be issue to customer if the
loan has approved which is based on 36 fields which are as under just suggest
me is there any way to split this ?? if i will split this in different tables
I have to setup a mail merge with Word So will i be able to do that??

Customer identification
Customer name Branch
Sales officer
Local purchase order date
Vendor
Vendor address
Brand
Model
Year of Make
Color
Chassis no
Engine no
Original cost
Insurance cost
Total cost Down payment
Total deferred payment
1st installment date
Last installment date
1st installment amount
Last installment amount
Place of registration
Vehicle owner name
Passport no
Debt account
Commission amount in no
Commission amount in words

this is my customer table from where i generate contract.
The idea a customer record is entered there should be no redundancy. Please
don't forger that I have to issue contracts by mail merge. Or is there any
way to design report for contracts. it contains 12 pages for one customer.
can we design such a report???

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201003/1

  #2  
Old March 12th, 2010, 09:21 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Normalization

On Fri, 12 Mar 2010 07:53:59 GMT, "sys_analyst47 via AccessMonster.com"
u58607@uwe wrote:

Hi,
I am developing a inter dept application which will help us out to track the
auto loan application receiving, status of application, daily activity of
staff & few more things beside that. The problem which i am facing to design
tables is this I have a contract which needs to be issue to customer if the
loan has approved which is based on 36 fields which are as under just suggest
me is there any way to split this ?? if i will split this in different tables
I have to setup a mail merge with Word So will i be able to do that??


A mailmerge can easily be based on a Query. You certainly do not need to have
it all in one table!

Customer identification
Customer name Branch
Sales officer
Local purchase order date
Vendor
Vendor address
Brand
Model
Year of Make
Color
Chassis no
Engine no
Original cost
Insurance cost
Total cost Down payment
Total deferred payment
1st installment date
Last installment date
1st installment amount
Last installment amount
Place of registration
Vehicle owner name
Passport no
Debt account
Commission amount in no
Commission amount in words


Each type or "Entity" - real-life person, thing, or event - should have its
own table. A Customer is an entity; an automobile is a different kind of
entity; a payment is yet a third type of entity. I'd suggest at LEAST tables
such as:

Customers
CustomerID autonumber primary key
LastName
FirstName
PassportNo you might be able to make this the primary key if every customer
will have one, but I doubt that is the case

SalesOfficers
OfficerID your company's employee number or an autonumber
LastName
firstName
other biographical or contact data

Vehicles
VehicleID I think the VIN is pretty standard
Make
Model
ModelYear
Color
other vehicle specific fields

Loan
LoanNo primary key, your unique loan number
LoanDate
PrincipalAmount
other loan terms


Fields which can be derived from other fields should not exist in your table,
period - they should instead be calculated on the fly. Amount in words is one
example. If the monthly payment can be calculated from the loan amount,
interest rate and term, then it should be calculated, not stored.


this is my customer table from where i generate contract.
The idea a customer record is entered there should be no redundancy. Please
don't forger that I have to issue contracts by mail merge. Or is there any
way to design report for contracts. it contains 12 pages for one customer.
can we design such a report???


Sure.

--

John W. Vinson [MVP]
  #3  
Old March 12th, 2010, 10:24 AM posted to microsoft.public.access.tablesdbdesign
sys_analyst47 via AccessMonster.com
external usenet poster
 
Posts: 11
Default Normalization

John W. Vinson wrote:
Hi,
I am developing a inter dept application which will help us out to track the

[quoted text clipped - 4 lines]
me is there any way to split this ?? if i will split this in different tables
I have to setup a mail merge with Word So will i be able to do that??


A mailmerge can easily be based on a Query. You certainly do not need to have
it all in one table!

Customer identification
Customer name Branch

[quoted text clipped - 22 lines]
Commission amount in no
Commission amount in words


Each type or "Entity" - real-life person, thing, or event - should have its
own table. A Customer is an entity; an automobile is a different kind of
entity; a payment is yet a third type of entity. I'd suggest at LEAST tables
such as:

Customers
CustomerID autonumber primary key
LastName
FirstName
PassportNo you might be able to make this the primary key if every customer
will have one, but I doubt that is the case

SalesOfficers
OfficerID your company's employee number or an autonumber
LastName
firstName
other biographical or contact data

Vehicles
VehicleID I think the VIN is pretty standard
Make
Model
ModelYear
Color
other vehicle specific fields

Loan
LoanNo primary key, your unique loan number
LoanDate
PrincipalAmount
other loan terms

Fields which can be derived from other fields should not exist in your table,
period - they should instead be calculated on the fly. Amount in words is one
example. If the monthly payment can be calculated from the loan amount,
interest rate and term, then it should be calculated, not stored.

this is my customer table from where i generate contract.
The idea a customer record is entered there should be no redundancy. Please
don't forger that I have to issue contracts by mail merge. Or is there any
way to design report for contracts. it contains 12 pages for one customer.
can we design such a report???


Sure.


Thanks for your quick revert, well may i send you a copy of my database. if i
will keep separate vehicle table, sales table, customer table & Loan amount
table. How a user in one go will input the information .... can you please
suggest me on that. Please provide me your email so that i will send you a
copy of my database have a look into this and suggest me. I would really
appreciate your help

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE

Message posted via http://www.accessmonster.com

  #4  
Old March 12th, 2010, 06:00 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Normalization

On Fri, 12 Mar 2010 09:24:10 GMT, "sys_analyst47 via AccessMonster.com"
u58607@uwe wrote:

Thanks for your quick revert, well may i send you a copy of my database. if i
will keep separate vehicle table, sales table, customer table & Loan amount
table. How a user in one go will input the information .... can you please
suggest me on that. Please provide me your email so that i will send you a
copy of my database have a look into this and suggest me. I would really
appreciate your help


You would use appropriate Forms with Subforms. There are some tutorials and a
good video for how to get started posted below.

I'm sorry, but designing your database for you goes beyond what I'm
comfortable doing as an unpaid volunteer on these newsgroups. I do provide
fee-based consulting services but my time is fully booked up at present. You
may want to see if you can find someone locally or use a jobs forum to request
someone's paid services.


Here are those resources:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


--

John W. Vinson [MVP]
  #5  
Old March 12th, 2010, 07:52 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Normalization

Hello Majid,

I provide help with Access, Excel and Word applications for a small fee. If
you need help with your database, contact me.

Steve



"sys_analyst47 via AccessMonster.com" u58607@uwe wrote in message
news:a4e335f442705@uwe...
Hi,
I am developing a inter dept application which will help us out to track
the
auto loan application receiving, status of application, daily activity of
staff & few more things beside that. The problem which i am facing to
design
tables is this I have a contract which needs to be issue to customer if
the
loan has approved which is based on 36 fields which are as under just
suggest
me is there any way to split this ?? if i will split this in different
tables
I have to setup a mail merge with Word So will i be able to do that??

Customer identification
Customer name Branch
Sales officer
Local purchase order date
Vendor
Vendor address
Brand
Model
Year of Make
Color
Chassis no
Engine no
Original cost
Insurance cost
Total cost Down payment
Total deferred payment
1st installment date
Last installment date
1st installment amount
Last installment amount
Place of registration
Vehicle owner name
Passport no
Debt account
Commission amount in no
Commission amount in words

this is my customer table from where i generate contract.
The idea a customer record is entered there should be no redundancy.
Please
don't forger that I have to issue contracts by mail merge. Or is there any
way to design report for contracts. it contains 12 pages for one customer.
can we design such a report???

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201003/1



  #6  
Old March 12th, 2010, 08:45 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Normalization

Majid

These newsgroups offer FREE assistance and are (usually) "staffed" by
volunteers. The Code of Conduct for these newsgroups prohibits soliciting
paid work.

Before you do business with someone who solicits paid work here, ask
yourself if you are comfortable working with someone who knowingly violates
the rules here.

To make best use of the free assistance here, describe what you have and
what you have already done in more detail.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"sys_analyst47 via AccessMonster.com" u58607@uwe wrote in message
news:a4e335f442705@uwe...
Hi,
I am developing a inter dept application which will help us out to track
the
auto loan application receiving, status of application, daily activity of
staff & few more things beside that. The problem which i am facing to
design
tables is this I have a contract which needs to be issue to customer if
the
loan has approved which is based on 36 fields which are as under just
suggest
me is there any way to split this ?? if i will split this in different
tables
I have to setup a mail merge with Word So will i be able to do that??

Customer identification
Customer name Branch
Sales officer
Local purchase order date
Vendor
Vendor address
Brand
Model
Year of Make
Color
Chassis no
Engine no
Original cost
Insurance cost
Total cost Down payment
Total deferred payment
1st installment date
Last installment date
1st installment amount
Last installment amount
Place of registration
Vehicle owner name
Passport no
Debt account
Commission amount in no
Commission amount in words

this is my customer table from where i generate contract.
The idea a customer record is entered there should be no redundancy.
Please
don't forger that I have to issue contracts by mail merge. Or is there any
way to design report for contracts. it contains 12 pages for one customer.
can we design such a report???

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201003/1



  #7  
Old March 12th, 2010, 08:51 PM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default Normalization

"Steve" wrote in message
...
Hello Majid,

I provide help with Access, Excel and Word applications for a small fee.
If you need help with your database, contact me.

Steve


Stevie - Normalization? We all know that you are not normal!


Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

Please do not feed the trolls.

John... Visio MVP

  #8  
Old March 12th, 2010, 08:53 PM posted to microsoft.public.access.tablesdbdesign
sys_analyst47 via AccessMonster.com
external usenet poster
 
Posts: 11
Default Normalization

Jeff Boyce wrote:
Majid

These newsgroups offer FREE assistance and are (usually) "staffed" by
volunteers. The Code of Conduct for these newsgroups prohibits soliciting
paid work.

Before you do business with someone who solicits paid work here, ask
yourself if you are comfortable working with someone who knowingly violates
the rules here.

To make best use of the free assistance here, describe what you have and
what you have already done in more detail.

Regards

Jeff Boyce
Microsoft Access MVP

Hi,
I am developing a inter dept application which will help us out to track

[quoted text clipped - 43 lines]
way to design report for contracts. it contains 12 pages for one customer.
can we design such a report???


Thank you very much for giving me information on that. Even I was not willing
to pay like this. As all the time everyone is getting assistance from this
forum. Well thanks a lot.

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE

Message posted via http://www.accessmonster.com

  #9  
Old March 13th, 2010, 04:18 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Normalization

On Fri, 12 Mar 2010 19:53:36 GMT, "sys_analyst47 via AccessMonster.com"
u58607@uwe wrote:

Thank you very much for giving me information on that. Even I was not willing
to pay like this. As all the time everyone is getting assistance from this
forum. Well thanks a lot.


I think there may be a language barrier both ways here, Majid; I can't tell if
your "thanks a lot" is sincere appreciation or sarcastic. Do you still need
help (other than someone downloading your database and fixing it for you,
which again would not generally be a free service)?
--

John W. Vinson [MVP]
  #10  
Old March 13th, 2010, 03:29 PM posted to microsoft.public.access.tablesdbdesign
sys_analyst47 via AccessMonster.com
external usenet poster
 
Posts: 11
Default Normalization

John W. Vinson wrote:
Thank you very much for giving me information on that. Even I was not willing
to pay like this. As all the time everyone is getting assistance from this
forum. Well thanks a lot.


I think there may be a language barrier both ways here, Majid; I can't tell if
your "thanks a lot" is sincere appreciation or sarcastic. Do you still need
help (other than someone downloading your database and fixing it for you,
which again would not generally be a free service)?


Well i fix it by the way ... tell me one thing is it possible that i have a
combo box i will put assume a customer ID in it and this will fetch product
sold from another table ...if so please let me know exactly how to do tht

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201003/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


All times are GMT +1. The time now is 04:30 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.