What am I not doing right...?

M

Mike C

I've created a simple database using Access 2002 tracking donations made by
individuals and donations made by Corporations. These donations are being
used to rebuild the old arena in town so I created two separate tables - one
for Corporations and the other for Inidividuals using the 'Amount' field as
the primary key in both tables.
I can total up the donations made by corporations and I can total up the
donations made by individuals using the =Sum([Amount]) expression in the
Report Footer.

When they asked me to total up ALL the donations made by both groups I can't
figure out a way to do that.

Should I have just used ONE table? I can't seem to get the Relationships
arrow to point both ways between the tables in my query... I can either get
all the corp or all the inidividual donations but I can't seem to get both.
I'm sure there's a simple explanation for this using an expression I can't
find or can't write (due to inexperience with the software).

Any suggestioons would be greatly appreciated.

Mike C.
 
W

Wayne-I-M

Hi Mike

You may be better off just have one payments table.

Just an idea

Create a new table

Table Name
Payments

PaymentID - Autonumber
Amount - Currency
PayDate - Date
FromWho - Text
Other details, et, etc
I would add other fields that would be need such as donor address – this way
you could send an automated recipt, etc, etc

Oh – if people make regular payments you may want to think about a Donor
table (but that’s for another day)

Create a query

SELECT Payments.PaymentID, Payments. Amount, Payments.PayDate,
Payments.FromWho
FROM Payments;

Create a form based on the query
Add the fields to the form - except “FromWhoâ€
Add a combo to the form
Control Source = FromWho
Row Source Type = Value List
Row Source = " Corporations ";" Inidividuals "

In the footer section create a text box
=sum([Amount])

Create the report - if you need one in much the same way but include FromWho
field and sum on that.

Good luck

Happy Christmas
 
J

John W. Vinson

I've created a simple database using Access 2002 tracking donations made by
individuals and donations made by Corporations. These donations are being
used to rebuild the old arena in town so I created two separate tables - one
for Corporations and the other for Inidividuals using the 'Amount' field as
the primary key in both tables.
Click to expand...

A Primary Key must uniquely identify the record: that is, there can be one and
only one record in the table with that value. The Amount is inappropriate as
the primary key here, unless you want to say that there can only be one $100
donation in the entire table, and all future $100 donations will be politely
declined!
I can total up the donations made by corporations and I can total up the
donations made by individuals using the =Sum([Amount]) expression in the
Report Footer.

When they asked me to total up ALL the donations made by both groups I can't
figure out a way to do that.

Should I have just used ONE table? I can't seem to get the Relationships
arrow to point both ways between the tables in my query... I can either get
all the corp or all the inidividual donations but I can't seem to get both.
I'm sure there's a simple explanation for this using an expression I can't
find or can't write (due to inexperience with the software).
Click to expand...

I'd suggest that you need a table of Donors (containing both individuals and
corporations) related to a table of Donations. The primary key of Donors would
be a DonorID; this might be a unique TaxID if you have that information, or an
autonumber, or some other field just so it uniquely identifies the donor. The
Donations table would have fields for DonorID (who donated), DonationDate
(when they donated it), Amount, and perhaps other fields about this particular
donation.

If the information that you're collecting about corporate donors is completely
different than that about individuals, you may want two Donor tables, but for
this purpose I'd suggest that just one table is going to be easier.

John W. Vinson [MVP]
 
M

Mike C

To John & Wayne,

Thank-you for your input... I think you're right in that I should stick with
one table for payments.

Mike

John W. Vinson said:
I've created a simple database using Access 2002 tracking donations made by
individuals and donations made by Corporations. These donations are being
used to rebuild the old arena in town so I created two separate tables - one
for Corporations and the other for Inidividuals using the 'Amount' field as
the primary key in both tables.
Click to expand...

A Primary Key must uniquely identify the record: that is, there can be one and
only one record in the table with that value. The Amount is inappropriate as
the primary key here, unless you want to say that there can only be one $100
donation in the entire table, and all future $100 donations will be politely
declined!
I can total up the donations made by corporations and I can total up the
donations made by individuals using the =Sum([Amount]) expression in the
Report Footer.

When they asked me to total up ALL the donations made by both groups I can't
figure out a way to do that.

Should I have just used ONE table? I can't seem to get the Relationships
arrow to point both ways between the tables in my query... I can either get
all the corp or all the inidividual donations but I can't seem to get both.
I'm sure there's a simple explanation for this using an expression I can't
find or can't write (due to inexperience with the software).
Click to expand...

I'd suggest that you need a table of Donors (containing both individuals and
corporations) related to a table of Donations. The primary key of Donors would
be a DonorID; this might be a unique TaxID if you have that information, or an
autonumber, or some other field just so it uniquely identifies the donor. The
Donations table would have fields for DonorID (who donated), DonationDate
(when they donated it), Amount, and perhaps other fields about this particular
donation.

If the information that you're collecting about corporate donors is completely
different than that about individuals, you may want two Donor tables, but for
this purpose I'd suggest that just one table is going to be easier.

John W. Vinson [MVP]
Click to expand...
 
E

enky chin

Mike C said:
I've created a simple database using Access 2002 tracking donations made
by
individuals and donations made by Corporations. These donations are being
used to rebuild the old arena in town so I created two separate tables -
one
for Corporations and the other for Inidividuals using the 'Amount' field
as
the primary key in both tables.
I can total up the donations made by corporations and I can total up the
donations made by individuals using the =Sum([Amount]) expression in the
Report Footer.

When they asked me to total up ALL the donations made by both groups I
can't
figure out a way to do that.

