Re: Help with Ms-Access

On Dec 3, 12:24 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> Jeffrey Davis wrote:
> > On Dec 3, 11:23 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
> >>Jeffrey Davis wrote:
>
> >>>On Dec 3, 10:51 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
> >>>>Jeffrey Davis wrote:
>
> >>>>>I'm hoping that someone here can give me some assistance with a
> >>>>>database I'm trying to set up. My skills in Access are fairly basic,
> >>>>>and I'm trying to skill up, but some of the stuff is a little opaque.
>
> >>>>>I'm trying to put some data I've got on paper into Access. Recently, I
> >>>>>got together with some other people marketing organic produce and
> >>>>>offered to do some marketing for us on a coop basis in the city.
>
> >>>>>The idea I had was to have four marketing packages for coop members
> >>>>>with different rates and services. For want of something better lets
> >>>>>call them P1, P2, P3 & P4
>
> >>>>>I'm thinking I'll need a table with the packages, and one listing the
> >>>>>coop members with their details. In theory, a coop member might
> >>>>>acquire another farm (either a new one or one from someone bailing
> >>>>>out) and put it on a separate package.
>
> >>>>>Which tables would have to have a relationship?
>
> >>>>>I'm thinking the packages table containing the dat about each package
> >>>>>would have to be related via a common field to the coop member table,
> >>>>>and there would probably have to be a link between that and the farm
> >>>>>table, through common fields -- maybe a unique ID in the coop member
> >>>>>table could appear in the farm table and maybe the primary key from
> >>>>>the package table could also appear in the farm table as a foreign
> >>>>>key. But am I right?
>
> >>>>>I'd also like to create a form that would list an individual coop
> >>>>>member's details plus any packages they own and assume that all I'd
> >>>>>need to do would be to ensure that there was a specified report for
> >>>>>the form to call. Is that right? Is 'switchboard manager' the way to
> >>>>>go here?
>
> >>>>>I'd also like to create a macro to automatically open the database.
> >>>>>I've had a bit of a look through Access and maybe I've missed it, but
> >>>>>could someone point me in the right direction?
>
> >>>>>Thanks in advance ...
>
> >>>>>JD
>
> >>>>Hi Jeffrey,
>
> >>>>I think you may find people more willing to give answers at
> >>>>comp.databases.ms-access especially with respect to macros and user
> >>>>interfaces.
>
> >>>>I am not sure what the distinction is between coop member and farm. I
> >>>>have no idea what you mean by 'switchboard manager'.
>
> >>>It's a kind of automated form in access that carries command buttons
> >>>that open/run specified database objects.
>
> >>>>Questions you need to answer for your design are:
>
> >>>>How many packages can a farm have?
>
> >>>only 1
>
> >>>>How many packages can a coop member have?
>
> >>>in theory, unlimited, although only one per farm
>
> >>>>How many farms can a coop member have?
>
> >>>in theory, unlimited
>
> >>>>How many coop members can a farm have?
>
> >>>just one
>
> >>>Thanks for your suggestion. I'll repost there.
>
> >>>JD
>
> >>Given that a farm can have only one coop member and only one package, it
> >>follows naturally that farm will reference coop member and package directly
>
> > So then you only need one table to cover both bits of data?
>
> I don't think I said that, and I am not entirely certain I understand
> what you are saying.
>
> > Maybe a farm table with the coop member and package as part of the
> > overall record?
>
> > So then you'd have
>
> > the farm table set out something like
>
> > FarmID
> > CoopMember <--- data from CoopMember table
> > Package <--- data from Packages table
> > FarmAddress
> > etc ...
>
> That depends on what you mean by "data from". If you mean a foreign key
> reference, then yes, that's exactly what I meant.
>
> > And then a CoopMember table
>
> > MemberID
> > other member data etc ...
>
> > And a Package Table
>
> > PackageID
> > other package data etc ...
>
> > Does that sound right?
>
> Probably. Doing design by usenet is foolish.
>
> Do you understand what a primary key (or candidate key) versus a foreign
> key is?

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.

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.

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.

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 ...

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 Received on Mon Dec 03 2007 - 03:50:59 CET