Help setting up Ms-Access Database

[Jeffrey Davis]

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


PS 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


PPS ... Bob Badour in another group suggested I clarify the following
questions:


"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


Any assistance would be greatly valued.


JD

 

[david12]

I'm hoping that someone here can give me some assistance with a
database I'm trying to set up. My skills inAccessare 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 intoAccess. 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 throughAccessand maybe I've missed it, but
could someone point me in the right direction?

Thanks in advance ...

JD

PS 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

PPS ... Bob Badour in another group suggested I clarify the following
questions:

"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

Any assistance would be greatly valued.

JD

Jeffrey,
What I say will give you a start but know that this application is not
trivial and you'll spend many hours setting this up and maintaining
it.
You can't have a field in "common" with more than one table but you
can make foreign keys that relate tables together by pointing to
primary keys (always unique).
Below, in the tFarms table, MbrID is a foreign key pointing to the
primary key ID in the tMbrs table so every farm points to a single
member.
That way many farms can point to a single member but a farm can have
only one member as you said.
I can only go by what you said as to how your entities relate.
From what you say I think this system is analogous to sales/order
system in which a package can be thought of as a list of 4 available
products and a member can be thought of as a customer.
What's different is that the farm is like a conduit between packages
and members.
Anyway, you're right about the transactions table which is analogous
to a list of orders.
If you're right about relationships and I understand you correctly,
try starting with these 4 tables:

tPkgs table:
Fields: ID (AutoNumber, primary key), PkgName (text), Rate(currency),
all the other fields for packages

tFarms table:
Fields: ID (Autonum, PK), FarmName (txt), MbrID (Number/Long), all the
other fields for farms

tMbrs table:
Fields: ID (Autonum, PK), MbrName (txt), all the other fields for coop
members like phone, email address, etc.

tTrans table:
Fields: ID (Autonum, PK), TransDate (Date), FarmID (Number/Long),
PkgID (Number/Long), any other transaction fields like Qty.

The form you mention would be centered on the Farm
(RecordSource=tFarms) with a subform for list of transactions related
to the farm thru the FarmID foreign key.
The subform (rec.source=tTrans) would show the transDate and PkgID.
You could make a combobox show a list of available packages with the
ID and PkgName as fields in the Row Source (SELECT ID, PkgName FROM
tPkgs).
You mention a Rate so I assume the tTrans table needs some kind of
quantity amount like hours or units whatever makes sense for how your
packages work.
Somewhere in this form you need a combo-box to select the member for
the farm with "SELECT ID, MbrName FROM tMbrs" in the Row Source.
I could go on...
Macros reside within Access databases; they don't open them. Do you
mean a shortcut in Windows or do you mean the form should open when
you open the database. If the latter, choose "Startup" from the Tools
menu and set the Display Form/Page to the name of the form you've
created.

There's much else to do but, as I said, this is a start.

Dave Stelle, Access Application Developer

 

[david12]

I'm hoping that someone here can give me some assistance with a
database I'm trying to set up. My skills inAccessare 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 intoAccess. 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 throughAccessand maybe I've missed it, but
could someone point me in the right direction?

Thanks in advance ...

JD

PS 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

PPS ... Bob Badour in another group suggested I clarify the following
questions:

"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

Any assistance would be greatly valued.

JD

Regarding what you said as to only 1 package per farm, following my
customer/orders analogy, consider that there's only one current
package per farm but multiple over time based on what you said about
transactions.

But if not and there is truly only 1 package per farm permanently then
adjusting the design I sent earlier, move the PkgID foreign key into
the tFarms table instead of the tTrans table and don't make a separate
table of transactions because the list of farms & packages acts like
it. You could also have a date or whatever other transaction-oriented
data fields as part of the tFarms table.
If you want the main form to be designed for single members, it just
needs a subform to list the farms, each with its own package selected
from available packages. Then above the list is the basic member data
like contact info.

So here are revised tables following those needs:

tMbrs table:
Fields: ID (AutoNumber, primary key), MbrName (text), all the other
fields for coop members like phone, email address, etc.

tPkgs table:
Fields: ID (AutoNumber, primary key), PkgName (text), Rate(currency),
all the other fields for packages

tFarms table:
Fields: ID (Autonum, PK), FarmName (txt), MbrID (Number/Long), PkgID
(Number/Long), TransDate (Date), all the other fields for a farm and
implementing the package for the farm

However, if there is a need for more than one transaction per Farm-
Pkg, you might want a separate dialog window to show these that the
user can open using a "Show Trans" button in each Farm row.
And here's the tTrans table:
Fields: ID (Autonum, PK), FarmID (Number/Long), TransDate (Date)
(instead of in the tFarms table), any other transaction fields.

I hope one of these combinations is right for you.
Let me know what you end up choosing as the "right" way.

David Stelle, Access Application Developer

 

[Jeffrey Davis]

Regarding what you said as to only 1 package per farm, following my
customer/orders analogy, consider that there's only one current
package per farm but multiple over time based on what you said about
transactions.

But if not and there is truly only 1 package per farm permanently then
adjusting the design I sent earlier, move the PkgID foreign key into
the tFarms table instead of the tTrans table and don't make a separate
table of transactions because the list of farms & packages acts like
it. You could also have a date or whatever other transaction-oriented
data fields as part of the tFarms table.
If you want the main form to be designed for single members, it just
needs a subform to list the farms, each with its own package selected
from available packages. Then above the list is the basic member data
like contact info.

So here are revised tables following those needs:

tMbrs table:
Fields: ID (AutoNumber, primary key), MbrName (text), all the other
fields for coop members like phone, email address, etc.

tPkgs table:
Fields: ID (AutoNumber, primary key), PkgName (text), Rate(currency),
all the other fields for packages

tFarms table:
Fields: ID (Autonum, PK), FarmName (txt), MbrID (Number/Long), PkgID
(Number/Long), TransDate (Date), all the other fields for a farm and
implementing the package for the farm

However, if there is a need for more than one transaction per Farm-
Pkg, you might want a separate dialog window to show these that the
user can open using a "Show Trans" button in each Farm row.
And here's the tTrans table:
Fields: ID (Autonum, PK), FarmID (Number/Long), TransDate (Date)
(instead of in the tFarms table), any other transaction fields.

I hope one of these combinations is right for you.
Let me know what you end up choosing as the "right" way.

David Stelle, Access Application Developer- Hide quoted text -

- Show quoted text -

Thanks very much for taking the trouble to reply. I've only just this
minute been able to get back to this project and check responses.

I'll certainly give this a go and let you know how it worked out.

JD