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
  #11  
Old March 13th, 2010, 05:33 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Normalization

On Sat, 13 Mar 2010 14:29:28 GMT, "sys_analyst47 via AccessMonster.com"
u58607@uwe wrote:

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


Yes it is possible. However you will need to help me: I do not know the
structure of your tables, and I do not know where the product sold is to be
found.

Please post a description of your tables in the form

Customers
CustomerID
LastName
FirstName
...

OtherTable
Thisfield
Thatfield
...

and indicate how the tables are related.
--

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

John W. Vinson wrote:
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


Yes it is possible. However you will need to help me: I do not know the
structure of your tables, and I do not know where the product sold is to be
found.

Please post a description of your tables in the form

Customers
CustomerID
LastName
FirstName
...

OtherTable
Thisfield
Thatfield
...

and indicate how the tables are related.



Customer identification - Unique no assigned to every customer
Customer name Branch
Sales officer
Product
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

based on the above mentioned fields my contract is generating. Now i have
another table Named as discrepancy the reason for that table is if there is
any error in customer finance application form i will have to return that
particular application. Fields of discrepancy are as under:

Customer No (FK)
Customer Name
Product
Branch
date of discrepancy - Will be same as Local purchase order date
Discrepancy reason

Now, if i will put the Customer Number i will get the customer name, product,
branch, date of local purchase order.
Tell me what will be the control for Customer number (Combo box or text box)
& similarly what will be the control for the rest of the fields. and which
property do i need to set

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

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

On Sat, 13 Mar 2010 17:30:23 GMT, "sys_analyst47 via AccessMonster.com"
u58607@uwe wrote:

John W. Vinson wrote:
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


Yes it is possible. However you will need to help me: I do not know the
structure of your tables, and I do not know where the product sold is to be
found.

Please post a description of your tables in the form

Customers
CustomerID
LastName
FirstName
...

OtherTable
Thisfield
Thatfield
...

and indicate how the tables are related.



Customer identification - Unique no assigned to every customer
Customer name Branch
Sales officer
Product
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



Ok. So you completely ignored or dismissed the earlier advice about how to
normalize your table? That's why you're having trouble!

A customer *does not have a chassis number*.
A customer *does not have an installment date*.
A customer *does not have a commission amount*.

Or do you want to limit your database so that each customer can own one and
only one vehicle, and have one and only one insurance policy?

based on the above mentioned fields my contract is generating. Now i have
another table Named as discrepancy the reason for that table is if there is
any error in customer finance application form i will have to return that
particular application. Fields of discrepancy are as under:

Customer No (FK)
Customer Name
Product
Branch
date of discrepancy - Will be same as Local purchase order date
Discrepancy reason

Now, if i will put the Customer Number i will get the customer name, product,
branch, date of local purchase order.
Tell me what will be the control for Customer number (Combo box or text box)
& similarly what will be the control for the rest of the fields. and which
property do i need to set


It sounds like you want to COPY the customer name, product, and so on from the
(non-normalized) Customer table into the Discrepancy table.

Don't.

That's not how relational databases work! You should store customer specific
information - *once and once only* - in the Customer table, and noplace else.
The discrepancy table will have a CustomerNo to LINK it to the Customer table,
but it will not contain any other fields from that table.

You can enter data into the table by using a Form based on the Customer table,
with a Subform based on the Discrepancy table, using Customer No as the
master/child link field. On the subform you will have fields such as the date
of discrepancy, reason, resolution etc.

--

John W. Vinson [MVP]
  #14  
Old March 13th, 2010, 09:21 PM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Normalization

John W. Vinson wrote in
:

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


Yes, true. But if your query has any user-defined functions or uses
any VBA functions that are not supported by the Jet/ACE expression
service, it won't work. I almost always use a temp table for writing
data that is exported or used for mail merge.

And I'd also recommend Albert Kallal's Word Merge utility, which
I've used quite successfully in one of my apps:

http://www.members.shaw.ca/AlbertKal.../msaccess.html

(search for Super Easy Word Merge in the page)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #15  
Old March 14th, 2010, 07:21 AM posted to microsoft.public.access.tablesdbdesign
sys_analyst47 via AccessMonster.com
external usenet poster
 
