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
|
|||
|
|||
showing details of a many to one relationship in a form.
Hi,
I'm wondering if someone can help? I am stuck on this?? I have created two forms who's tables are joined by primary/forigen keys. Table\Form #1 - Original Software Table\Form # 2 - Upgraded. Form # 1 - Has a bunch of records. Each record can have many upgrades. Form # 2 - immedialtely asks u to choose a original software. It is a required field. Form 1 has a upgraded Id field (the upgrade tables primary key, referential integrity enforced) . Is there anyway to show? when you choose an upgrade in form # 2 the autonumber Id from the upgrade form appears in the original softaware form in the upgrade ID field. There may be more than one Id as it can have many upgrades. My goal is if I open a original software record I will see all the associated upgrades. Is this possible and how would I go about doing this? |
#2
|
|||
|
|||
Hi Cindy,
You can set Form2 as a subform to Form1 to do this - make sure the master/child links are set to the primary/foreign keys (the wizard will walk you through this part) -- hth, SusanV "Cindy" wrote in message ... Hi, I'm wondering if someone can help? I am stuck on this?? I have created two forms who's tables are joined by primary/forigen keys. Table\Form #1 - Original Software Table\Form # 2 - Upgraded. Form # 1 - Has a bunch of records. Each record can have many upgrades. Form # 2 - immedialtely asks u to choose a original software. It is a required field. Form 1 has a upgraded Id field (the upgrade tables primary key, referential integrity enforced) . Is there anyway to show? when you choose an upgrade in form # 2 the autonumber Id from the upgrade form appears in the original softaware form in the upgrade ID field. There may be more than one Id as it can have many upgrades. My goal is if I open a original software record I will see all the associated upgrades. Is this possible and how would I go about doing this? |
#3
|
|||
|
|||
Hi Susan,
thanks for the reply. I already have a subform in Form # 1. Is there any other way to do this. I don't want a the whole form displayed in the original form, only the migration id output as such Ex: Original Id: 1 Migration: 1, 6, 7 "SusanV" wrote: Hi Cindy, You can set Form2 as a subform to Form1 to do this - make sure the master/child links are set to the primary/foreign keys (the wizard will walk you through this part) -- hth, SusanV "Cindy" wrote in message ... Hi, I'm wondering if someone can help? I am stuck on this?? I have created two forms who's tables are joined by primary/forigen keys. Table\Form #1 - Original Software Table\Form # 2 - Upgraded. Form # 1 - Has a bunch of records. Each record can have many upgrades. Form # 2 - immedialtely asks u to choose a original software. It is a required field. Form 1 has a upgraded Id field (the upgrade tables primary key, referential integrity enforced) . Is there anyway to show? when you choose an upgrade in form # 2 the autonumber Id from the upgrade form appears in the original softaware form in the upgrade ID field. There may be more than one Id as it can have many upgrades. My goal is if I open a original software record I will see all the associated upgrades. Is this possible and how would I go about doing this? |
#4
|
|||
|
|||
Sorry, I misunderstood. If you only want the one field from Table2 included
in Form1, probably the simplest way would be to create a new little subform with just the related field and the one you wish to view (set the related field to hidden) and embed this new subform in Form1. "Cindy" wrote in message ... Hi Susan, thanks for the reply. I already have a subform in Form # 1. Is there any other way to do this. I don't want a the whole form displayed in the original form, only the migration id output as such Ex: Original Id: 1 Migration: 1, 6, 7 "SusanV" wrote: Hi Cindy, You can set Form2 as a subform to Form1 to do this - make sure the master/child links are set to the primary/foreign keys (the wizard will walk you through this part) -- hth, SusanV "Cindy" wrote in message ... Hi, I'm wondering if someone can help? I am stuck on this?? I have created two forms who's tables are joined by primary/forigen keys. Table\Form #1 - Original Software Table\Form # 2 - Upgraded. Form # 1 - Has a bunch of records. Each record can have many upgrades. Form # 2 - immedialtely asks u to choose a original software. It is a required field. Form 1 has a upgraded Id field (the upgrade tables primary key, referential integrity enforced) . Is there anyway to show? when you choose an upgrade in form # 2 the autonumber Id from the upgrade form appears in the original softaware form in the upgrade ID field. There may be more than one Id as it can have many upgrades. My goal is if I open a original software record I will see all the associated upgrades. Is this possible and how would I go about doing this? |
#5
|
|||
|
|||
So do I just create a form and then in Form # 1 using the wizard add it in
as a subform? The field I want is called Migration_ID and it is in both tables/forms 1,2. Just wondering how I would create the subform? Do I need both tables and have both fields in the subform. What do you mean setting the related field to hidden? "SusanV" wrote: Sorry, I misunderstood. If you only want the one field from Table2 included in Form1, probably the simplest way would be to create a new little subform with just the related field and the one you wish to view (set the related field to hidden) and embed this new subform in Form1. "Cindy" wrote in message ... Hi Susan, thanks for the reply. I already have a subform in Form # 1. Is there any other way to do this. I don't want a the whole form displayed in the original form, only the migration id output as such Ex: Original Id: 1 Migration: 1, 6, 7 "SusanV" wrote: Hi Cindy, You can set Form2 as a subform to Form1 to do this - make sure the master/child links are set to the primary/foreign keys (the wizard will walk you through this part) -- hth, SusanV "Cindy" wrote in message ... Hi, I'm wondering if someone can help? I am stuck on this?? I have created two forms who's tables are joined by primary/forigen keys. Table\Form #1 - Original Software Table\Form # 2 - Upgraded. Form # 1 - Has a bunch of records. Each record can have many upgrades. Form # 2 - immedialtely asks u to choose a original software. It is a required field. Form 1 has a upgraded Id field (the upgrade tables primary key, referential integrity enforced) . Is there anyway to show? when you choose an upgrade in form # 2 the autonumber Id from the upgrade form appears in the original softaware form in the upgrade ID field. There may be more than one Id as it can have many upgrades. My goal is if I open a original software record I will see all the associated upgrades. Is this possible and how would I go about doing this? |
#6
|
|||
|
|||
Is the "migration_ID" data duplicated between tables? If so, how is this
controlled? It really should only be in one table... normalization and all that. How it the field setup? Do you have it as, say "1, 5, 9"? For multiple items like this you really should have 3 separate records in a related table - for instance Table1 fldRecordID (pk) (...other fields) Table3 (for migration id records) fldRecID (foreign key to Table1.fldFRecordID) fldMigrationNumber With a one-to-many relationship (one in Table1, Many in Table3) Does this make sense? Now you can use a subform based on Table3 embedded in Form1, listing the migration numbers, and have a good basis for searching what records have MigrationID 1, or 5, or both or whatever... The subform wizard will pretty much do all the work for you as far as creating the master/child links. Sorry if I'm not clear! Susan "Cindy" wrote in message ... So do I just create a form and then in Form # 1 using the wizard add it in as a subform? The field I want is called Migration_ID and it is in both tables/forms 1,2. Just wondering how I would create the subform? Do I need both tables and have both fields in the subform. What do you mean setting the related field to hidden? "SusanV" wrote: Sorry, I misunderstood. If you only want the one field from Table2 included in Form1, probably the simplest way would be to create a new little subform with just the related field and the one you wish to view (set the related field to hidden) and embed this new subform in Form1. "Cindy" wrote in message ... Hi Susan, thanks for the reply. I already have a subform in Form # 1. Is there any other way to do this. I don't want a the whole form displayed in the original form, only the migration id output as such Ex: Original Id: 1 Migration: 1, 6, 7 "SusanV" wrote: Hi Cindy, You can set Form2 as a subform to Form1 to do this - make sure the master/child links are set to the primary/foreign keys (the wizard will walk you through this part) -- hth, SusanV "Cindy" wrote in message ... Hi, I'm wondering if someone can help? I am stuck on this?? I have created two forms who's tables are joined by primary/forigen keys. Table\Form #1 - Original Software Table\Form # 2 - Upgraded. Form # 1 - Has a bunch of records. Each record can have many upgrades. Form # 2 - immedialtely asks u to choose a original software. It is a required field. Form 1 has a upgraded Id field (the upgrade tables primary key, referential integrity enforced) . Is there anyway to show? when you choose an upgrade in form # 2 the autonumber Id from the upgrade form appears in the original softaware form in the upgrade ID field. There may be more than one Id as it can have many upgrades. My goal is if I open a original software record I will see all the associated upgrades. Is this possible and how would I go about doing this? |
#7
|
|||
|
|||
Hi Susan, maybe its me that not being clear. sorry for the confusion.
I have 2 tables, one called Original Software and the other Migration Original_Software Software_ID Migration_ID Software_Title etc.. Migration: Migration_ID Software_ID (drop down list from original software) (required field etc. Then I created 2 forms with the same names. So everytime you upgrade(migrate) you must choose(tie it to) a original software. So One piece of software can have many migration_ID's. In the relationships view I have linked Migration_ID using referential integrity. Ex (migration form) Migration ID: 1 Software_ID: 2 Migration_ID: 2 Software_ID: 2 What I want is when I open the Original Software form there is a field displayed showing all the migration id # associated with the software. Ex: Software_ID#2 Migration _ID# 1, 2 Now what would be my child/master fields? Do I just create the subform using the wizard inside the Original_Software form. "SusanV" wrote: Is the "migration_ID" data duplicated between tables? If so, how is this controlled? It really should only be in one table... normalization and all that. How it the field setup? Do you have it as, say "1, 5, 9"? For multiple items like this you really should have 3 separate records in a related table - for instance Table1 fldRecordID (pk) (...other fields) Table3 (for migration id records) fldRecID (foreign key to Table1.fldFRecordID) fldMigrationNumber With a one-to-many relationship (one in Table1, Many in Table3) Does this make sense? Now you can use a subform based on Table3 embedded in Form1, listing the migration numbers, and have a good basis for searching what records have MigrationID 1, or 5, or both or whatever... The subform wizard will pretty much do all the work for you as far as creating the master/child links. Sorry if I'm not clear! Susan "Cindy" wrote in message ... So do I just create a form and then in Form # 1 using the wizard add it in as a subform? The field I want is called Migration_ID and it is in both tables/forms 1,2. Just wondering how I would create the subform? Do I need both tables and have both fields in the subform. What do you mean setting the related field to hidden? "SusanV" wrote: Sorry, I misunderstood. If you only want the one field from Table2 included in Form1, probably the simplest way would be to create a new little subform with just the related field and the one you wish to view (set the related field to hidden) and embed this new subform in Form1. "Cindy" wrote in message ... Hi Susan, thanks for the reply. I already have a subform in Form # 1. Is there any other way to do this. I don't want a the whole form displayed in the original form, only the migration id output as such Ex: Original Id: 1 Migration: 1, 6, 7 "SusanV" wrote: Hi Cindy, You can set Form2 as a subform to Form1 to do this - make sure the master/child links are set to the primary/foreign keys (the wizard will walk you through this part) -- hth, SusanV "Cindy" wrote in message ... Hi, I'm wondering if someone can help? I am stuck on this?? I have created two forms who's tables are joined by primary/forigen keys. Table\Form #1 - Original Software Table\Form # 2 - Upgraded. Form # 1 - Has a bunch of records. Each record can have many upgrades. Form # 2 - immedialtely asks u to choose a original software. It is a required field. Form 1 has a upgraded Id field (the upgrade tables primary key, referential integrity enforced) . Is there anyway to show? when you choose an upgrade in form # 2 the autonumber Id from the upgrade form appears in the original softaware form in the upgrade ID field. There may be more than one Id as it can have many upgrades. My goal is if I open a original software record I will see all the associated upgrades. Is this possible and how would I go about doing this? |
#8
|
|||
|
|||
Ho-boy, I'm confused! grin How is the Migration ID being updated in the
original software table? This duplication of data is not "normalized" and there should be only one table holding the migration ID... Does the original software table's migration ID have the data with the commas? how is this data managed? "Cindy" wrote in message ... Hi Susan, maybe its me that not being clear. sorry for the confusion. I have 2 tables, one called Original Software and the other Migration Original_Software Software_ID Migration_ID Software_Title etc.. Migration: Migration_ID Software_ID (drop down list from original software) (required field etc. Then I created 2 forms with the same names. So everytime you upgrade(migrate) you must choose(tie it to) a original software. So One piece of software can have many migration_ID's. In the relationships view I have linked Migration_ID using referential integrity. Ex (migration form) Migration ID: 1 Software_ID: 2 Migration_ID: 2 Software_ID: 2 What I want is when I open the Original Software form there is a field displayed showing all the migration id # associated with the software. Ex: Software_ID#2 Migration _ID# 1, 2 Now what would be my child/master fields? Do I just create the subform using the wizard inside the Original_Software form. "SusanV" wrote: Is the "migration_ID" data duplicated between tables? If so, how is this controlled? It really should only be in one table... normalization and all that. How it the field setup? Do you have it as, say "1, 5, 9"? For multiple items like this you really should have 3 separate records in a related table - for instance Table1 fldRecordID (pk) (...other fields) Table3 (for migration id records) fldRecID (foreign key to Table1.fldFRecordID) fldMigrationNumber With a one-to-many relationship (one in Table1, Many in Table3) Does this make sense? Now you can use a subform based on Table3 embedded in Form1, listing the migration numbers, and have a good basis for searching what records have MigrationID 1, or 5, or both or whatever... The subform wizard will pretty much do all the work for you as far as creating the master/child links. Sorry if I'm not clear! Susan "Cindy" wrote in message ... So do I just create a form and then in Form # 1 using the wizard add it in as a subform? The field I want is called Migration_ID and it is in both tables/forms 1,2. Just wondering how I would create the subform? Do I need both tables and have both fields in the subform. What do you mean setting the related field to hidden? "SusanV" wrote: Sorry, I misunderstood. If you only want the one field from Table2 included in Form1, probably the simplest way would be to create a new little subform with just the related field and the one you wish to view (set the related field to hidden) and embed this new subform in Form1. "Cindy" wrote in message ... Hi Susan, thanks for the reply. I already have a subform in Form # 1. Is there any other way to do this. I don't want a the whole form displayed in the original form, only the migration id output as such Ex: Original Id: 1 Migration: 1, 6, 7 "SusanV" wrote: Hi Cindy, You can set Form2 as a subform to Form1 to do this - make sure the master/child links are set to the primary/foreign keys (the wizard will walk you through this part) -- hth, SusanV "Cindy" wrote in message ... Hi, I'm wondering if someone can help? I am stuck on this?? I have created two forms who's tables are joined by primary/forigen keys. Table\Form #1 - Original Software Table\Form # 2 - Upgraded. Form # 1 - Has a bunch of records. Each record can have many upgrades. Form # 2 - immedialtely asks u to choose a original software. It is a required field. Form 1 has a upgraded Id field (the upgrade tables primary key, referential integrity enforced) . Is there anyway to show? when you choose an upgrade in form # 2 the autonumber Id from the upgrade form appears in the original softaware form in the upgrade ID field. There may be more than one Id as it can have many upgrades. My goal is if I open a original software record I will see all the associated upgrades. Is this possible and how would I go about doing this? |
#9
|
|||
|
|||
The Migration_ID in the Original Software table was included only to show the
associated migration_ID's in the other table. No one would be entering anything there. Only the Migration table holds the migration id's. When I look at my relationships view there is a link from Migration_ID in the Original Software table to Migration_ID in the Migration table. It shows Many software to One migration. But what I want is One to Many one software to many migrations. Should I not be linking migration_id in the migration table to migration_id from the original table, instead should I be linking the software_id's? "SusanV" wrote: Ho-boy, I'm confused! grin How is the Migration ID being updated in the original software table? This duplication of data is not "normalized" and there should be only one table holding the migration ID... Does the original software table's migration ID have the data with the commas? how is this data managed? "Cindy" wrote in message ... Hi Susan, maybe its me that not being clear. sorry for the confusion. I have 2 tables, one called Original Software and the other Migration Original_Software Software_ID Migration_ID Software_Title etc.. Migration: Migration_ID Software_ID (drop down list from original software) (required field etc. Then I created 2 forms with the same names. So everytime you upgrade(migrate) you must choose(tie it to) a original software. So One piece of software can have many migration_ID's. In the relationships view I have linked Migration_ID using referential integrity. Ex (migration form) Migration ID: 1 Software_ID: 2 Migration_ID: 2 Software_ID: 2 What I want is when I open the Original Software form there is a field displayed showing all the migration id # associated with the software. Ex: Software_ID#2 Migration _ID# 1, 2 Now what would be my child/master fields? Do I just create the subform using the wizard inside the Original_Software form. "SusanV" wrote: Is the "migration_ID" data duplicated between tables? If so, how is this controlled? It really should only be in one table... normalization and all that. How it the field setup? Do you have it as, say "1, 5, 9"? For multiple items like this you really should have 3 separate records in a related table - for instance Table1 fldRecordID (pk) (...other fields) Table3 (for migration id records) fldRecID (foreign key to Table1.fldFRecordID) fldMigrationNumber With a one-to-many relationship (one in Table1, Many in Table3) Does this make sense? Now you can use a subform based on Table3 embedded in Form1, listing the migration numbers, and have a good basis for searching what records have MigrationID 1, or 5, or both or whatever... The subform wizard will pretty much do all the work for you as far as creating the master/child links. Sorry if I'm not clear! Susan "Cindy" wrote in message ... So do I just create a form and then in Form # 1 using the wizard add it in as a subform? The field I want is called Migration_ID and it is in both tables/forms 1,2. Just wondering how I would create the subform? Do I need both tables and have both fields in the subform. What do you mean setting the related field to hidden? "SusanV" wrote: Sorry, I misunderstood. If you only want the one field from Table2 included in Form1, probably the simplest way would be to create a new little subform with just the related field and the one you wish to view (set the related field to hidden) and embed this new subform in Form1. "Cindy" wrote in message ... Hi Susan, thanks for the reply. I already have a subform in Form # 1. Is there any other way to do this. I don't want a the whole form displayed in the original form, only the migration id output as such Ex: Original Id: 1 Migration: 1, 6, 7 "SusanV" wrote: Hi Cindy, You can set Form2 as a subform to Form1 to do this - make sure the master/child links are set to the primary/foreign keys (the wizard will walk you through this part) -- hth, SusanV "Cindy" wrote in message ... Hi, I'm wondering if someone can help? I am stuck on this?? I have created two forms who's tables are joined by primary/forigen keys. Table\Form #1 - Original Software Table\Form # 2 - Upgraded. Form # 1 - Has a bunch of records. Each record can have many upgrades. Form # 2 - immedialtely asks u to choose a original software. It is a required field. Form 1 has a upgraded Id field (the upgrade tables primary key, referential integrity enforced) . Is there anyway to show? when you choose an upgrade in form # 2 the autonumber Id from the upgrade form appears in the original softaware form in the upgrade ID field. There may be more than one Id as it can have many upgrades. My goal is if I open a original software record I will see all the associated upgrades. Is this possible and how would I go about doing this? |
#10
|
|||
|
|||
Yes exactly! If the Migration ID is not populated in the Original Software
table you may as well delete it too, once you get the relationship sorted out. Be sure to run any queries, reports etc that may be pulling data from both tables, just in case something is funky (I always modify a copy of the db, just to be sure I have a "go back clause"). Once you have this sorted, create a new form using just the software and migration id's from the Migrations table, and embed it as a subform in the main software form. (If it gives you options on how to link the data, be sure to select the one for migration ID on both tables). "Cindy" wrote in message news The Migration_ID in the Original Software table was included only to show the associated migration_ID's in the other table. No one would be entering anything there. Only the Migration table holds the migration id's. When I look at my relationships view there is a link from Migration_ID in the Original Software table to Migration_ID in the Migration table. It shows Many software to One migration. But what I want is One to Many one software to many migrations. Should I not be linking migration_id in the migration table to migration_id from the original table, instead should I be linking the software_id's? "SusanV" wrote: Ho-boy, I'm confused! grin How is the Migration ID being updated in the original software table? This duplication of data is not "normalized" and there should be only one table holding the migration ID... Does the original software table's migration ID have the data with the commas? how is this data managed? "Cindy" wrote in message ... Hi Susan, maybe its me that not being clear. sorry for the confusion. I have 2 tables, one called Original Software and the other Migration Original_Software Software_ID Migration_ID Software_Title etc.. Migration: Migration_ID Software_ID (drop down list from original software) (required field etc. Then I created 2 forms with the same names. So everytime you upgrade(migrate) you must choose(tie it to) a original software. So One piece of software can have many migration_ID's. In the relationships view I have linked Migration_ID using referential integrity. Ex (migration form) Migration ID: 1 Software_ID: 2 Migration_ID: 2 Software_ID: 2 What I want is when I open the Original Software form there is a field displayed showing all the migration id # associated with the software. Ex: Software_ID#2 Migration _ID# 1, 2 Now what would be my child/master fields? Do I just create the subform using the wizard inside the Original_Software form. "SusanV" wrote: Is the "migration_ID" data duplicated between tables? If so, how is this controlled? It really should only be in one table... normalization and all that. How it the field setup? Do you have it as, say "1, 5, 9"? For multiple items like this you really should have 3 separate records in a related table - for instance Table1 fldRecordID (pk) (...other fields) Table3 (for migration id records) fldRecID (foreign key to Table1.fldFRecordID) fldMigrationNumber With a one-to-many relationship (one in Table1, Many in Table3) Does this make sense? Now you can use a subform based on Table3 embedded in Form1, listing the migration numbers, and have a good basis for searching what records have MigrationID 1, or 5, or both or whatever... The subform wizard will pretty much do all the work for you as far as creating the master/child links. Sorry if I'm not clear! Susan "Cindy" wrote in message ... So do I just create a form and then in Form # 1 using the wizard add it in as a subform? The field I want is called Migration_ID and it is in both tables/forms 1,2. Just wondering how I would create the subform? Do I need both tables and have both fields in the subform. What do you mean setting the related field to hidden? "SusanV" wrote: Sorry, I misunderstood. If you only want the one field from Table2 included in Form1, probably the simplest way would be to create a new little subform with just the related field and the one you wish to view (set the related field to hidden) and embed this new subform in Form1. "Cindy" wrote in message ... Hi Susan, thanks for the reply. I already have a subform in Form # 1. Is there any other way to do this. I don't want a the whole form displayed in the original form, only the migration id output as such Ex: Original Id: 1 Migration: 1, 6, 7 "SusanV" wrote: Hi Cindy, You can set Form2 as a subform to Form1 to do this - make sure the master/child links are set to the primary/foreign keys (the wizard will walk you through this part) -- hth, SusanV "Cindy" wrote in message ... Hi, I'm wondering if someone can help? I am stuck on this?? I have created two forms who's tables are joined by primary/forigen keys. Table\Form #1 - Original Software Table\Form # 2 - Upgraded. Form # 1 - Has a bunch of records. Each record can have many upgrades. Form # 2 - immedialtely asks u to choose a original software. It is a required field. Form 1 has a upgraded Id field (the upgrade tables primary key, referential integrity enforced) . Is there anyway to show? when you choose an upgrade in form # 2 the autonumber Id from the upgrade form appears in the original softaware form in the upgrade ID field. There may be more than one Id as it can have many upgrades. My goal is if I open a original software record I will see all the associated upgrades. Is this possible and how would I go about doing this? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Relationship feature/bug/accident | Peter Danes | General Discussion | 22 | September 11th, 2005 11:15 PM |
Meeting Planner showing details in grid | Peter | General Discussion | 0 | June 9th, 2005 08:26 PM |
Internal and external details | lynn atkinson | Using Forms | 4 | May 4th, 2005 02:50 PM |
Relationship problem | Rock | General Discussion | 1 | March 23rd, 2005 08:22 AM |
Copy a txtbox and details from one form to another | Guernsey Gaz | Using Forms | 1 | February 25th, 2005 02:21 PM |