showing details of a many to one relationship in a form.

[Cindy]

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?

 

[SusanV]

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)

 

[Cindy]

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]

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]

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]

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]

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]

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]

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]

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).