Dsum issue.

[dniezby]

I have a transaction table that tracks all of my member's transactions. Sort of like Airline miles; if someone spends X they get XX in points.


On my member form, I want those points to show up as "current points".

I created a textbox on my form with
=DSum([player_points],[plr_transactions],[member_id]=[member_id])

It just gives me a #Name?

Any thoughts?

[June7]

Try:

=DSum("[player_points]", "[plr_transactions]", "[member_id]=" & [member_id])

Might review Access Help on use of domain aggregate functions.

[dniezby]

The quotes worked but it doesn't work for each record. When I load the first record, it loads and it correct, but then when I go to the next one, it's the same. Do I have to related it or something?

[June7]

No, that should work. The textbox is in Detail section of form?

[dniezby]

It is in the details section of the form. I agree, it should work.
Here is the application if anyone wants to take a look.
http://sdrv.ms/12lglP1

It's supposed to show me a running total of all the player's transaction (player_points) from the transaction table. It does show me the correct points but ONLY for the one player. When I move to a new record, it doesn't update for the new player.

[June7]

The sum shown is the total of the entire table.

The field name in plr_transactions is member not member_ID

You have a lookup set for the member field in plr_transactions. (I NEVER set lookups in tables, review: http://access.mvps.org/access/lookupfields.htm)

The actual value in the member field is the autonumber primary key from members table.

=DSum("player_points","plr_transactions","member=" & [ID_memtbl])

[dniezby]

Hmm, I did write it like this once and it didn't work. For some reason, when I wrote it, it put everything in brackets when I saved the formula. It didn't do it this time. I kind of figured I was mixing one of the fields. I think the last time I tried it this way, I had member and ID_memtble reversed. When I was reading up on the DSum function I didn't understand the way they described it. Now that I see it in use, I do. Thank you again.

Also, I read the information in your link. If you don't use lookups, how do YOU do it. For example, I need to look up member IDs a lot in my app. What would you do differently for all the times I set a look up?

[June7]

Set lookups in comboboxes on forms, never in tables. I avoid lookup alias whenever I can. In other words, I will save the descriptive text instead of ID. For instance, for the states info I would make the 2-letter state abbreviation the key and eliminate the autonumber ID. I might even make member_ID the primary key instead of ID_memtbl. The way I set up most of my projects I don't use autonumber PK. There are arguments on both sides. Right now I am dealing with an issue of merging data from several Access files that use autonumber PK/FK. Requires complicated VBA code to handle.