Help with Ms-Access

#1

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

#2

On Dec 3, 10:01 am, Jeffrey Davis <jpm1... (AT) gmail (DOT) com> wrote:

Quote:

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

Further to this, it occurs to me that if I'm going to permit coop
members to have more than one package then maybe I need some sort of
transactions table in which the records would be packages actually
taken out. Each record could contain the ID from the relevant farm
(the transactions table being the 'one' and the farm being the many)
and an ID from the packages table (eg P1 to P4) where the transactions
table could be the many.

Am I in a muddle here or on the right track?

JD

#3

Jeffrey Davis wrote:

Quote:

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

Questions you need to answer for your design are:

How many packages can a farm have?
How many packages can a coop member have?
How many farms can a coop member have?
How many coop members can a farm have?

#4

On Dec 3, 10:51 am, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

Quote:

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.

Quote:

Questions you need to answer for your design are:

How many packages can a farm have?

only 1

Quote:

How many packages can a coop member have?

in theory, unlimited, although only one per farm

Quote:

How many farms can a coop member have?

in theory, unlimited

Quote:

How many coop members can a farm have?

just one

Thanks for your suggestion. I'll repost there.

JD

#5

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

Quote:

On Dec 3, 10:01 am, Jeffrey Davis <jpm1... (AT) gmail (DOT) com> 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

Further to this, it occurs to me that if I'm going to permit coop
members to have more than one package then maybe I need some sort of
transactions table in which the records would be packages actually
taken out. Each record could contain the ID from the relevant farm
(the transactions table being the 'one' and the farm being the many)
and an ID from the packages table (eg P1 to P4) where the transactions
table could be the many.

Am I in a muddle here or on the right track?

JD

I agree with Bob here.

You'll probably get more help with an Access application in a product
specific newsgroup.

The one I recommend is microsoft.public.access

(Bob, a "switchboard" in Access is a form that is made to look like a
bushbutton menu.
The switchboard manager is a tool that allows you to create and revise a
switchboard without writing any code. No, I'm not an Access expert. I'm
just coming up to speed on it, now that I'm retired).

#6

On Dec 3, 11:10 am, "David Cressey" <cresse... (AT) verizon (DOT) net> wrote:

Quote:

"Jeffrey Davis" <jpm1... (AT) gmail (DOT) com> wrote in message

news:0bb382b5-a679-4945-bf6e-ede4807c5a37 (AT) s36g2000prg (DOT) googlegroups.com...

On Dec 3, 10:01 am, Jeffrey Davis <jpm1... (AT) gmail (DOT) com> 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

Further to this, it occurs to me that if I'm going to permit coop
members to have more than one package then maybe I need some sort of
transactions table in which the records would be packages actually
taken out. Each record could contain the ID from the relevant farm
(the transactions table being the 'one' and the farm being the many)
and an ID from the packages table (eg P1 to P4) where the transactions
table could be the many.

Am I in a muddle here or on the right track?

JD

I agree with Bob here.

You'll probably get more help with an Access application in a product
specific newsgroup.

The one I recommend is microsoft.public.access

(Bob, a "switchboard" in Access is a form that is made to look like a
bushbutton menu.
The switchboard manager is a tool that allows you to create and revise a
switchboard without writing any code. No, I'm not an Access expert. I'm
just coming up to speed on it, now that I'm retired).- Hide quoted text -

- Show quoted text -

Thanks for the suggestion David. I've just posted there.

JD

#7

Jeffrey Davis wrote:

Quote:

On Dec 3, 10:51 am, Bob Badour <bbad... (AT) pei (DOT) 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.

#8

On Dec 3, 11:23 am, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

Quote:

Jeffrey Davis wrote:
On Dec 3, 10:51 am, Bob Badour <bbad... (AT) pei (DOT) 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?

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

And then a CoopMember table

MemberID
other member data etc ...

And a Package Table

PackageID
other package data etc ...

Does that sound right?

Thanks

JD

#9

Jeffrey Davis wrote:

Quote:

On Dec 3, 11:23 am, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

Jeffrey Davis wrote:

On Dec 3, 10:51 am, Bob Badour <bbad... (AT) pei (DOT) 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.

Quote:

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.

Quote:

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?

#10

On Dec 3, 12:24 pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

Quote:

Jeffrey Davis wrote:
On Dec 3, 11:23 am, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

Jeffrey Davis wrote:

On Dec 3, 10:51 am, Bob Badour <bbad... (AT) pei (DOT) 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