relationship & Input for 5 tables in a single Form impossible

  • Thread starter Thread starter Rasoul Khoshravan Azar
  • Start date Start date
R

Rasoul Khoshravan Azar

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
 
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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?
Click to expand...

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.
Click to expand...

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]
 
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
 
Back
Top