Solved

DSum on Access Query

Posted on 2007-12-04
14
1,286 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
 

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
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.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article describes some very basic things about SQL Server filegroups.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
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 …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

948 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now