?
Solved

DSum on Access Query

Posted on 2007-12-04
14
Medium Priority
?
1,299 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

589 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