Re: Help with Ms-Access
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Mon, 03 Dec 2007 09:55:36 -0400
Message-ID: <47540add$0$5264$9a566e8b_at_news.aliant.net>
>>Jeffrey Davis wrote:
>>
>>>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.
>>
>>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!
Date: Mon, 03 Dec 2007 09:55:36 -0400
Message-ID: <47540add$0$5264$9a566e8b_at_news.aliant.net>
Jeffrey Davis wrote:
> On Dec 3, 2:47 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote: >
>>Jeffrey Davis wrote:
>>
>>>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.
>>
>>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!
> > > 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.]
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. Received on Mon Dec 03 2007 - 14:55:36 CET