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
|
|||
|
|||
relationship & Input for 5 tables in a single Form impossible
For some reasons (should be discussed later) I have split out my database to
5 tables all haveing a common field (OrderID) which is a primary key to all. I would like to enter data for all five tables in a single Form (I made a query and collect all fields from five tables and based the form on this query), named "InoutFrm" but when I try to input data in "InputFrm" the following error message appears: You cannot add or change a record because a related record is required in table 'C&FTable' 1- Isn't it a good idea to enter all required data in a single Form? I checked Northwind sample database and it seams that it has created single entry form for each table. Is it a rule or just it happened to be so. 2- Why split to five tables. I am trying to input a physical form which means that one table should be enough (For a single order all boxes should be filled out) but as the numebr of fields is about 80, and they are categorized under 5 different category, I thought makeing five table and relating them by a single primary key should do the job. Isn't it a good idea to do so? Any comment on the structure of the DB and above question (input in a single from) is highly appreciated. Rasoul Khoshravan Azar Tabriz, Iran |
#2
|
|||
|
|||
On Fri, 25 Feb 2005 18:05:41 +0330, "Rasoul Khoshravan Azar"
wrote: For some reasons (should be discussed later) I have split out my database to 5 tables all haveing a common field (OrderID) which is a primary key to all. This is a VERY unusual approach, and suggests that your tables are not correctly structured. One to one relationships are VERY rare. If you're not doing "Subclassing", or splitting tables for security reasons, one-to-one relationships are probably not appropriate. I would like to enter data for all five tables in a single Form (I made a query and collect all fields from five tables and based the form on this query), named "InoutFrm" but when I try to input data in "InputFrm" the following error message appears: You cannot add or change a record because a related record is required in table 'C&FTable' 1- Isn't it a good idea to enter all required data in a single Form? I checked Northwind sample database and it seams that it has created single entry form for each table. Is it a rule or just it happened to be so. Look at the form again. It uses Subforms for one-to-many related tables, not a single massive query. 2- Why split to five tables. Good question. You should have a Table for each "entity" - a real-life person, thing, or event. Entities are typically related either one-to-many or many-to-many - for example, the Northwind Orders database has Orders and Products, in a many to many relationship (each Order can be for multiple products, and each product can be a part of many Orders); the relationship is mediated by the OrderDetails table, which is related one to many to both Orders and Products. I am trying to input a physical form which means that one table should be enough (For a single order all boxes should be filled out) but as the numebr of fields is about 80, and they are categorized under 5 different category, I thought makeing five table and relating them by a single primary key should do the job. Isn't it a good idea to do so? Almost certainly, no, it is not. Without knowing what "boxes" you are filling out it's hard to say how you should be doing things differently - but I suspect that you should. A common mistake is to store data (products, perhaps?) in fieldnames, so that your form would have a textbox for the number of Widgets, another textbox for the number of Grommets, and a third textbox for the number of Gizmos. This approach IS SIMPLY WRONG and will get you into no end of trouble! Any comment on the structure of the DB and above question (input in a single from) is highly appreciated. Stop worrying about the Form until you have the proper table structures and relationships. You'll almost certainly need a Form with one or more Subforms rather than a single massive form/query, but it's probable that your table structure will need to be modified. John W. Vinson[MVP] |
#3
|
|||
|
|||
Dear John Vinson
Thanks for your comments. What I want to do is to make a database of ongoing transactions in the company I am working for to trace them. Actually the company doesn't need to do any calculation or execution on the data at this momnet but only to keep them . My present DB structure is as follows: MainTbl OrderID (Primary Key, Autonumber) Producer Name Producer contact address L/C openning bank name Bank Branch Bank Address Buyer Buyer Adress and some other fields like Tel, Fax Email of buyer CFTbl (Cost & Freight info of transaction) OrderID (Primary Key, Autonumber) Transportation Proforma Invoice Number P/I expiry date .... TransportTbl (Transporting info of transaction) OrderID (Primary Key, Autonumber) Transporter Company Name Transporter Address Transpoter Tel Number of shipment Custom name Entrance port name Destination name Type of packing .... ProformaTbl (Proforma Invoice info of transaction) OrderID (Primary Key, Autonumber) P/I number P/I issue date Type of transaction Period of finance rate of finance CommodityRegistoryTbl (Commodity registration info of transaction) Seller Seller Address Country of Origin Commodity Code Commodity Price .... Now that I rewrite the DB to newsgroup, comparing to what you wrote to me as the meaning of table (a real identity outside in the world), I guess I have to reorganize my DB, maybe as follows. (The one I have made is too much complicated with many repetetive fields in different tables with no logical reasoning for collecting fields in one table). ProducerTbl ProducerID (Primary KEY, Autonumber) and related fields like address, tel, etc BankTbl BankID (Primary KEY, Autonumber) and related fields like address, tel, etc BuyerTbl BuyerID (Primary KEY, Autonumber) and related fields like address, tel, etc TransporterTbl TranspoterID (Primary KEY, Autonumber) and related fields like address, tel, etc CommodityTbl CommodityID (Primary KEY, Autonumber) TransactionDetailTbl (instead of ProformaTbl in old design DB) CommodityID (Forigen Key) TranspoterID (Forigen Key) BuyerID (Forigen Key) ProducerID (Forigen Key) P/I Number P/I expirey date Finance period Finance rate Relationships TransactionDetailTbl will have many-to-one relation with other tables (CommodityTbl, TranspoterTbl, BuyerTbl, ProducerTbl). Looks little complicated for me at first glance. I think I need to think more about my Database and it is not as easy as I thought at first step. What the company asks me at this point is to print them neatly in a one A4 page. If you have any idea in the overall design, I will be very happy to hear it. For details which I think I will need more time to digest and slove, I may ask in comming separate mails. Very Sincerely Yours Rasoul Khoshravan Tabriz, Iran |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Input form from three other tables | [email protected] | Using Forms | 0 | November 8th, 2004 08:45 PM |
Strange stLinkCriteria behaviour on command button | Anthony Dowd | Using Forms | 3 | August 21st, 2004 03:01 AM |
2 tables, sub form and confusion, help please | Matt P. | Running & Setting Up Queries | 1 | August 10th, 2004 09:46 PM |
Fields from multiple Tables on 1 Form | Alan Armitage | Using Forms | 2 | July 15th, 2004 11:13 AM |
Recordset in subform based on field in parent form | Lyn | General Discussion | 15 | June 14th, 2004 03:10 PM |