Posts: 11
Default Normalization

John W. Vinson wrote:
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

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


Ok. So you completely ignored or dismissed the earlier advice about how to
normalize your table? That's why you're having trouble!

A customer *does not have a chassis number*.


A customer *does not have a commission amount*.

Or do you want to limit your database so that each customer can own one and
only one vehicle, and have one and only one insurance policy?

based on the above mentioned fields my contract is generating. Now i have
another table Named as discrepancy the reason for that table is if there is

[quoted text clipped - 13 lines]
& similarly what will be the control for the rest of the fields. and which
property do i need to set


It sounds like you want to COPY the customer name, product, and so on from the
(non-normalized) Customer table into the Discrepancy table.

Don't.

That's not how relational databases work! You should store customer specific
information - *once and once only* - in the Customer table, and noplace else.
The discrepancy table will have a CustomerNo to LINK it to the Customer table,
but it will not contain any other fields from that table.

You can enter data into the table by using a Form based on the Customer table,
with a Subform based on the Discrepancy table, using Customer No as the
master/child link field. On the subform you will have fields such as the date
of discrepancy, reason, resolution etc.


So you want me to break the customer info, like Customer no, Name & Passport
number, and what about the vehicle information then ???? If i split it like
this how i will generate my contract which is based on mail merge & in mail
merge i think we can only take one table at one time.. Please suggest me if i
will generate a query based on the suppose non normalized customer table &
just fetch customer no,name, date based on that i make a table & then a form
for discrepancy and here i will just add one field for discrepancy. I know
its just a way out ...

And if i split the customer info as mentioned above and vehicle info separate
?? the problem which i am looking is that how I will generate contract which
is purely replying on the above non normalized data ... or either i will make
a query which will take info from 2 - 3 tables and will create a table and
basis on that table i will generate the contract .. in addition to this a
user who is going to put information for the contract i will have to make a
sub form ???? means i dun want to make it complicated .. and I asked for the
wayout for searching like user put customer no in suppose combo box and the
information from other table will fetch in text box.

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

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

  #16  
Old March 14th, 2010, 08:17 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Normalization

On Sun, 14 Mar 2010 06:21:40 GMT, "sys_analyst47 via AccessMonster.com"
u58607@uwe wrote:


So you want me to break the customer info, like Customer no, Name & Passport
number, and what about the vehicle information then ????


In a Vehicle table.

If i split it like
this how i will generate my contract which is based on mail merge & in mail
merge i think we can only take one table at one time..


You are mistaken.

You CAN base a mailmerge on a query.

As David Fenton pointed out in another thread, the query may not work if it
contains Access or VBA functions... but a simple join query *DOES INDEED* work
as the source of a mailmerge.

Even if it doesn't (for performance, or because you do need such a function),
you can maintain your data better if the tables are normalized. You can use a
(non-normalized) table as the source for the merge; empty it with a Delete
query and populate it with multitable data from your normalized queries with
an Append query.

Please suggest me if i
will generate a query based on the suppose non normalized customer table &
just fetch customer no,name, date based on that i make a table & then a form
for discrepancy and here i will just add one field for discrepancy. I know
its just a way out ...


I do not understand your question.

And if i split the customer info as mentioned above and vehicle info separate
?? the problem which i am looking is that how I will generate contract which
is purely replying on the above non normalized data ... or either i will make
a query which will take info from 2 - 3 tables and will create a table and
basis on that table i will generate the contract .. in addition to this a
user who is going to put information for the contract i will have to make a
sub form ???? means i dun want to make it complicated .. and I asked for the
wayout for searching like user put customer no in suppose combo box and the
information from other table will fetch in text box.


Don't confuse DATA STORAGE - in tables, normalized - with DATA DISPLAY - a
Report, a printed contract, which can be an Access Report, a MailMerge from a
query, perhaps a MailMerge from a temporary table, perhaps even generated
using VBA and Word automation. Unless your contracts are one-time-only
printouts and you never need to deal with repeat customers or other repeating
data, your maintenance will be much easier with normalized tables.

--

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

John W. Vinson wrote:
So you want me to break the customer info, like Customer no, Name & Passport
number, and what about the vehicle information then ????


