I need a specific calculation

[Jimmy]

Hello. I have a problem and need your help bacause I do not know how to
solve it.
I have a table called ENTRY. There are about 20000 records of people who
have earned some points. There is a column named POINTS in which everyones
points are entered. Every person may be in the table more than once, with
different points entered each time. There is also a column named TOTAL. I
need to have the total points of each person entered in the column TOTAL but
only ONCE. So if I have "John Smith" in one record with 10 points and "John
Smith" in another record with 6 points, I need to have the number 16 in the
column TOTAL in only 1 of "John Smiths" records. The other records should be
blanc. We concider each record duplicate if "First-name" "Last-name" and
"Address" are similar.
Can someone help me? I am new in Access so I am really at a dead end.
Thank you very much in advance.
Jimmy

 

[chas]

Hi Jimmy,

Create a new query in design view and add the table to the
query pane. Double-click the fields that make each person
unique (FirstName, LastName, PostalCode......) to add them
to the grid and then add the [POINTS] field as well.
Choose View>Totals, you will see a new 'Totals' row in the
grid. Leave them all set to 'Group By' except the
[POINTS] field which you can change to 'Sum' by selecting
it from the list. Save and run your query.

hth

chas

 

[Chris]

Don't store the calculation in any of the fields. It will change too
frequently. Use the total from the query and work from there.

--
Chris

Please respond to newsgroups, as I
don't check this address very often.

Thanks chas.
That gives me the sum of each record. But how can I have that number entered
in the column TOTAL, only once, in one of the duplicate records only and not
in every duplicate record? Can you give me some help on that?
Thanks again
Jimmy

Hi Jimmy,

Create a new query in design view and add the table to the
query pane. Double-click the fields that make each person
unique (FirstName, LastName, PostalCode......) to add them
to the grid and then add the [POINTS] field as well.
Choose View>Totals, you will see a new 'Totals' row in the
grid. Leave them all set to 'Group By' except the
[POINTS] field which you can change to 'Sum' by selecting
it from the list. Save and run your query.

hth

chas

-----Original Message-----
Hello. I have a problem and need your help bacause I do not know how to
solve it.
I have a table called ENTRY. There are about 20000 records of people who
have earned some points. There is a column named POINTS in which everyones
points are entered. Every person may be in the table more than once, with
different points entered each time. There is also a column named TOTAL. I
need to have the total points of each person entered in the column TOTAL but
only ONCE. So if I have "John Smith" in one record with 10 points and "John
Smith" in another record with 6 points, I need to have the number 16 in the
column TOTAL in only 1 of "John Smiths" records. The other records should be
blanc. We concider each record duplicate if "First- name" "Last-name" and
"Address" are similar.
Can someone help me? I am new in Access so I am really at a dead end.
Thank you very much in advance.
Jimmy


.