summing data from columns

[buggirl]

Hello again,

I'm trying to sum and count my data. I have been told that it's not a good
idea to do these calculations in the table - rather, I should make a report
(or query?) and do the calculations in there.

Basically, my table looks like this:

apples oranges pears guava
Jim 1 0 3 0
Max 0 0 0 20
Fred 1 4 1 0
Angie 1 1 1 0

I want to know two things:

1. The total number of fruits each person has (sum)
2. The number of different types of fruit that each person has (count)

The way I see it, I want to add two columns at the end and just sum or count
the data in the rows (this is exactly what I would do in Excel in the past).
Seems simple, but I'm new to Access and I don't know which buttons to click!

Any advice would be greatly appreciated!

Thanks,

buggirl

 

[John W. Vinson]

Hello again,

I'm trying to sum and count my data. I have been told that it's not a good
idea to do these calculations in the table - rather, I should make a report
(or query?) and do the calculations in there.

Basically, my table looks like this:

apples oranges pears guava
Jim 1 0 3 0
Max 0 0 0 20
Fred 1 4 1 0
Angie 1 1 1 0

Then your table design IS WRONG.

You're "committing spreadsheet upon a table", a crime punishible by being
forced to read about Normalization ( see below ).

The correct design would have THREE tables:

People
PersonID
LastName
FirstName
<other bio information>

Fruits
FruitID
FruitName <e.g. apples, oranges>

Counts
PersonID <who's making fruit salad>
FruitID <what they're using for it>
Qty <how many they used>

Rather than one *field* per quantity you will have one *record* per quantity.

I want to know two things:

1. The total number of fruits each person has (sum)
2. The number of different types of fruit that each person has (count)

Very easy with a Totals query with a correct design. A pain in the neck with
your spreadsheet.

The way I see it, I want to add two columns at the end and just sum or count
the data in the rows (this is exactly what I would do in Excel in the past).
Seems simple, but I'm new to Access and I don't know which buttons to click!

Access is a relational database; Excel is a spreadsheet. THEY ARE DIFFERENT.
Applying spreadsheet logic to a database will get you into deep trouble!

Read some of the tutorials here:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[buggirl]

Well. How depressing. It looks like I have a lot of re-structuring to do...

But, on a happier note, I now am beginning to understand the difference
between a spreadsheet and a table, and get an inkling of what normalization
actually means. Why didn't they tell me this in my intro access course?!?!?!

Thanks,

buggirl