In a Vehicle table.

If i split it like
this how i will generate my contract which is based on mail merge & in mail
merge i think we can only take one table at one time..


You are mistaken.

You CAN base a mailmerge on a query.

As David Fenton pointed out in another thread, the query may not work if it
contains Access or VBA functions... but a simple join query *DOES INDEED* work
as the source of a mailmerge.

Even if it doesn't (for performance, or because you do need such a function),
you can maintain your data better if the tables are normalized. You can use a
(non-normalized) table as the source for the merge; empty it with a Delete
query and populate it with multitable data from your normalized queries with
an Append query.

Please suggest me if i
will generate a query based on the suppose non normalized customer table &
just fetch customer no,name, date based on that i make a table & then a form
for discrepancy and here i will just add one field for discrepancy. I know
its just a way out ...


I do not understand your question.

And if i split the customer info as mentioned above and vehicle info separate
?? the problem which i am looking is that how I will generate contract which

[quoted text clipped - 5 lines]
wayout for searching like user put customer no in suppose combo box and the
information from other table will fetch in text box.


Don't confuse DATA STORAGE - in tables, normalized - with DATA DISPLAY - a
Report, a printed contract, which can be an Access Report, a MailMerge from a
query, perhaps a MailMerge from a temporary table, perhaps even generated
using VBA and Word automation. Unless your contracts are one-time-only
printouts and you never need to deal with repeat customers or other repeating
data, your maintenance will be much easier with normalized tables.


Well i normalized the data & here are the details
customer:
customer number
cm name
installment amount
installment date

Vehicle:
customer number
Purchase order date
brand
model
year of make
color
chassis no
Insurance amount
total cost
profit amt
selling price
downpayment
deferred selling price
registration

and after that i create a subform. Correct me if i am wrong i will create a
query which will generate a table on basis of that i will prepare my contract.
Please tell me how can i make my contract by report because for one customer
there are 12 pages of contract how can i design 12 continuous pages in access
? is it possible

And just tell me one simple thing .. for amount i kept data type number & in
general i mentioned decimal with 2 digit after point but again in form when i
put a value it's getting round off ?? how can i set this

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

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

John W. Vinson wrote:
So you want me to break the customer info, like Customer no, Name & Passport
number, and what about the vehicle information then ????


In a Vehicle table.

If i split it like
this how i will generate my contract which is based on mail merge & in mail
merge i think we can only take one table at one time..


You are mistaken.

You CAN base a mailmerge on a query.

As David Fenton pointed out in another thread, the query may not work if it
contains Access or VBA functions... but a simple join query *DOES INDEED* work
as the source of a mailmerge.

Even if it doesn't (for performance, or because you do need such a function),
you can maintain your data better if the tables are normalized. You can use a
(non-normalized) table as the source for the merge; empty it with a Delete
query and populate it with multitable data from your normalized queries with
an Append query.

Please suggest me if i
will generate a query based on the suppose non normalized customer table &
just fetch customer no,name, date based on that i make a table & then a form
for discrepancy and here i will just add one field for discrepancy. I know
its just a way out ...


I do not understand your question.

And if i split the customer info as mentioned above and vehicle info separate
?? the problem which i am looking is that how I will generate contract which

[quoted text clipped - 5 lines]
wayout for searching like user put customer no in suppose combo box and the
information from other table will fetch in text box.


Don't confuse DATA STORAGE - in tables, normalized - with DATA DISPLAY - a
Report, a printed contract, which can be an Access Report, a MailMerge from a
query, perhaps a MailMerge from a temporary table, perhaps even generated
using VBA and Word automation. Unless your contracts are one-time-only
printouts and you never need to deal with repeat customers or other repeating
data, your maintenance will be much easier with normalized tables.


Well i normalized the data & here are the details
customer:
customer number
cm name
installment amount
installment date

Vehicle:
customer number
Purchase order date
brand
model
year of make
color
chassis no
Insurance amount
total cost
profit amt
selling price
downpayment
deferred selling price
registration

and after that i create a subform. Correct me if i am wrong i will create a
query which will generate a table on basis of that i will prepare my contract.
Please tell me how can i make my contract by report because for one customer
there are 12 pages of contract how can i design 12 continuous pages in access
? is it possible

