Access 2000 - Can I not link 1 Master Field to multiple Child Fields???

[paquer]

Ok'
I have 2 tables.
The first being my main table where each record has a 'FigureID' txt
box. (Indexed - No duplicates)
The second being my sub table where reporting is entered for these
Figures. Sometimes more than one at a time. I have 4 'FigureID' txt
boxes on this subtable.(All Indexed - Duplicates OK)

When I set the Master/Child link for the first FigureID on the
Subtable I have no problems.
The subtable shows all the records with the same FigureID.

Then when I add the 2nd,3rd & 4th relationships, it doesnt work.

The subtable shows records that dont exist (autonumber = autonumber).
& the 2nd,3rd & 4th FigureID's are auto-populated as the FigureID from
the main table?
The first FigureID on the subtable is blank?

Can I not link 1 Master Field to multiple Child Fields???

Any help would be greatly appreciated'
Ryan
pa****@gmail.com

[Rick Brandt]

pa****@gmail.com wrote:

Ok'
I have 2 tables.
The first being my main table where each record has a 'FigureID' txt
box. (Indexed - No duplicates)
The second being my sub table where reporting is entered for these
Figures. Sometimes more than one at a time. I have 4 'FigureID' txt
boxes on this subtable.(All Indexed - Duplicates OK)

This is where you went wrong. Your second table should have its own Primary
Key and then a single foreign key field to relate back to the parent table.
Then each sub-record has exactly one FigureID value. With this setup you
can have zero, one, or multiple child records in the second table that
relate to a single record in the main table.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[paquer]

The Subtable does have a primary key for each record - <ReportID>
You lost me on the single foreign key though...
Each SubRecord (Report) needs to have multiple FigureID values because
the reports usually cover more than one figure.

Here is what is in the 'Subform/Report: page after the Subform Field
Linker
Link Child Fields....... FigID;FigID2;FigID3;FigID4;FigID5
Link Master FIelds..... FigureID;FigureID;FigureID;FigureID;FigureID
<same repeated>

Therefore there are many master records for each child record.

Thanks'
On Feb 6, 9:27 am, "Rick Brandt" <[e-mail]:

[e-mail] wrote:

Ok'
I have 2 tables.
The first being my main table where each record has a 'FigureID' txt
box. (Indexed - No duplicates)
The second being my sub table where reporting is entered for these
Figures. Sometimes more than one at a time. I have 4 'FigureID' txt
boxes on this subtable.(All Indexed - Duplicates OK)

This is where you went wrong. Your second table should have its own Primary
Key and then a single foreign keyfieldto relate back to the parent table.
Then each sub-record has exactly one FigureID value. With this setup you
can have zero, one, ormultiplechildrecords in the second table that
relate to a single record in the main table.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Ron2006]

Your structures don't sound correct. In fact they almost sound as if
they are reversed from what they should be.

Show us in more detail what your two tables look like.

Ron

[paquer]

On Feb 7, 9:02 am, "Ron2006" <[e-mail]:

Your structures don't sound correct. In fact they almost sound as if
they are reversed from what they should be.

Show us in more detail what your two tables look like.

Ron

Actually, no they arent reversed...

The Main Tables records are the 'core' of the Database. Users are
responsible for the individual records indexed by there 'Figure ID #'.
These records are static, showing the current status..

Another group does reporting for the first group & these can relate to
more than one Figure at a time, hence the child records can affect
more than 1 master record at a time.
These reports are not "open" all the time, they happen on specific
dates & then are done, hence they are not the Main Table.

When the user from the (Main Table)group checks the database, I want
them to be able to see all the reporting done that affects their
figures via a Subform. I would link these child records to the master
records by the Figure ID.

I can do this for 1 FigureID, but not for multiple ones. When I add
the additional links, Access disregards the '2nd/3rd/4th' ID's. The
subform record that shows up is 'non-existant' (autonumber =
autonumber). & the 2nd/3rd/4th fields in the subform become
autopopulated with the FigureID from the Main Table???
Hope this clears it up'
Thanks'

[Ron2006]

What is the structure of the tables?

[paquer]

On Feb 7, 10:01 am, "Ron2006" <[e-mail]:

What is the structure of the tables?

Main Table
ID - autonumber
FigureID - text
Status - - text
Last Updated - date
Property - text
TWP- text
Priority - text
Owner - text
Current Request - text
Figure Description - text
Comments - text
etc...
Subtable
ID - Autonumber
Property- text
FigureID- text
FigureID2 - text
FigureID3- text
FigureID4 - text
FigureID5- text
FigureID6 - text
Report Date - Date
Reportee - text
ReportText - text
ReportTextCont1 - text
ReportTextCont2 - text
ReportDiagram1 - OLE
ReportDiagram2 - OLE
ReportDiagram3 - OLE

Not sure why you need this info, the FigureID's are all the same
format.

[Ron2006]

Rick, Maybe you can explain it better......

[paquer]

On Feb 7, 10:54 am, "Ron2006" <[e-mail]:

Rick, Maybe you can explain it better......

What do you need to know???

On the Main Table, each record has a "FigureID". (Indexed - No
Duplicates)
On the Subtable each record can have Multiple "FigureID's". (The
ReportID is what is indexed here)
I just want the Main table's Form to show in the subform all the
subtable records (reports) that have any FigureID's related to the
Main records FigureID#.

