Query Help One-to-Many

  • Thread starter Thread starter golfinray
  • Start date Start date
G

golfinray

I know this must be stupid, but I keep setting two tables up in a query and
it is doing the opposite of what I want. I have
Project #
School District
School Name
Project Description
Payment number

and

Payment number
Payment amount
Payment Date (must be kept for accounting and auditing purposes)

I want project # to be the one and payments to be the many, one project# can
have up to 30 payments. In the relationships tool, it keeps setting the one
to payments and the many to project number! I know this is a stupid error
somewhere. Thanks for your help.
 
It sounds as if you have a table design problem.

There should be a Project # field in the payment table (foreign key)
There should NOT be a payment Number field in the Project table.

You would link Payments(Many table) to the Project (one table) based on the
project #.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
project # needs to be the primary key in the first table. Then it needs to be
the foreign key in the second table.

It looks like you have this backwards with the Payment number as the FK in
the first table and PK in the second.

Now if you have a situation where a Payment can cover more than one Project
and a Project can have more than one Payment, you'll need a third bridging or
joining table to break up the Many-Many relationship.
 
Golfinray -

Put the 'one side' key as a foreign key in the 'many side' table. Like this:

Project #
School District
School Name
Project Description

and

Payment number
Project #
Payment amount
Payment Date
 
Back
Top