Re: Help with Ms-Access
Date: Sun, 02 Dec 2007 21:24:14 -0400
Message-ID: <47535ac5$0$5278$9a566e8b_at_news.aliant.net>
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? Received on Mon Dec 03 2007 - 02:24:14 CET