Solved

DSum on Access Query

Posted on 2007-12-04
14
1,289 Views
Last Modified: 2008-02-01
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.

0
Comment
Question by:squidlings
  • 7
  • 7
14 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 20403843
0
 

Author Comment

by:squidlings
ID: 20403863
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
 
LVL 77

Expert Comment

by:peter57r
ID: 20404019
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:squidlings
ID: 20404434
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
 
LVL 77

Expert Comment

by:peter57r
ID: 20404568
Sorry - there should be a closing ) before As
0
 

Author Comment

by:squidlings
ID: 20410310
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
 
LVL 77

Expert Comment

by:peter57r
ID: 20410403
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
 

Author Comment

by:squidlings
ID: 20410485
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
 

Author Comment

by:squidlings
ID: 20410504
Hmmm, nope, I tried the JBA_code and it didn't work :(
0
 
LVL 77

Expert Comment

by:peter57r
ID: 20410522
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
 

Author Comment

by:squidlings
ID: 20410925
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
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 20410956
You have put the ' ' in the wrong place.
Look at my previous response.
0
 

Author Comment

by:squidlings
ID: 20411105
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
 
LVL 77

Expert Comment

by:peter57r
ID: 20411350
That looks OK to me, except that you might need to make sure that you have a space either side of & 
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question