"Flattening" table

[Guest]

Hi!

I have been thinking what would be the best way to "flatten" a table. I
think the easiest way to explain what I'm searching is by a simple example.
Beware, this is only to show you the idea, this isn't an actual table of mine


Lets say that I would have a table "Donations". It would have fields
Firstname, Lastname (name of the donator) and fields Animals, Hospital and
School as the things one could donate to. Table could look like this:

Firstname Lastname Animals Hospital School
Gene Gray 500
Mary May 200
Gene Gray 10
Donald Duck 1000
Tina Simpson 500
Mary May 300
Gene Gray 200

I'd like to have results like this:
Gene Gray 700 10
Mary May 200 300
Tina Simpson 500
Donald Duck 1000

Every distinct donator would have a record that would have the amounts the
donator had donated. I know that I can make a select distinct -query to get
every donators I would have in the table, but how to calculate how much they
have donated?

Any suggestions are appreciated, and if someone would know how to make this
as fast as possible, I'd be very glad!

- Beginner -

 

[Alex Dybenko]

hi,
you can make a new query with group by and sum, like this:

Select Firstname, Lastname, Sum(Animals) as AnimalsTotal, Sum(Hospital) as
HospitalTotal, Sum(School) as SchoolTotal
from mytable
group by Firstname, Lastname


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Great! Thank you a lot!

-Beginner-

hi,
you can make a new query with group by and sum, like this:

Select Firstname, Lastname, Sum(Animals) as AnimalsTotal, Sum(Hospital) as
HospitalTotal, Sum(School) as SchoolTotal
from mytable
group by Firstname, Lastname


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Steve]

You need three tables:
TblDonor
DonorID
FirstName
LastName

TblRecipient
RecipientID
RecipientName

TblDonation
DonationID
DonorID
RecipientID
DonationAmount

To get the result you want, you need to first create a totals query based on
TblDonation and get the total donation by each donor to each recipient. To
create a totals query, first create a regular query. Then with the query in
design view, click the Sigma button (looks like capital E) in the menu at
the top of the screen. Then under DonationAmount, change Group By to Sum.

Finally you need to create a crosstab query that includes the above query,
TblDonor and TblRecipient. To create a crosstab query, open the database
window to Queries, click on New and select Crosstab query. From there follow
the instructions.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Okay, that way is a bit harder but I totally understand what you mean. Thanks
for the advice!

-Beginner-