DSum on Access Query

Hi,

I've been advised to use dSum in a form rather than a query to sum the totals of a field, simply because the sum query prevents updates to the form you are using.

Is DSum VB or an SQL expression?

Here is how I want to use it.  The form will be in dataview and show a list of all the account.  So I don't want to use a form showing single accounts with a subtable.  I've already got this screen working great.

2 tables.

First table (tbl1)

customer no. (key)(link)
Release order (yes/no)
order value

Tbl2

Autonumber (key)
Customer no.(link)
cheque amount.

I want the form to add up all the individual cheques from table 2 and display them on 1 form with all the info from table 1, so the user can see if the cheques received =>  than the order value.  Then they can click release order.

If I use the sum query to add the cheques together, the tick box will not work because a query is being used and access doesn't like that.

Please can somebody help me learn a new trick?

Thanks All!!!

Squid's.

squidlingsAsked:
Who is Participating?
 
peter57rCommented:
You have put the ' ' in the wrong place.
Look at my previous response.
0
 
peter57rCommented:
0
 
squidlingsAuthor Commented:
Hey,

Nothing went wrong, but your answer didn't quite fill my needs.  If you could read my question.

I need all the customers on 1 form, showing single customers with subform doesn't work for my company.

Your help was very good though, you pointed me in the right direction. :)

Cheers.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
peter57rCommented:
Base your form on a query as follows.

Select tbl1.*, dsum("[Cheque Amount]", "tbl2", "[Customer No] =" & tbl1.[customer No] as ChqSum
From tbl1 inner join tbl2
on tbl1.[customer no] = tble2,[customer no]
order by tbl1.[customer no]
 
You should then be able to update the yes/no field.
0
 
squidlingsAuthor Commented:
Hi dude,

Getting sytax, it's highlighting the "AS" at the end of line 2 below, any ideas?
I've implimented it to my actual database, so natuarally all the field/table names have changed.



SELECT SS07_payment_plan_chq_info.*, dsum("[Chq_amm]", "SS07_payment_plan", "[JBA code] =" & SS07_payment_plan_chq_info.[Acc_num] as chqsum

SS07_payment_plan.[Cliente Fatturazione],
SS07_payment_plan.[JBA code],
SS07_payment_plan.[Ragione Sociale],
SS07_payment_plan.[Order Value(inc vat)],
SS07_payment_plan.Currency, SS07_payment_plan.Controller,
SS07_payment_plan.[Insurance in currency],
SS07_payment_plan.[Existing Payment terms],
SS07_payment_plan.[Suggested pay terms],
SS07_payment_plan.[Memo notes],
SS07_payment_plan.release_acc,
SS07_payment_plan.[sum of cheques],
SS07_payment_plan.[date of release],
SS07_payment_plan_chq_info.Acc_num

FROM SS07_payment_plan LEFT JOIN SS07_payment_plan_chq_info ON SS07_payment_plan.[JBA code] = SS07_payment_plan_chq_info.Acc_num;
0
 
peter57rCommented:
Sorry - there should be a closing ) before As
0
 
squidlingsAuthor Commented:
Hi again,

SELECT SS07_payment_plan_chq_info.*,
DSum("[Chq_amm]","SS07_payment_plan","[JBA code] =" & SS07_payment_plan_chq_info.Acc_num) AS chqsum
FROM SS07_payment_plan LEFT JOIN SS07_payment_plan_chq_info ON SS07_payment_plan.[JBA code] = SS07_payment_plan_chq_info.Acc_num;

I've managed to get this in data view, only promblem is in the chqum column, everything reads #ERROR

Also it shows 502 line items(records), the database at this stage only has 103 records?

Any help or ideas?
0
 
peter57rCommented:
Very sorry - I have no idea what was going through my head when I posted that query.
Tbl2 must be removed from the query.

Hi again,

SELECT SS07_payment_plan_chq_info.*,
DSum("[Chq_amm]","SS07_payment_plan","[JBA code] =" & SS07_payment_plan_chq_info.Acc_num) AS chqsum
FROM SS07_payment_plan ;
0
 
squidlingsAuthor Commented:
Ok getting there.  It has the correct number of records now, clearly cos I'm using only 1 table.

But the chqsum field is full of #Error, when I click in any field, a small window pops up saying...

Syntax error (missing operator) in query expression '[JBA code] = 98G802

98G802 is actually a field entry for JBA Code.

Do you think I need underscore between JBA code, i.e. JBA_CODE? I'd have to change the field name in table design etc?

BIG THANKS FOR THIS MATE, MUCH APPRECIATED!"!

Here is the SQL again.

SELECT SS07_payment_plan_chq_info.*,
DSum("[Chq_amm]","SS07_payment_plan","[JBA code] =" & SS07_payment_plan_chq_info.Acc_num) AS chqsum
FROM SS07_payment_plan ;
0
 
squidlingsAuthor Commented:
Hmmm, nope, I tried the JBA_code and it didn't work :(
0
 
peter57rCommented:
If Jba_code is a text field you need ' ' around the value being found.

SELECT SS07_payment_plan_chq_info.*,
DSum("[Chq_amm]","SS07_payment_plan","[JBA code] ='" & SS07_payment_plan_chq_info.Acc_num &"'") AS chqsum
FROM SS07_payment_plan ;
0
 
squidlingsAuthor Commented:
Your correct, it is text, I put the, 'JBA_code' around.  Acc_num is also text so tried the same on different tests, still getting the same error message...

Sorry this is taking up so much time, I sense I'm close, but something isn't quite right.

SQL code :
SELECT SS07_payment_plan_chq_info.*,
DSum("[Chq_amm]","SS07_payment_plan"," ['JBA_code'] = "
& SS07_payment_plan_chq_info.Acc_num) AS chqsum
FROM SS07_payment_plan_chq_info;

The expression in design view :
chqsum: DSum("[Chq_amm]","SS07_payment_plan"," ['JBA_code'] = " & SS07_payment_plan_chq_info.Acc_num)

If you have 1 last effort I'd be grateful, then I'll dish out the points to you and learn more about dSum before I try to use it.  Thanks for all your help.
0
 
squidlingsAuthor Commented:
Won't even let me run the query now, stuck in SQL view, syntax eror.  

I'm gonna start a new thread and give you the points for this 1.  It's doing my nut.  Big thanks for everything you've done for me, much appreciated!

SELECT SS07_payment_plan_chq_info.*,
DSum("[Chq_amm]","SS07_payment_plan"," [JBA_code] = ' "& SS07_payment_plan_chq_info.Acc_num " ' " ) AS chqsum
FROM SS07_payment_plan_chq_info;
0
 
peter57rCommented:
That looks OK to me, except that you might need to make sure that you have a space either side of & 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.