And just tell me one simple thing .. for amount i kept data type number & in
general i mentioned decimal with 2 digit after point but again in form when i
put a value it's getting round off ?? how can i set this

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

  #19  
Old March 14th, 2010, 04:55 PM posted to microsoft.public.access.tablesdbdesign
sys_analyst47 via AccessMonster.com
external usenet poster
 
Posts: 11
Default Normalization

John W. Vinson wrote:
So you want me to break the customer info, like Customer no, Name & Passport
number, and what about the vehicle information then ????


In a Vehicle table.

If i split it like
this how i will generate my contract which is based on mail merge & in mail
merge i think we can only take one table at one time..


You are mistaken.

You CAN base a mailmerge on a query.

As David Fenton pointed out in another thread, the query may not work if it
contains Access or VBA functions... but a simple join query *DOES INDEED* work
as the source of a mailmerge.

Even if it doesn't (for performance, or because you do need such a function),
you can maintain your data better if the tables are normalized. You can use a
(non-normalized) table as the source for the merge; empty it with a Delete
query and populate it with multitable data from your normalized queries with
an Append query.

Please suggest me if i
will generate a query based on the suppose non normalized customer table &
just fetch customer no,name, date based on that i make a table & then a form
for discrepancy and here i will just add one field for discrepancy. I know
its just a way out ...


I do not understand your question.

And if i split the customer info as mentioned above and vehicle info separate
?? the problem which i am looking is that how I will generate contract which

[quoted text clipped - 5 lines]
wayout for searching like user put customer no in suppose combo box and the
information from other table will fetch in text box.


Don't confuse DATA STORAGE - in tables, normalized - with DATA DISPLAY - a
Report, a printed contract, which can be an Access Report, a MailMerge from a
query, perhaps a MailMerge from a temporary table, perhaps even generated
using VBA and Word automation. Unless your contracts are one-time-only
printouts and you never need to deal with repeat customers or other repeating
data, your maintenance will be much easier with normalized tables.


Well i tried to normalized the data & here are the details
customer:
customer number
cm name
installment amount
installment date

Vehicle:
customer number
Purchase order date
brand
model
year of make
color
chassis no
Insurance amount
total cost
profit amt
selling price
downpayment
deferred selling price
registration

and after that i create a subform. Correct me if i am wrong i will create a
query which will generate a table on basis of that i will prepare my contract.
Please tell me how can i make my contract by report because for one customer
there are 12 pages of contract how can i design 12 continuous pages in access
? is it possible

And just tell me one simple thing .. for amount i kept data type number & in
general i mentioned decimal with 2 digit after point but again in form when i
put a value it's getting round off ?? how can i set this

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

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

On Sun, 14 Mar 2010 15:52:22 GMT, "sys_analyst47 via AccessMonster.com"
u58607@uwe wrote:

and after that i create a subform. Correct me if i am wrong i will create a
query which will generate a table on basis of that i will prepare my contract.
Please tell me how can i make my contract by report because for one customer
there are 12 pages of contract how can i design 12 continuous pages in access
? is it possible


Since I have no idea what's on your contract, all I can say is I Don't Know.
You can put "page break" controls on a Report, so you could put some fields on
the report, a page break, some other controls, and so on. You might have some
complexity because a report is limited to 22" total height, however you can
use a report with multiple subreports to get around this limit.


And just tell me one simple thing .. for amount i kept data type number & in
general i mentioned decimal with 2 digit after point but again in form when i
put a value it's getting round off ?? how can i set this


The default Number size is "Long Integer". Select this field in table design
view and look at the Properties in the lower left. A Long Integer is, by
definition, a whole number.

If the field represents money, don't use a Number datatype at all, use
Currency (oddly, it's a datatype of its own, not a special case of Number).
That will give you four decimals. Currency can be used for any type of number,
not just money values, if four decimal places are appropriate; otherwise you
can use Single (floating point, about 7 digits precision), Double (floating
point, about 14 digits), or Decimal (you pick the scale and precision).

--

John W. Vinson [MVP]
 




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 09:52 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.