ANy ideas

[Simonglencross]

I have a database used for subscriptions.

Each subscriber is given a mailinglistid (tblsubscribers) and there
subscriptions information is stored in a table called (tblsubscriptions)
what I would like to do is the following, I;ve tried but I'm pritty new to
this....

On the main form "frmsubscribers" I have a Text box called "Subs Paid" I
would like to be able to calculate the total of the field "catcost" in table
"Subscriptions" for each Subscriber or "MailinglistID" . Can anyone point me
in the correct direction?


Many Thanks in advance


Simon

 

[Jeff Boyce]

Without a better idea of the underlying data structure (forms display data,
tables store it), it could be problematic offering any specific query
suggestions.

For example, you want to "calculate the total of the field 'catcost' ... for
each Subscriber." Does this mean you have multiple rows with the same
Subscriber but different "catcost" values, or multiple fields on one row for
one Subscriber? What is the relationship (one-to-one, one-to-many,
many-to-many)?

 

[Simonglencross]

Here goes

Tables As Follows

tblsubscriber has a one-to-many relationship with tblsubscriptions
Fields
Primary Key MailingListID
SubsPaid
SubRenewalDate
Title
Forname
Surname
Address
city
County



tblSubscriptions has a one-to-many relationship with tblsubcat
Fields
Primary Key SubscriptionID
cattypes
catcost
mailinglistID



tblsubcat Fields
Primary Key catID
cattypes
catcost

I hope this makes things a little clearer, I have multiple rows with
differnet cat costs but each line has the subscribers mailinglistiD.

Many Thanks

Simon

 

[Jeff Boyce]

Have you looked into the "Totals" queries? This would give you a way to get
a sum of catcost, grouping by MailingListID.

You could create a new query joining tblSubscriber to tblSubscription.
Select MailingListID and subcat. Click on the Totals toolbar button, set
MailingListID as "GroupBy" and catcost as "Sum".