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