Should I have just used ONE table? I can't seem to get the Relationships
arrow to point both ways between the tables in my query... I can either
get
all the corp or all the inidividual donations but I can't seem to get
both.
I'm sure there's a simple explanation for this using an expression I can't
find or can't write (due to inexperience with the software).

Any suggestioons would be greatly appreciated.

Mike C.
Click to expand...
 
E

enky chin

Mike C said:
I've created a simple database using Access 2002 tracking donations made
by
individuals and donations made by Corporations. These donations are being
used to rebuild the old arena in town so I created two separate tables -
one
for Corporations and the other for Inidividuals using the 'Amount' field
as
the primary key in both tables.
I can total up the donations made by corporations and I can total up the
donations made by individuals using the =Sum([Amount]) expression in the
Report Footer.

When they asked me to total up ALL the donations made by both groups I
can't
figure out a way to do that.

Should I have just used ONE table? I can't seem to get the Relationships
arrow to point both ways between the tables in my query... I can either
get
all the corp or all the inidividual donations but I can't seem to get
both.
I'm sure there's a simple explanation for this using an expression I can't
find or can't write (due to inexperience with the software).

Any suggestioons would be greatly appreciated.

Mike C.
Click to expand...
 
E

enky chin

Mike C said:
I've created a simple database using Access 2002 tracking donations made
by
individuals and donations made by Corporations. These donations are being
used to rebuild the old arena in town so I created two separate tables -
one
for Corporations and the other for Inidividuals using the 'Amount' field
as
the primary key in both tables.
I can total up the donations made by corporations and I can total up the
donations made by individuals using the =Sum([Amount]) expression in the
Report Footer.

When they asked me to total up ALL the donations made by both groups I
can't
figure out a way to do that.

Should I have just used ONE table? I can't seem to get the Relationships
arrow to point both ways between the tables in my query... I can either
get
all the corp or all the inidividual donations but I can't seem to get
both.
I'm sure there's a simple explanation for this using an expression I can't
find or can't write (due to inexperience with the software).

Any suggestioons would be greatly appreciated.

Mike C.
Click to expand...
 
E

enky chin

Mike C said:
I've created a simple database using Access 2002 tracking donations made
by
individuals and donations made by Corporations. These donations are being
used to rebuild the old arena in town so I created two separate tables -
one
for Corporations and the other for Inidividuals using the 'Amount' field
as
the primary key in both tables.
I can total up the donations made by corporations and I can total up the
donations made by individuals using the =Sum([Amount]) expression in the
Report Footer.

When they asked me to total up ALL the donations made by both groups I
can't
figure out a way to do that.

Should I have just used ONE table? I can't seem to get the Relationships
arrow to point both ways between the tables in my query... I can either
get
all the corp or all the inidividual donations but I can't seem to get
both.
I'm sure there's a simple explanation for this using an expression I can't
find or can't write (due to inexperience with the software).

Any suggestioons would be greatly appreciated.

Mike C.
Click to expand...
 
E

enky chin

Mike C said:
I've created a simple database using Access 2002 tracking donations made
by
individuals and donations made by Corporations. These donations are being
used to rebuild the old arena in town so I created two separate tables -
one
for Corporations and the other for Inidividuals using the 'Amount' field
as
the primary key in both tables.
I can total up the donations made by corporations and I can total up the
donations made by individuals using the =Sum([Amount]) expression in the
Report Footer.

When they asked me to total up ALL the donations made by both groups I
can't
figure out a way to do that.

Should I have just used ONE table? I can't seem to get the Relationships
arrow to point both ways between the tables in my query... I can either
get
all the corp or all the inidividual donations but I can't seem to get
both.
I'm sure there's a simple explanation for this using an expression I can't
find or can't write (due to inexperience with the software).

Any suggestioons would be greatly appreciated.

Mike C.
Click to expand...
 
E

enky chin

Mike C said:
I've created a simple database using Access 2002 tracking donations made
by
individuals and donations made by Corporations. These donations are being
used to rebuild the old arena in town so I created two separate tables -
one
for Corporations and the other for Inidividuals using the 'Amount' field
as
the primary key in both tables.
I can total up the donations made by corporations and I can total up the
donations made by individuals using the =Sum([Amount]) expression in the
Report Footer.

When they asked me to total up ALL the donations made by both groups I
can't
figure out a way to do that.

Should I have just used ONE table? I can't seem to get the Relationships
arrow to point both ways between the tables in my query... I can either
get
all the corp or all the inidividual donations but I can't seem to get
both.
I'm sure there's a simple explanation for this using an expression I can't
find or can't write (due to inexperience with the software).

Any suggestioons would be greatly appreciated.

Mike C.
Click to expand...
 
E

enky chin

Mike C said:
I've created a simple database using Access 2002 tracking donations made
by
individuals and donations made by Corporations. These donations are being
used to rebuild the old arena in town so I created two separate tables -
one
for Corporations and the other for Inidividuals using the 'Amount' field
as
the primary key in both tables.
I can total up the donations made by corporations and I can total up the
donations made by individuals using the =Sum([Amount]) expression in the
Report Footer.

When they asked me to total up ALL the donations made by both groups I
can't
figure out a way to do that.

Should I have just used ONE table? I can't seem to get the Relationships
arrow to point both ways between the tables in my query... I can either
get
all the corp or all the inidividual donations but I can't seem to get
both.
I'm sure there's a simple explanation for this using an expression I can't
find or can't write (due to inexperience with the software).

Any suggestioons would be greatly appreciated.

Mike C.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

New User - Linking tables 22
Problems with Totaling 1
sql question (using First, not working) 4
Table Design: common attributes in multiple entities 7
Search 2 tables for same criteria 1
using a query for a lookup column 1
Append Query Major Complication 11
Using the Count function in a cross-tab query 1

Top