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 » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

relationship & Input for 5 tables in a single Form impossible



 
 
Thread Tools Display Modes
  #1  
Old February 25th, 2005, 03:35 PM
Rasoul Khoshravan Azar
external usenet poster
 
Posts: n/a
Default 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  
Old February 25th, 2005, 06:57 PM
John Vinson
external usenet poster
 
Posts: n/a
Default

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  
Old February 26th, 2005, 11:20 PM
Rasoul Khoshravan Azar
external usenet poster
 
Posts: n/a
Default

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

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

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


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