Re: Help with Ms-Access

Jeffrey Davis wrote:

> On Dec 3, 12:24 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> 

> 
> Yes, I think so.
> 
> A primary key is a unique identifier in a table, whereas a foreign key
> accepts the data passed from a field in another table.
> 
> What I'm not sure of is how the right data can be passed.

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.

> I eventually decided to create a fourth table called 'agreements' -- a
> kind of transaction table.
> 
> I then manually entered some test data into the agreements table
> 
> The records here were of all the agreements and had, FarmId,
> PackageID, and CoopMemberID as foreign keys. When I tried setting up
> the realtionships above Ms-Access started warning me that I'd breached
> 'referential integrity' presumably because it wanted to populate the
> field with whatever was in the other tables.

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.

> I turned this function off and ran a query that tried to extract cross
> referenced data (in this case listing farms by package type "P1") and
> it did seem to work.

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

> Now I'm trying to play about with it to make it generate a report with
> a toal based on the selected sales of those meeting the criterion
> "=P1".
> 
> More tinkering needed obviously ...

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.

> Thanks for your feedback. Further comments, if you're so inclined,
> would be welcome, though I obviously don't want to take up all your
> time.
> 
> JD

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! Received on Mon Dec 03 2007 - 04:47:07 CET