enterabel form (data going into multiple tables)
in [General]
|
From: George Hepworth on 10 Mar 2010 11:35 There are two tables, not "multiple tables"? Then, the classic main form subform design is very much appropriate for your situation. However, let's just be absolutely clear. For each Problem, there is ONE and ONLY ONE possible result? If so, then you do indeed have a one-to-one relationship. Let's assume that's valid. However, one of your statements raises another issue that we need to clarify first. "...however both fields have a primary Key field called ID (one to one relationship)..." I assume you meant to write that "..however both [tables] have a primary Key field called ID (one to one relationship)..." and that you have defined your relationship between Problems and Results on those fields. Is that correct? George "Graeme" <user(a)msgroups.net/> wrote in message news:u4VQcPGwKHA.3764(a)TK2MSFTNGP04.phx.gbl... > Hello > To answer the above question my tables are Problem and Results. I would > like to have one form, that would combine the fields from these tables. > They are linked by an autonumber field called ID in the Problem table and > a number field called ID in the Result field. What I would like to happen > is every time autnoumber ID field increases the corresponding ID field for > results also increases. I am infact trying to throw all the fields in one > form. Could you tell me if this is a good Idea or if I should, hault what > i am doing and find a different approach. I do not understand the purpose > of subforms, however both fields have a primary Key field called ID (one > to one relationship), so the one-to-many relationship you had discussed > may not be valid. > Regards > Graeme > > --- > frmsrcurl: > http://msgroups.net/microsoft.public.access/enterabel-form-data-going-into-multiple-tables
From: Graeme on 10 Mar 2010 12:12 Hello George You are correct, both tables have a one to one relationship, and that relationship is do to the primary key field ID in both tables. Regards Graeme --- frmsrcurl: http://msgroups.net/microsoft.public.access/enterabel-form-data-going-into-multiple-tables
From: George Hepworth on 10 Mar 2010 12:48 Well, that's not how I would do it, but you can make it work if you are willing to tolerate the restrictions. First, it is critical that the Primary Key field in the Results table is defined as a Number of Long Integer datatype, not an Autonumber. Second, the Primary Key field in the Problem table needs to be the Autonumber. Third, you'll need to add values for all of the fields in the Problem table first. Typically, you'd want to do this with a main form/sub form design so that the record in the Problem table is entered and saved first. George PS: Just a comment on terminology and the apparent logic behind it. "that relationship is do to the primary key field ID " is not factually accurate. Relationships between things (which we call entities in the world of database design) exist with or without our assistance or even our knowledge in many cases. If you have a problem, you may or may not have a "result", but that relationship between problem and result does not depend on whether you track it in a database. In fact, when you create Primary and Foreign Key fields in tables in a relational database and define the relationship between them by dragging the line in the relationship window, what you are doing is simply making that relationship EXPLICIT to the database. That's all; you are, in a sense, "telling" Access about that relationship. So, the reality is that the relationship is NOT due to the Primary Key fields. The Primary Key fields (and the relationship you have defined between them) are due to the need for your database to track the existing relationship. "Graeme" <user(a)msgroups.net/> wrote in message news:eXlbOTHwKHA.2436(a)TK2MSFTNGP04.phx.gbl... > Hello George > > You are correct, both tables have a one to one relationship, and that > relationship is do to the primary key field ID in both tables. > Regards > Graeme > > --- > frmsrcurl: > http://msgroups.net/microsoft.public.access/enterabel-form-data-going-into-multiple-tables
From: Graeme on 10 Mar 2010 13:16 Hello George You said above, "thats not how I would do it". So I guess the question is how would you do it. How can I make this database, stronger and less restrictive. Regards Graeme --- frmsrcurl: http://msgroups.net/microsoft.public.access/enterabel-form-data-going-into-multiple-tables
From: George Hepworth on 10 Mar 2010 13:51
I would use the main form/subform design. "Graeme" <user(a)msgroups.net/> wrote in message news:ul0yf3HwKHA.1796(a)TK2MSFTNGP02.phx.gbl... > Hello George > > You said above, "thats not how I would do it". So I guess the question is > how would you do it. How can I make this database, stronger and less > restrictive. > > Regards > Graeme > > --- > frmsrcurl: > http://msgroups.net/microsoft.public.access/enterabel-form-data-going-into-multiple-tables |