Many to One ?

[Linda]

Sorry about first post...
Tables:
BusRouteTble The "One" Table
Autonumber PK
Bus_number
Start_City
Start_Time
End_City
End_Time

Route_Fee_Code The "Many" Table
Autonumber PK
Fee_Type
Fee_Code

The BusRouteTable has one record for each trip

I need to add many Route_Fee_Codes to that record

I thought to build a Form to Enter the BusRouteTable
info and in it a subform to Enter Route_Fee_Codes

Then create a Report: For each Route all the Fee Codes

How can I create a relationship between them ?

Thanks again for your great support !!!!!

 

[Roger Carlson]

To create a One-to-Many relationship, you have to put the primary key field
of the table on the "one" side into the table on the "many" side as a
Foreign Key (NOT Primary Key). Then go to your Relationships Window and
create the relationship.

 

[Linda]

Rodger: But the PK is an Autonumber, how can I use
Autonumber as a FK ? Will it keep writing the same Route
record and (same Autonumber) for each new Fee_Code I input
for that record ?

I will ask the Forms area about how to setup the form and
subform

Thanks for your quite response Rodger

 

[Linda]

Rodger: But the PK is an Autonumber, how can I use
Autonumber as a FK ? Will it keep writing the same Route
record and (same Autonumber) for each new Fee_Code I input
for that record ?

I will ask the Forms area about how to setup the form and
subform

Thanks for your quite response Rodger

 

[Linda]

Rodger: But the PK is an Autonumber, how can I use
Autonumber as a FK ? Will it keep writing the same Route
record and (same Autonumber) for each new Fee_Code I input
for that record ?

I will ask the Forms area about how to setup the form and
subform

Thanks for your quite response Rodger

 

[Lynn Trapp]

Linda,
The FK should NOT be an Autonumber. Doing that would create a One to One
relationship. Instead, you should use a Long Integer as the FK.

 

[Linda]

Lynn: I'm sorry....the PK of the "One" Table is an
Autonumber PK - is that ok? I will create a Long Integer
FK in the "Many" Table and create a One:Many Relationship.
Could you also answer my other concerns ?

 

[Linda]

Lynn: I'm sorry....the PK of the "One" Table is an
Autonumber PK - is that ok? I will create a Long Integer
FK in the "Many" Table and create a One:Many Relationship.
Could you also answer my other concerns ?

 

[Linda]

Lynn: I'm sorry....the PK of the "One" Table is an
Autonumber PK - is that ok? I will create a Long Integer
FK in the "Many" Table and create a One:Many Relationship.
Could you also answer my other concerns ?

 

[Roger Carlson]

Sorry, I wasn't sufficiently clear. What you write below is *precisely*
what you need. Make sure that when you create the Relationship in the
Relationship Window you turn Referential Integrity ON.

What were your other concerns?

 

[Linda]

Sorry to ask here..........
How would my Form and subform relate to each other?
What would the control field be so I can enter data on the
Form and then data on the subform?

If I need to take this to "Forms" I sure will!

And thank you for your quick response!

You guys are the GREATEST!

 

[Roger Carlson]

In the subform control, there are two properties Link Child Fields and Link
Master Fields. If you click the builder button [...] at the end of either,
it will give you a dialog box to indicate which fields you are linking on.
Generally it is the Primary Key in the Master and the Foreign Key in the
Child.

From this point, the subform control takes care of the linking. The really
cool thing about the subform control is that it also inserts the Primary key
from the master into the foreign key field of each new record created, so
you don't have to bother with that.

On my website (see sig below) is a small sample database called
FormSubform.mdb, which illustrates a simple form/subform scenerio.

 

[Craig Hornish]

With all the discussion on where to put what I think
the actual tables have been overlooked. IMHO I think
that the Route fee should be associated to the BusRoute
table. I assume that there is 'One' route fee for each
bus and that 'Many' Busess can use the same route fee.

As I noted below you can either use the Autonumber
for the actual linking or the Fee_Code itself. (Which is
better has probably been discussed before

Tables:
BusRouteTble The "One" Table
** Bus_Code_ID an Autonumber PK ** May be used in
another table to determine connecting routes.
Bus_number
Start_City
Start_Time
End_City
End_Time
** Fee_Code_ID FK

Route_Fee_Code The "Many" Table
** Fee_Code_ID an Autonumber PK
Fee_Type
Fee_Code ** Or use this field in the other table
** Cost

Craig