One to many relationships

[Sarah]

I have a master database called "Master List". It contains ID, Name,
Address, phone, etc.

I have a detail database called "Transactions" It contains, ID, Transaction,
event, date, etc. (many records for each in "Master List")

I have a detail database called "Connections". It contains ID and
Connection type (many records for each in "Master List")

I have setup a one to many relation ship with "Master List" and
"Transactions."
I have setup a one to many relationship with "Master List and "Connections"

I have my form working fine updating transactions and connections.

My questions is how do I make this link in creating reports.

I want to create a report that lists the record information from "Master
list" then I want it to list all the connections for that person from
"Connections" and then I want it to list all the transactions for that
person from "Transactions" .

_____________________________________________
HEADER
Name
Address
City, State Zip
Phone, Cell, Wk #

DETAIL
Connections (may have 5 connections)

Transactions (may have 10 transactions)
______________________________________________________


Any help would be greatly appreciated.

Sarah

 

[Joan Wild]

I have a master database called "Master List". It contains ID, Name,
Address, phone, etc.

I have a detail database called "Transactions" It contains, ID,
Transaction,
event, date, etc. (many records for each in "Master List")

I have a detail database called "Connections". It contains ID and
Connection type (many records for each in "Master List")

I assume what you actually have are tables called these; all in one database
file.

My questions is how do I make this link in creating reports.

Much like you did for your form. Create a report for the Master List. Then
create sub-reports for the Connections and Transactions - link Master/Child
on ID.

 

[Lynn Trapp]

If your primary key in the [Master List] table is named ID and the primary
key in the [Transactions] table is named ID, then you have NOT created a one
to many relationship. Rather you have created a one to one relationship.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Sarah]

I assume what you actually have are tables called these; all in one database
file.


Much like you did for your form. Create a report for the Master List. Then
create sub-reports for the Connections and Transactions - link Master/Child
on ID.

--
Joan Wild
Microsoft Access MVP


Thanks Joan - That was exactly what I needed.

 

[Sarah]

Joan,

Thank you for your help. That 's exactly what I needed.

Sarah

 

[Katrina Smith via AccessMonster.com]

hi there Lynn I was wonder if you can help my .When i try to set a one-too-
many relationship it doesn't work ....how do i set this...i only have the
one field in three tables that are the same...I have an employe master
file, an employee deduction ledger file, and a employe income ledger
file.... i was wonder if you can help me or if anyone can me for that
matter.

 

[Lynn Trapp]

hi there Lynn I was wonder if you can help my .When i try to set a

one-too-
many relationship it doesn't work ....how do i set this...

Katrina,
Can you tell me what you tried to do to set that relationship?

i only have the
one field in three tables that are the same...I have an employe master
file, an employee deduction ledger file, and a employe income ledger
file.... i was wonder if you can help me or if anyone can me for that
matter.

Could you post the entire table structure of your tables along with a
description of the business problem you are trying to solve?


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Robert]

This is unrelated to Sarah's question. How would one set up the one to many
link between Master List and Transactions? If the ID in Master List is a
primary key, autonumber field, and the ID in transactions is not a primary
key, number field, would those two fields link. I have tried such a link but
I am not able to enforce referential integrity or get cascading updates?

 

[John W. Vinson]

This is unrelated to Sarah's question. How would one set up the one to many
link between Master List and Transactions? If the ID in Master List is a
primary key, autonumber field, and the ID in transactions is not a primary
key, number field, would those two fields link. I have tried such a link but
I am not able to enforce referential integrity or get cascading updates?

What have you tried? What errors have you gotten?

An Autonumber primary key should link easily to a Number/Long Integer
field in the transactions table. In the Relationships Window you'ld
add both tables, and drag the Autonumber field to the foreign key long
integer field; select the join line and check the Enforce RI checkbox.

There is NO point in specifying Cascade Updates on such a link,
though; you can't update an Autonumber field in any case, and there is
therefore nothing to cascade. If you wish (and care to take the risk
of deleting lots of data by accident) you can specify Cascade Deletes;
this will delete all transactions related to a Master List item if
that item should be deleted.

John W. Vinson [MVP]

 

[Robert]

When I click on Enforce Referential Integrity, Cascade Updates or Cascade
Deletes, I get the message, Relationship must be on the same number of fields
with the same data types.

 

[John W. Vinson]

When I click on Enforce Referential Integrity, Cascade Updates or Cascade
Deletes, I get the message, Relationship must be on the same number of fields
with the same data types.

What are the datatypes and sizes of the two fields you're trying to
join? An Autonumber must be linked to a Number of Long Integer type;
you can't join an autonumber to (say) a Float or Double.

John W. Vinson [MVP]