Jeffrey Davis wrote:

The data is not really passed anywhere. It just is where it is. In your
original example, you gave 4 arbitrary identifiers for packages: p1, p2,
p3, and p4.

Suppose instead of access you were using index cards like you might find
in a library card catalog or in a recipe box. You could have a stack of
4 cards for packages with p1 or p2 or p3 or p4 written in the top left
corner of each card to identify it with the rest of the information
about the package written on the card. We could say that whatever we
write in the top-left corner of the card is the primary key and it must
uniquely identify the card in the stack of cards.

You could have another stack of cards for coop members with a unique
member number in the top left corner of each coop member card. The
member number would be the primary key.

You could have another stack of cards for farms with a farm name in the
top left corner of each farm card. The farm name would be the primary
key. On each farm card, you would have a place to write down the package
number (p1, p2, p3 or p4) and another place to write down the coop
member. Each of those are foreign keys referencing a unique card in one
of the other stacks.

Each stack of cards is like a table in access. You don't have to record
all of the details of each package in each farm -- you only need to
record enough to look up the package information somewhere else.


That probably means you have corrupt data somewhere. If you fix the data
so that every agreement references an existing farm, package and member,
the error message should go away. You might have to delete a bad
agreement row or fix a bad entry.

To extend my example above, you would have another stack of cards called
agreements. Instead of writing the package number and member number on
each farm card, you would write the package number, member number and
farm name on each agreement card.


It would seem to work even if some of the data were corrupt.


No doubt. I am sure there are a lot of things you have not yet
considered like "When does an agreement come into effect?", "What
happens when an agreement terminates?" etc.


I would suggest you get a more solid grounding in the fundamentals
before completing your project, but I doubt you would follow the suggestion.

Good luck with it!

On Dec 3, 2:47 pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
Thanks. I'm actually finding it quite interesting as an intellectual
exercise, (albeit I'm way outside my comfort zone and feel a bit of an
idiot) so I probably will do some more study. Your dummies guide to
access "office 2000 made easy" in my case, is not really all that
helpful and some of the databse books read like the techos answer to
Umberto Eco or Michel Foucault -- except that I can follow these
latter!

I'm still trying to build a query that calculates the sum of a number
of records selected by the meeting the value of another field.

I keep getting type mismatches when I try to insert the criterion.

If I better specified (eg by specifying the full field and table name
in the correct syntax), would this work?

[Only if you want to answer ... I'm still thankful for the time you've
spent.]

JD

..

Jeffrey Davis wrote:

I assume you are using some sort of graphical query building tool in
Access. I don't know anything about that.

Other than general suggestions like checking to make sure the types of
foreign key fields match the types of referenced primary key fields, I
don't really have anything to offer. Perhaps someone on one of the MS
Access forums will help more.

"Jeffrey Davis" <jpm1121 (AT) gmail (DOT) com> wrote

I might be able to give you some help here.

Bob Badour has already asked the basic questions about "how many of this and
how many of that". I don't need to repeat his questions in this regard.

He also asked whether you understood the concept of foreign key. I read
your answer but I want to go a little deeper than the answer you gave.

Given a bunch of tables (a table about packages, a table about co-op
members, a table about farms, etc.) Do you know how to use foreign keys
to tie the whole bunch together?
Once you have put the foreign keys into to the tables, do you know how to
use the "relationship tool" in MS Access to bind each foreign key to the
primary key it references.

Do you know how to cause MS Access to enforce referential integrity? Do you
know the consequences of doing this? Do you know the consequences of not
doing this?


Do you know how to create a query that pulls data from more than one table,
using either the graphical interface, or SQL? Do you know SQL? In
particular, do you know what an INNER JOIN does? Do you know how to use
the graphical interface to create a query that, when you switch from Design
view to SQL view, shows you one or more INNER JOINS in the query?

There's a lot of overlap in the above questions. What I'm trying to get at
is what you already know, and what you need to know.