473,324 Members | 2,268 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Help setting up Ms-Access Database

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

Dec 3 '07 #1
4 1724
On Dec 3, 11:12 am, Jeffrey Davis <jpm1...@gmail.comwrote:
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
Ok ,,, I've done the above ... not perfect but workable (still haven't
figured out how to create the macro) ,,,

but I'm 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 fuill field and table name
in the correct syntax), would this work?

TIA

JD

Dec 3 '07 #2
On Sun, 2 Dec 2007 20:15:53 -0800 (PST), Jeffrey Davis
<jp*****@gmail.comwrote:

Show us your query and the data types of the fields involved. The
error message seems to indicate you're doing something that violates
the data type rules. E.g. Sum(FirstName) would trigger this error, or
"...where FirstName=Tom" (because the quotes are missing).

To start your application, set a startup form, or create an AutoExec
macro.

-Tom.

<clip>
>
Ok ,,, I've done the above ... not perfect but workable (still haven't
figured out how to create the macro) ,,,

but I'm 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 fuill field and table name
in the correct syntax), would this work?

TIA

JD
Dec 3 '07 #3
On Dec 3, 8:06 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Sun, 2 Dec 2007 20:15:53 -0800 (PST), Jeffrey Davis

<jpm1...@gmail.comwrote:

Show us your query and the data types of the fields involved. The
error message seems to indicate you're doing something that violates
the data type rules. E.g. Sum(FirstName) would trigger this error, or
"...where FirstName=Tom" (because the quotes are missing).

To start your application, set a startup form, or create an AutoExec
macro.

-Tom.

<clip>


Ok ,,, I've done the above ... not perfect but workable (still haven't
figured out how to create the macro) ,,,
but I'm 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 fuill field and table name
in the correct syntax), would this work?
TIA
JD- Hide quoted text -

- Show quoted text -
I've just used the standard query app..

When it's run, it accurately extracts the relevant records with the
sales figures displayed for each of them.

SELECT package.Type, farms.Location, farms.farmID, farms.State,
farms.Sales
FROM farms INNER JOIN (coopMember INNER JOIN (package INNER JOIN
agreements ON package.Type = agreements.package) ON
coopMember.coopMemberID = agreements.coopMember) ON farms.farmID =
agreements.Restaurant
GROUP BY package.Type, farms.Location, farms.farmID, farms.State,
farms.Sales
HAVING (((package.Type)="p1"))

I tried to write some script to sum the three extracted records (i.e
all those with p1 as the value of the package type) in a new column,
but no dice.
In Excel, this would be fairly easy as you'd simply write an IF
referenced to the column with the p values specifying p1 and then sum
them at the bottom of the column, but in Access ...

Thanks.

JD

Dec 3 '07 #4
Jeffrey Davis wrote:
On Dec 3, 8:06 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
>>On Sun, 2 Dec 2007 20:15:53 -0800 (PST), Jeffrey Davis

<jpm1...@gmail.comwrote:

Show us your query and the data types of the fields involved. The
error message seems to indicate you're doing something that violates
the data type rules. E.g. Sum(FirstName) would trigger this error, or
"...where FirstName=Tom" (because the quotes are missing).

To start your application, set a startup form, or create an AutoExec
macro.

-Tom.

<clip>


>>>Ok ,,, I've done the above ... not perfect but workable (still haven't
figured out how to create the macro) ,,,
>>>but I'm 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 fuill field and table name
in the correct syntax), would this work?
>>>TIA
>>>JD- Hide quoted text -

- Show quoted text -


I've just used the standard query app..

When it's run, it accurately extracts the relevant records with the
sales figures displayed for each of them.

SELECT package.Type, farms.Location, farms.farmID, farms.State,
farms.Sales
FROM farms INNER JOIN (coopMember INNER JOIN (package INNER JOIN
agreements ON package.Type = agreements.package) ON
coopMember.coopMemberID = agreements.coopMember) ON farms.farmID =
agreements.Restaurant
GROUP BY package.Type, farms.Location, farms.farmID, farms.State,
farms.Sales
HAVING (((package.Type)="p1"))

I tried to write some script to sum the three extracted records (i.e
all those with p1 as the value of the package type) in a new column,
but no dice.
In the Totals row of the query builder (where you see all those
GroupBys), have you considered changing the Sales column from GroupBy to
Sum?
>

In Excel, this would be fairly easy as you'd simply write an IF
referenced to the column with the p values specifying p1 and then sum
them at the bottom of the column, but in Access ...

Thanks.

JD


Dec 3 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Steve | last post by:
Hi, I'm setting up an Intranet for a WAN of a 5 remote sites linked with 128 ISDN links. We run a Win2000 servers network with win 98 and XP clients. I currently use MS Exchange 2000 on a...
4
PDF
by: Dave | last post by:
Can anyone point me in the direction of any notes on loading pdf files from within an ASP.Net app Cheers Dave
23
by: gregf | last post by:
I have a paragraph of text pasted into a word document, it's in Polish, complete with polish characters. They show up just fine in word, but the program I use for web page programming, HomeSite,...
5
by: Bamse | last post by:
hi, i have this situation: 1 gridDocs binded to a table, 1 gridRev + several controls binded to a dataset, which does not contain the above mentioned table; i want that when i select a row in...
1
by: Sven Buggermann | last post by:
Hi, i am getting only vb.net code samples when parsing the mdsn library can someone tell me how to set with what language code samples are displayed ? Thanks & regards, Sven
2
by: Paul M | last post by:
Hi there, can anyone help me with this one.... i have a page which is in normal english format, but when i wanna change it to Russian format text, i set the Culture code, but it still remains...
13
by: Lloyd Sheen | last post by:
I have now spent 5 hours on google/msdn looking for something useful in the creation of user controls for asp.net. The VS 2003 has very limited support for things such as absolute positioning of...
3
by: Justnew | last post by:
I have a web application and a frameset. The fameset have left and righ frame. What I want to archieve is that when I click on a button on th left frame I want the link that is placed on the button...
10
by: Donald French | last post by:
I have posted this before and got n real help. I have been beating my head against the wall over this one. Everytime I try to create a new web application I get this message. I have done the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.