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.co m

[Rick Brandt]

pa****@gmail.co m 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;Fi gID3;FigID4;Fig ID5
Link Master FIelds..... FigureID;Figure ID;FigureID;Fig ureID;FigureID
<same repeated>

Therefore there are many master records for each child record.

Thanks'
On Feb 6, 9:27 am, "Rick Brandt" <rickbran...@ho tmail.comwrote:

[e-mail] m 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, ormultiplechild 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

[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" <ronne...@hotma il.comwrote:

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" <ronne...@hotma il.comwrote:

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" <ronne...@hotma il.comwrote:

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.