Prev: Querying Date Field, Disregarding Year
Next: Query multiple Criteria, pick one!
From: golfinray on 24 Mar 2010 14:09
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.
--
Milton Purdy
ACCESS
State of Arkansas
From: John Spencer on 24 Mar 2010 14:18
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

golfinray wrote:
> 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.
From: Jerry Whittle on 24 Mar 2010 14:41
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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"golfinray" wrote:

> 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.
> --
> Milton Purdy
> ACCESS
> State of Arkansas
From: Daryl S on 24 Mar 2010 14:46
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

--
Daryl S


"golfinray" wrote:

> 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.
> --
> Milton Purdy
> ACCESS
> State of Arkansas
From: golfinray on 24 Mar 2010 15:06
Thanks guys, the old brain wasn't working on that one!
--
Milton Purdy
ACCESS
State of Arkansas


"John Spencer" wrote:

> 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
>
> golfinray wrote:
> > 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.
> .
>
 | 
Pages: 1
Prev: Querying Date Field, Disregarding Year
Next: Query multiple Criteria, pick one!