[Ron2006]

That is where the design departs from the standard. On the "sub"
table there should only be one FigureID and there should be multiple
records (one for each of the IDs that you have there.) That way you
will get exactly what you want AND the day that you need to go more
than 6 you can handle it with out any changes.

The closest way you can handle it now (and it will not allow any
updates to the information in the sub table) is to use a UNION query.
essentially you will create a query defintion of

MatchFigureID - text
Report Date - Date
Reportee - text
ReportText - text
ReportTextCont1 - text
ReportTextCont2 - text
ReportDiagram1 - OLE
ReportDiagram2 - OLE
ReportDiagram3 - OLE

and the data behind the MatchFigureID for each will be FigureID1 for
one record and ID1 for the second, etc.

Sort of like this if you look at the sql view for A NEW Matched query:

SELECT subtable.[FigureID] AS MatchFigureID, subtable.[ReportDate] AS
ReportDate,....FROM subtable

UNION ALL SELECT subtable.[FigureID2] AS MatchFigureID, subtable.
[ReportDate] AS ReportDate,....FROM subtable

UNION ALL SELECT subtable.[FigureID3] AS MatchFigureID, subtable.
[ReportDate] AS ReportDate,....FROM subtable

UNION ALL SELECT subtable.[FigureID4] AS MatchFigureID, subtable.
[ReportDate] AS ReportDate,....FROM subtable

UNION ALL SELECT subtable.[FigureID5] AS MatchFigureID, subtable.
[ReportDate] AS ReportDate,....FROM subtable

UNION ALL SELECT subtable.[FigureID6] AS MatchFigureID, subtable.
[ReportDate] AS ReportDate,....FROM subtable
And then use this query in the child subform and use MatchFigureID as
the matching field.
Now the day you need more than 6 the table and this query all need to
be changed because of the way the table is defined.

Good Luck.

[Ron2006]

And as your table gets bigger, this query will get slower and slower
because of the iterative process.

Whereas with one record per ID you will hardly see a difference.

[rwr]

pa****@gmail.com wrote:

The Subtable does have a primary key for each record - <ReportID>
You lost me on the single foreign key though...
Each SubRecord (Report) needs to have multiple FigureID values because
the reports usually cover more than one figure.

Here is what is in the 'Subform/Report: page after the Subform Field
Linker
Link Child Fields....... FigID;FigID2;FigID3;FigID4;FigID5
Link Master FIelds..... FigureID;FigureID;FigureID;FigureID;FigureID
<same repeated>

Therefore there are many master records for each child record.

Thanks'
On Feb 6, 9:27 am, "Rick Brandt" <[e-mail]:

>[e-mail] wrote:

>>Ok'
I have 2 tables.
The first being my main table where each record has a 'FigureID' txt
box. (Indexed - No duplicates)
The second being my sub table where reporting is entered for these
Figures. Sometimes more than one at a time. I have 4 'FigureID' txt
boxes on this subtable.(All Indexed - Duplicates OK)

This is where you went wrong. Your second table should have its own Primary
Key and then a single foreign keyfieldto relate back to the parent table.
Then each sub-record has exactly one FigureID value. With this setup you
can have zero, one, ormultiplechildrecords in the second table that
relate to a single record in the main table.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

You need each FigureID to be a separate record, you can't put 4 fields
in one record and try to set a relationship. Look up database
normalization, you tables are not normalized correctly.

Ron

[paquer]

>

Ron- Hide quoted text -

- Show quoted text -

Hi'
The only way for me to separate each report so that it only has 1
FigureID is to duplicate each report for every figureID it relates
to...
Is that not producing duplicate & redundant data, therefore un-
normalizing it?
Ryan

[Ron2006]

On Feb 8, 9:08 am, [e-mail] wrote:

Ron- Hide quoted text -

- Show quoted text -

Hi'
The only way for me to separate each report so that it only has 1
FigureID is to duplicate each report for every figureID it relates
to...
Is that not producing duplicate & redundant data, therefore un-
normalizing it?
Ryan

The basic idea of normalization is that Unique data is maintained only
once, is so far as possible.

Your table is as follows:

Subtable
ID - Autonumber
Property- text
FigureID- text
FigureID2 - text
FigureID3- text
FigureID4 - text
FigureID5- text
FigureID6 - text
Report Date - Date
Reportee - text
ReportText - text
ReportTextCont1 - text
ReportTextCont2 - text
ReportDiagram1 - OLE
ReportDiagram2 - OLE
ReportDiagram3 - OLE

If you are saying that in that table

Property and Report Date (and all the fields below it are identical
for Figure ID 1 and 2 and 3 etc (if they are there) and therefore
would be duplicated if you created a singel record for each ID then
Table 2 should be broken down into 2 tables

Property-Report Table

ID - Autonumber
Property- text
Report Date - Date
Reportee - text
ReportText - text
ReportTextCont1 - text
ReportTextCont2 - text
ReportDiagram1 - OLE
ReportDiagram2 - OLE
ReportDiagram3 - OLE

And a new table - one record for every unique combination of
FigureID and Property Report ID.

Figure-Report Table

ID - Autonumber
FigureID
Property-reportID

This allows you to query either way
Show all reports for any figureID (and not have to search in 6
different fields to find them)

or
Show All Figures that are in any Report And on this one you are not
limited to a max of 6.

Ron