Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1297
  • Last Modified:

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.

0
squidlings
Asked:
squidlings
  • 7
  • 7
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
peter57rCommented:
You have put the ' ' in the wrong place.
Look at my previous response.
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now